数据表TB
字段
ID ZD1 ZD2
1 大 A
2 大 B
3 大 C
4 小 B
5 大 A
6 大 C
7 大 B
8 小 B
想统计的是:根据字段ZD1分组统计
结果是:
大-->
A 2
B 3
C 2
小-->
A 0
B 2
C 0这样的SQL语句怎么写啊??
字段
ID ZD1 ZD2
1 大 A
2 大 B
3 大 C
4 小 B
5 大 A
6 大 C
7 大 B
8 小 B
想统计的是:根据字段ZD1分组统计
结果是:
大-->
A 2
B 3
C 2
小-->
A 0
B 2
C 0这样的SQL语句怎么写啊??
http://blog.csdn.net/downmoon/archive/2009/10/23/4715814.aspx
select X1.ZD1,X1.ZD2, cnt=sum(case when T.ID is null then 0 else 1 end)
from
(
select * from
(select distinct ZD1 from T) A,(select distinct ZD2 from T) B
) X1
left join T
on X1.ZD1=T.ZD1 and X1.ZD2=T.ZD2
group by X1.ZD1,X1.ZD2
order by X1.ZD1,X1.ZD2
create table T(id int, ZD1 nvarchar(10), ZD2 nvarchar(10))
insert into T select 1 ,N'大' ,N'A'
insert into T select 2 ,N'大' ,N'B'
insert into T select 3 ,N'大' ,N'C'
insert into T select 4 ,N'小' ,N'B'
insert into T select 5 ,N'大' ,N'A'
insert into T select 6 ,N'大' ,N'C'
insert into T select 7 ,N'大' ,N'B'
insert into T select 8 ,N'小' ,N'B'
GOselect X1.ZD1,X1.ZD2, cnt=sum(case when T.ID is null then 0 else 1 end)
from
(
select * from
(select distinct ZD1 from T) A,(select distinct ZD2 from T) B
) X1
left join T
on X1.ZD1=T.ZD1 and X1.ZD2=T.ZD2
group by X1.ZD1,X1.ZD2
order by X1.ZD1,X1.ZD2/*
ZD1 ZD2 cnt
-----------------------------------
大 A 2
大 B 2
大 C 2
小 A 0
小 B 2
小 C 0*/
GO
drop table T
无法绑定由多个部分组成的标识符 "T.UName"。
无法绑定由多个部分组成的标识符 "T.ID"。
.......
不如先好好学一下sql基础,你用T做别名也得配合你的实际表啊
left join kmdata T
(select*from
(selectdistinct ZD1from T) A,(selectdistinct ZD2from T) B
) X1leftjoin Ton X1.ZD1=T.ZD1a?-
insert into T select 1 ,N'大' ,N'A'
insert into T select 2 ,N'大' ,N'B'
insert into T select 3 ,N'大' ,N'C'
insert into T select 4 ,N'小' ,N'B'
insert into T select 5 ,N'大' ,N'A'
insert into T select 6 ,N'大' ,N'C'
insert into T select 7 ,N'大' ,N'B'
insert into T select 8 ,N'小' ,N'B'SELECT ZD1,ZD2 INTO #test_Cross FROM T WHERE 1 = 2INSERT INTO #test_Cross
SELECT *
FROM
(SELECT DISTINCT ZD1 FROM T )A
CROSS JOIN
(SELECT DISTINCT ZD2 FROM T )BSELECT C.*,Count_All = ISNULL(D.Count_ZD2,0)
FROM #test_Cross C
LEFT JOIN
(SELECT ZD2,ZD1,Count_ZD2 = COUNT(*)
FROM T
GROUP BY ZD2,ZD1)D
ON C.ZD1 = D.ZD1
AND C.ZD2 = D.ZD2DROP TABLE #test_Cross
from
(
select * from
(select distinct ZD1 from T) A,(select distinct ZD2 from T) B
) X1
left join T
on X1.ZD1=T.ZD1 and X1.ZD2=T.ZD2
group by X1.ZD1,X1.ZD2
order by X1.ZD1,X1.ZD2
--1 即是ZD1和ZD2的所有组合情况2*3 = 6,即是Cross Join
select * from
(select distinct ZD1 from T) A,(select distinct ZD2 from T) B--Result
ZD1 ZD2
大 A
大 B
大 C
小 A
小 B
小 C--2 加个T.id再看看结果
select X1.ZD1,X1.ZD2,T_id = T.id
from
(
select * from
(select distinct ZD1 from T) A,(select distinct ZD2 from T) B
) X1
left join T
on X1.ZD1=T.ZD1 and X1.ZD2=T.ZD2
group by X1.ZD1,X1.ZD2,T.idZD1 ZD2 T_id
大 A 1
大 A 5
大 B 2
大 B 7
大 C 3
大 C 6
小 A NULL
小 B 4
小 B 8
小 C NULL--在Sum下求和,为NULL的做0处理
select X1.ZD1,X1.ZD2, cnt=sum(case when T.ID is null then 0 else 1 end)
from
(
select * from
(select distinct ZD1 from T) A,(select distinct ZD2 from T) B
) X1
left join T
on X1.ZD1=T.ZD1 and X1.ZD2=T.ZD2
group by X1.ZD1,X1.ZD2
order by X1.ZD1,X1.ZD2
-------------------------------------------------------
/*
Try following =====Shenliang1985=====
*/
------------------------------------------------------- SELECT E.ZD1,E.ZD2,Count_All = ISNULL(D.Count_ZD2,0)
FROM (SELECT * FROM
(SELECT DISTINCT ZD1 FROM T) A,(SELECT DISTINCT ZD2 FROM T) C)E
LEFT JOIN
(SELECT ZD2,ZD1,Count_ZD2 = COUNT(*)
FROM T
GROUP BY ZD2,ZD1)D
ON E.ZD1 = D.ZD1
AND E.ZD2 = D.ZD2
--Result
ZD1 ZD2 Count_All
大 A 2
大 B 2
大 C 2
小 A 0
小 B 2
小 C 0