SQL 分组统计,再合并组
发布网友
发布时间：2022-04-24 01:10
共5个回答
热心网友
时间：2022-04-26 01:54
select
ta.deptname,
count(ta.deptname)
as
personcount,
sum(ta.status1)
as
status1,
sum(ta.status2)
as
status2,
sum(ta.status3)
as
status3,
sum(ta.status4)
as
status4
from
(select
deptname,
case
status
when
'状态1'
then
1
else
0
end
as
status1,
case
status
when
'状态2'
then
1
else
0
end
as
status2,
case
status
when
'状态3'
then
1
else
0
end
as
status3,
case
status
when
'状态4'
then
1
else
0
end
as
status4
from
tablename
where
datepart(y,
date)=2011
and
datepart(m,
date)=1)
ta
group
by
ta.deptname
月份需要加上年份一起判断,
合计不能从这条语句中得到,可以通过另外一条语句或者通过程序中对数据分析得到,不知道你前台用什么开发的,是不是有控件可以直接生成.
select
count(*)
as
recordcount,
sum(tb.status1)
as
status1,
sum(tb.status2)
as
status2,
sum(tb.status3)
as
status3,
sum(tb.status4)
as
status4
from
(select
ta.deptname,
count(ta.deptname)
as
personcount,
sum(ta.status1)
as
status1,
sum(ta.status2)
as
status2,
sum(ta.status3)
as
status3,
sum(ta.status4)
as
status4
from
(select
deptname,
case
status
when
'状态1'
then
1
else
0
end
as
status1,
case
status
when
'状态2'
then
1
else
0
end
as
status2,
case
status
when
'状态3'
then
1
else
0
end
as
status3,
case
status
when
'状态4'
then
1
else
0
end
as
status4
from
tablename
where
datepart(y,
date)=2011
and
datepart(m,
date)=1)
ta
group
by
ta.deptname)
tb
这条语句可以得到合计值
热心网友
时间：2022-04-26 03:12
select 组名2 as 组名, 产品2 as 产品,sum(数量) as 数量
from
(select case when 组名 = 'A1' then 'A' when 组名 = 'B1' then 'B' else 组名 end as 组名2,case when 组名 = 'A1' then 'aa' when 组名 = 'B1' then 'bb' else 产品 end as 产品2 ,数量
from 表
) tb
group by 组名2, 产品2
热心网友
时间：2022-04-26 04:46
select left(组名,1) as new_组名,min(产品) as new_产品,sum(数量) from 表 group by left(组名,1) order by new_组名
热心网友
时间：2022-04-26 06:38
给你句简单也是速度最快的：
select left(组名,1),min(产品),sum(数量)
from a
group by left(组名,1)
热心网友
时间：2022-04-26 08:46
select substring(组名,1,1) as 组名,产品,sum(数量) as 数量 from 表 group by substring(组名,1,1);
