主表A
colID colClass colDate colZT
1 A 2008 1
2 A 2008 1
3 B 2008 2
4 A 2007 0细表B
colID colName colSex
1 'A' '男'
1 'B' '男'
2 'C' '男'
2 'D' '女'
2 'E' '男'
2 'F' '男'
3 'G' '男'
3 'H' '男'
3 'I' '男'
4 'J' '男'
4 'K' '女'
4 'L' '男'先期望查询结果:
colClass 主表AllNum ZT1Num ZT2Num ZT0Num 细表AllNum
A 3 2 1 0 9
B 1 0 0 1 3
备注:
主表AllNum :按colClass统计colID的总数。
ZT1Num:colZT=1的数量;
ZT2Num:colZT=2的数量;
ZT0Num:colZT=0的数量;
细表AllNum=colID所对应的细表记录数量。
colID colClass colDate colZT
1 A 2008 1
2 A 2008 1
3 B 2008 2
4 A 2007 0细表B
colID colName colSex
1 'A' '男'
1 'B' '男'
2 'C' '男'
2 'D' '女'
2 'E' '男'
2 'F' '男'
3 'G' '男'
3 'H' '男'
3 'I' '男'
4 'J' '男'
4 'K' '女'
4 'L' '男'先期望查询结果:
colClass 主表AllNum ZT1Num ZT2Num ZT0Num 细表AllNum
A 3 2 1 0 9
B 1 0 0 1 3
备注:
主表AllNum :按colClass统计colID的总数。
ZT1Num:colZT=1的数量;
ZT2Num:colZT=2的数量;
ZT0Num:colZT=0的数量;
细表AllNum=colID所对应的细表记录数量。
主表AllNum = count(*) ,
ZT1Num = (select count(*) from b where colID= a.colID and a.colZT=1 ),
ZT2Num = (select count(*) from b where colID= a.colID and a.colZT=2 ),
ZT0Num = (select count(*) from b where colID= a.colID and a.colZT=0 ),
细表AllNum = (select count(*) from b where colID= a.colID)
from a
group by colClass
insert into a values(1 , 'A', 2008, 1)
insert into a values(2 , 'A', 2008, 1)
insert into a values(3 , 'B', 2008, 2)
insert into a values(4 , 'A', 2007, 0)
create table b(colID int, colName varchar(10), colSex varchar(10))
insert into b values(1 , 'A' , '男' )
insert into b values(1 , 'B' , '男' )
insert into b values(2 , 'C' , '男' )
insert into b values(2 , 'D' , '女' )
insert into b values(2 , 'E' , '男' )
insert into b values(2 , 'F' , '男' )
insert into b values(3 , 'G' , '男' )
insert into b values(3 , 'H' , '男' )
insert into b values(3 , 'I' , '男' )
insert into b values(4 , 'J' , '男' )
insert into b values(4 , 'K' , '女' )
insert into b values(4 , 'L' , '男' )
goselect m.* , n.ZT1Num , ZT2Num , ZT0Num , 细表AllNum from
(select colClass , count(*) 主表AllNum from a group by colClass) m,
(
select colClass ,
ZT1Num = sum(case when a.colZT = 1 then 1 else 0 end) ,
ZT2Num = sum(case when a.colZT = 2 then 1 else 0 end) ,
ZT0Num = sum(case when a.colZT = 0 then 1 else 0 end) ,
细表AllNum = count(*)
from a,b
where a.colID = b.colID
group by colClass
) n
where m.colClass = n.colClassdrop table a,b/*
colClass 主表AllNum ZT1Num ZT2Num ZT0Num 细表AllNum
---------- ----------- ----------- ----------- ----------- -----------
A 3 6 0 3 9
B 1 0 3 0 3(所影响的行数为 2 行)
*/
DECLARE @a TABLE
(
colID INT
,colClass VARCHAR(10)
,colDate VARCHAR(4)
,colZT INT
)
INSERT INTO @a VALUES (1, 'A', '2008', 1)
INSERT INTO @a VALUES (2, 'A', '2008', 1)
INSERT INTO @a VALUES (3, 'B', '2008', 2)
INSERT INTO @a VALUES (4, 'A', '2007', 0) DECLARE @b TABLE
(
colID INT
,colName VARCHAR(10)
,colSex VARCHAR(2)
)
INSERT INTO @b VALUES (1, 'A', '男')
INSERT INTO @b VALUES (1, 'B', '男')
INSERT INTO @b VALUES (2, 'C', '男')
INSERT INTO @b VALUES (2, 'D', '女')
INSERT INTO @b VALUES (2, 'E', '男')
INSERT INTO @b VALUES (2, 'F', '男')
INSERT INTO @b VALUES (3, 'G', '男')
INSERT INTO @b VALUES (3, 'H', '男')
INSERT INTO @b VALUES (3, 'I', '男')
INSERT INTO @b VALUES (4, 'J', '男')
INSERT INTO @b VALUES (4, 'K', '女')
INSERT INTO @b VALUES (4, 'L', '男')
SELECT colClass
,COUNT(colID) AS 主表AllNum
,SUM(CASE WHEN colZT = 1 THEN 1 ELSE 0 END) AS ZT1Num
,SUM(CASE WHEN colZT = 2 THEN 1 ELSE 0 END) AS ZT2Num
,SUM(CASE WHEN colZT = 0 THEN 1 ELSE 0 END) AS ZT0Num
,SUM(counts) AS 细表AllNum
FROM (
SELECT A.colID,A.colClass,A.colZT,COUNT(B.colID)AS counts
FROM @a A
INNER JOIN @b B ON A.colID = B.colID
GROUP BY A.colID,A.colClass,A.colZT
)X
GROUP BY colClass/*
colClass 主表AllNum ZT1Num ZT2Num ZT0Num 细表AllNum
---------- ----------- ----------- ----------- ----------- -----------
A 3 2 0 1 9
B 1 0 1 0 3
*/
declare @A table (colID int,colClass varchar(5),coldate varchar(4),colzt int)
insert into @A select 1,'A','2008',1
union all select 2,'A','2008',1
union all select 3,'B','2008',2
union all select 4,'A','2007',0
declare @B table (colID int,colName varchar(5),colSex varchar(5))
insert into @B select 1,'A','男'
union all select 1,'B','男'
union all select 2,'C','男'
union all select 2,'D','女'
union all select 2,'E','男'
union all select 2,'F','男'
union all select 3,'G','男'
union all select 3,'H','男'
union all select 4,'J','男'
union all select 4,'K','女'
union all select 4,'L','男'
-------
--select a.colzt from @A a left join @B b on a.colzt=b.colid
---group by a.colzt--select a.colclass,a.colzt,zt1sum=sum(case when colzt=1 then 1 else 0 end),
--zt2sum=sum(case when colzt=2 then 1 else 0 end),zt3sum=sum(case when colzt=0 then 1 else 0 end)
-- from @A a left join @B b on a.colzt=b.colid
---group by colzt,a.colclass
---------
select a.colclass,zt1num=sum(case when colzt=1 then 1 else 0 end),
zt2num=sum(case when colzt=2 then 1 else 0 end),
zt3num=sum(case when colzt=0 then 1 else 0 end)from @A a left join
(select colid,count(*) colid数 from @B
group by colid) b on a.colid=b.colid
group by colClass
现在期望查询结果:
colClass 主表AllNum ZT1Num ZT2Num ZT0Num 细表AllNum
A 3 2 1 0 9
B 1 0 0 1 3
备注:
DECLARE @a TABLE
(
colID INT
,colClass VARCHAR(10)
,colDate VARCHAR(4)
,colZT INT
)
INSERT INTO @a VALUES (1, 'A', '2008', 1)
INSERT INTO @a VALUES (2, 'A', '2008', 1)
INSERT INTO @a VALUES (3, 'B', '2008', 2)
INSERT INTO @a VALUES (4, 'A', '2007', 0) DECLARE @b TABLE
(
colID INT
,colName VARCHAR(10)
,colSex VARCHAR(2)
)
INSERT INTO @b VALUES (1, 'A', '男')
INSERT INTO @b VALUES (1, 'B', '男')
INSERT INTO @b VALUES (2, 'C', '男')
INSERT INTO @b VALUES (2, 'D', '女')
INSERT INTO @b VALUES (2, 'E', '男')
INSERT INTO @b VALUES (2, 'F', '男')
INSERT INTO @b VALUES (3, 'G', '男')
INSERT INTO @b VALUES (3, 'H', '男')
INSERT INTO @b VALUES (3, 'I', '男')
INSERT INTO @b VALUES (4, 'J', '男')
INSERT INTO @b VALUES (4, 'K', '女')
INSERT INTO @b VALUES (4, 'L', '男') select colClass,
主表AllNum=(select count(1) from @a where colClass=qq.colClass),
ZT1Num=(select count(1) from @a where colClass=qq.colClass and colZT =1),
ZT2Num=(select count(1) from @a where colClass=qq.colClass and colZT =2),
ZT0Num=(select count(1) from @a where colClass=qq.colClass and colZT =0),
AllNum=count(1)
from
(select B.colID,colName,colSex,colclass,coldate,colZT from @b as B left join @a as A
on B.colID = A.colID )qq group by colClass