请教一个 SQL 统计的查询

Question

A表,F1,F2字段 （F2保存的F1的值）。

1,0
2,0
3,1
4,1
5,2

希望的查询结果列表 F1,F2,(F2中包括F1的个数)

1,0,2
2,0,1
3,1,0
4,1,0
5,2,0

Answer 1

不是很明白LZ想表达的意思？？

Answer 2

您好，

請參考以下方式，先找出F2的Group，再Join到F1的值，如下，

USE tempdb
GO

create table t1
(
F1 int,
F2 int
);
go

insert into t1(F1, F2) values(1,0);
insert into t1(F1, F2) values(2,0);
insert into t1(F1, F2) values(3,1);
insert into t1(F1, F2) values(4,1);
insert into t1(F1, F2) values(5,2);
--way 1
;
WITH F2_Group
AS
(
SELECT F2, COUNT(*) AS F2_CNT
FROM t1
GROUP BY F2
)
SELECT t1.F1, t1.F2
, ISNULL((SELECT TOP 1 F2_CNT FROM F2_Group WHERE t1.F1 = F2_Group.F2), 0)
FROM t1;

--way 2
;
WITH F2_Group
AS
(
SELECT F2, COUNT(*) AS F2_CNT
FROM t1
GROUP BY F2
)
SELECT t1.F1, t1.F2, ISNULL(F2_CNT, 0) AS F2CNT
FROM t1 LEFT JOIN F2_Group
ON  t1.F1 = F2_Group.F2;

---

亂馬客blog: http://www.dotblogs.com.tw/rainmaker/