表是这样的:
id name pid
1 aa 0
2 aa 0
3 ab 1
4 aa 1
5 aa 0想要的结果:
name count1 count2
aa 3 4
ab 1 1count1指的是pid=0的个数,count2指的是name='aa'的个数
id name pid
1 aa 0
2 aa 0
3 ab 1
4 aa 1
5 aa 0想要的结果:
name count1 count2
aa 3 4
ab 1 1count1指的是pid=0的个数,count2指的是name='aa'的个数
count1=sum(case when pid=0 then 1 else 0 end),
count2=count(*)
from tb group by name
谢谢了
a表:
id name pid fid
1 aa 0 7
2 aa 0 8
3 ab 1 1
4 aa 1 2
5 aa 0 9
b表
fid name1 dis
1 adj 1
2 dsfs 2
3 dsf 1
4 fjdkj 3
两表以fid关联,
想要的结果仍以上面的为基础,只是多了count1*dis这个值
count1=sum(case when pid=0 then 1 else 0 end),
count2=count(*),
sum(case when pid=0 then 1 else 0 end)*b.dis
from tb group by name
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
id int identity(1,1),
name varchar(10),
pid int
)
go
insert into tb (name,pid)
select 'aa',0 union all
select 'aa',0 union all
select 'ab',1 union all
select 'aa',1 union all
select 'aa',0
go
select name,count1=(select count(*) from tb where name=a.name and pid=0),count2=count(*) from tb a group by name
/*
name count1 count2
---------- ----------- -----------
aa 3 4
ab 0 1(2 行受影响)
*/