表A
colID colAdd colDate
1 'China' 2008-10-10
2 'China' 2008-10-11
3 'USA' 2008-10-13----------------------
表B
colID colStudent
1 AA
1 BB
1 CC
1 DD
2 FF
2 EE
2 GG
2 HH
3 mm
-----------------------------
表CcolID colStudent colZT
1 AA 1
1 BB 1
1 CC 0
1 DD 0
2 FF 1
2 EE 0
2 GG 1
2 HH 1
3 mmmm 1
--------------------------------
以上3张表均通过colID相关联,现期望得到如下查询结果:
colAdd AllNum SomNum
china 8 5
USA 1 1备注:
AllNum为该地区下colStudent的总和;(表B)
SomNum为该地区下colZT为1的colStudent的总和;(表C)
colID colAdd colDate
1 'China' 2008-10-10
2 'China' 2008-10-11
3 'USA' 2008-10-13----------------------
表B
colID colStudent
1 AA
1 BB
1 CC
1 DD
2 FF
2 EE
2 GG
2 HH
3 mm
-----------------------------
表CcolID colStudent colZT
1 AA 1
1 BB 1
1 CC 0
1 DD 0
2 FF 1
2 EE 0
2 GG 1
2 HH 1
3 mmmm 1
--------------------------------
以上3张表均通过colID相关联,现期望得到如下查询结果:
colAdd AllNum SomNum
china 8 5
USA 1 1备注:
AllNum为该地区下colStudent的总和;(表B)
SomNum为该地区下colZT为1的colStudent的总和;(表C)
select a.coladd,B.allnum,C.somNum
from a
left join (select colid,count(colid) as allnum from b group by colid)B on a.colid = B.colid
left join (select colid,count(colid) as somNum from c where colzt=1 group by colid)C on a.colid = C.colid
from A join B on A.colID=B.colID
join C on C.colID=B.colID
group by colAdd
from 表C c left join 表B b on c.colid=b.colid
left join 表A a on a.colid=c.colid
group by a.coladd没有测试!
declare @表A table
(
colID int identity(1,1),
colAdd varchar(10),
colDate datetime
)
insert @表A select 'China','2008-10-10'
union all select 'China','2008-10-11'
union all select 'USA','2008-10-13'declare @表B table
(
colID int,
colStudent char(2)
)insert @表B select 1,'AA'
union all select 1,'BB'
union all select 1,'CC'
union all select 1,'DD'
union all select 2,'FF'
union all select 2,'EE'
union all select 2,'GG'
union all select 2,'HH'
union all select 3,'mm'declare @表C table
(
colID int,
colStudent varchar(10),
colZT int
)insert @表C select 1,'AA',1
union all select 1,'BB',1
union all select 1,'CC',0
union all select 1,'DD',0
union all select 2,'FF',1
union all select 2,'EE',0
union all select 2,'GG',1
union all select 2,'HH',1
union all select 3,'mmmm',1select
*
from @表A a
left join @表B b
on b.colID = a.colIDselect
a.colAdd,
AllNum = sum(b.count_b),
colZT = sum(count_c)
from @表A a
left join (
select
colID,
count(*) as count_b
from @表B
group by colID
)b
on b.colID = a.colID
left join (
select
colID,
sum(case colZT when 1 then 1 else 0 end) as count_c
from @表C
group by colID
)c
on c.colID = a.colID
group by a.colAdd
--多了个结果集:declare @表A table
(
colID int identity(1,1),
colAdd varchar(10),
colDate datetime
)
insert @表A select 'China','2008-10-10'
union all select 'China','2008-10-11'
union all select 'USA','2008-10-13'declare @表B table
(
colID int,
colStudent char(2)
)insert @表B select 1,'AA'
union all select 1,'BB'
union all select 1,'CC'
union all select 1,'DD'
union all select 2,'FF'
union all select 2,'EE'
union all select 2,'GG'
union all select 2,'HH'
union all select 3,'mm'declare @表C table
(
colID int,
colStudent varchar(10),
colZT int
)insert @表C select 1,'AA',1
union all select 1,'BB',1
union all select 1,'CC',0
union all select 1,'DD',0
union all select 2,'FF',1
union all select 2,'EE',0
union all select 2,'GG',1
union all select 2,'HH',1
union all select 3,'mmmm',1
select
a.colAdd,
AllNum = sum(b.count_b),
colZT = sum(count_c)
from @表A a
left join (
select
colID,
count(*) as count_b
from @表B
group by colID
)b
on b.colID = a.colID
left join (
select
colID,
sum(case colZT when 1 then 1 else 0 end) as count_c
from @表C
group by colID
)c
on c.colID = a.colID
group by a.colAdd
我的错了,没有考虑重复,应该先对连接结果distinct 再count 和sum
colAdd AllNum SomNum
---------- ----------- -----------
China 32 20
USA 1 1(所影响的行数为 2 行)
create table A
(
colID int identity(1,1),
colAdd varchar(10),
colDate datetime
)
insert A select 'China','2008-10-10'
union all select 'China','2008-10-11'
union all select 'USA','2008-10-13'create table B
(
colID int,
colStudent char(2)
)insert B select 1,'AA'
union all select 1,'BB'
union all select 1,'CC'
union all select 1,'DD'
union all select 2,'FF'
union all select 2,'EE'
union all select 2,'GG'
union all select 2,'HH'
union all select 3,'mm'create table C
(
colID int,
colStudent varchar(10),
colZT int
)insert C select 1,'AA',1
union all select 1,'BB',1
union all select 1,'CC',0
union all select 1,'DD',0
union all select 2,'FF',1
union all select 2,'EE',0
union all select 2,'GG',1
union all select 2,'HH',1
union all select 3,'mm',1select colAdd,AllNum=count(*),SomNum=sum(colZT)
from A join B on A.colID=B.colID
join C on C.colID=B.colID and C.colStudent=B.colStudent
group by colAdd
/*
colAdd AllNum SomNum
---------- ----------- -----------
China 8 5
USA 1 1
*/
--
drop table A
drop table B
drop table C
--
--select * from A
--select * from B
--select * from C
红色部分确实手误,但我的 colZT 值中不仅有1,0,还有2;
但我 SomNum统计出的数据仅仅期望是colZT=1的数据,该怎么实现呢,谢谢。
表C中数据可能为:colID colStudent colZT
1 AA 1
1 BB 1
1 CC 2
1 DD 0
2 FF 1
2 EE 0
2 GG 2
2 HH 1
3 mmmm 1但在SomNum统计列中,我仅仅想统计colZT=1的数量。
declare @表A table
(
colID int identity(1,1),
colAdd varchar(10),
colDate datetime
)
insert @表A select 'China','2008-10-10'
union all select 'China','2008-10-11'
union all select 'USA','2008-10-13'declare @表B table
(
colID int,
colStudent char(2)
)insert @表B select 1,'AA'
union all select 1,'BB'
union all select 1,'CC'
union all select 1,'DD'
union all select 2,'FF'
union all select 2,'EE'
union all select 2,'GG'
union all select 2,'HH'
union all select 3,'mm'declare @表C table
(
colID int,
colStudent varchar(10),
colZT int
)insert @表C select 1,'AA',1
union all select 1,'BB',1
union all select 1,'CC',0
union all select 1,'DD',0
union all select 2,'FF',1
union all select 2,'EE',0
union all select 2,'GG',1
union all select 2,'HH',1
union all select 3,'mmmm',1
select
a.colAdd,
AllNum = sum(b.count_b),
colZT = sum(count_c)
from @表A a
left join (
select
colID,
count(*) as count_b
from @表B
group by colID
)b
on b.colID = a.colID
left join (
select
colID,
sum(case colZT when 1 then 1 else 0 end) as count_c
from @表C
group by colID
)c
on c.colID = a.colID
group by a.colAdd/**
China 8 5
USA 1 1
**/
select colAdd,AllNum=count(*),SomNum=sum(case colZT when 1 then 1 else 0 end)
from A join B on A.colID=B.colID
join C on C.colID=B.colID and C.colStudent=B.colStudent
group by colAdd
现在我要计算SomNum/AllNum的值,在上面语句里该怎么写呢?
必须要sum(case colZT when 1 then 1 else 0 end)/count(*)?
计算的结果能否保留2位小数?
sum(case c.ZT when 1 then 1 else 0 end)/Convert(float,(convert(varchar,count(*))+'.00'))谢谢各位,结贴。