重新问一下报表统计表A:
cCode cName
01 重要
02 一般表B:
iType body view
01 0 1
01 0 1
02 0 1
02 1 1
02 1 1
02 1 0
02 1 0A.cCode与B.iType关联结果
cName total_1 total_2 total3
重要 2 2 0
一般 5 1 1
total_1 是统计B表中重要和一般据点的记录条数
total_2 是B表中body=0的记录条数
total_3 是B表中view=0的记录条数有没为法一条sql语句得出
cCode cName
01 重要
02 一般表B:
iType body view
01 0 1
01 0 1
02 0 1
02 1 1
02 1 1
02 1 0
02 1 0A.cCode与B.iType关联结果
cName total_1 total_2 total3
重要 2 2 0
一般 5 1 1
total_1 是统计B表中重要和一般据点的记录条数
total_2 是B表中body=0的记录条数
total_3 是B表中view=0的记录条数有没为法一条sql语句得出
select
A.cName,
total_1=count(B.iType),
total_2=sum(case when B.body=0 then 1 else 0 end),
total_3=sum(case when B.view=0 then 1 else 0 end)
from
A,B
where
A.cCode=B.iType
group by
A.cName
insert into A values('01','重要')
insert into A values('02','一般')create table B(iType varchar(8),body int,[view] int)
insert into B values('01',0,1)
insert into B values('01',0,1)
insert into B values('02',0,1)
insert into B values('02',1,1)
insert into B values('02',1,1)
insert into B values('02',1,0)
insert into B values('02',1,0)
select
A.cName,
total_1=count(B.iType),
total_2=sum(case when B.body=0 then 1 else 0 end),
total_3=sum(case when B.[view]=0 then 1 else 0 end)
from
A,B
where
A.cCode=B.iType
group by
A.cName/*
cName total_1 total_2 total_3
-------- ----------- ----------- -----------
一般 5 1 2
重要 2 2 0
*/drop table A,B
select
A.cName,
total_1=count(B.iType),
total_2=sum(case when B.body=0 then 1 else 0 end),
total_3=sum(case when B.view=0 then 1 else 0 end)
from
A left join B
on
A.cCode=B.iType
group by
A.cName
select cName, count(case when a.cName='重要' then 1 else 0 end)as total_1
from b
left join a on b.iType=a.cCode group by a.cName
union
select cName,count(case when a.cName='一般' then 1 else 0 end)as total_1
from b
left join a on b.iType=a.cCode group by a.cName
中午吃饭了不写了,我只写了前两个字段的,其余将下面的高手,呵呵