在线等,sql一次查询多个条件的统计

发布网友发布时间：2022-04-08 09:02

共2个回答

热心网友时间：2022-04-08 10:31

创建表。插入数据

create table a
(id int,
name varchar(1),
category varchar(2),
other varchar(2),
count int);

insert into a values (1,'a','xx','dd',1);
insert into a values (2,'a','xx','ff',1);
insert into a values (3,'a','yy','dd',1);
insert into a values (4,'a','yy','ff',1);
insert into a values (5,'b','xx','ff',1);
insert into a values (6,'b','xx','dd',1);
insert into a values (7,'b','yy','dd',1);
insert into a values (8,'b','yy','ff',1);
insert into a values (9,'b','zz','gg',1);
insert into a values (10,'c','zz','hh',1);--为了测试准确，在你基础上加了两条数据

执行

select t2.name,t2.category,t2.c_count,t1.total_count
from
(select name,count(*) as total_count from a group by name) t1 left join
(select name,category,count(*) as c_count from a group by name,category) t2
on t1.name=t2.name
order by t2.name,t2.category

结果截图

热心网友时间：2022-04-08 11:49

Select name,category,COUNT(*) As c_count,(Select COUNT(*) From T A
Where A.name=T.name)As total_count
From T Group By name,category