if object_id('tb') is not null
drop table tb
create table tb(type varchar(20),classshift varchar(20),deviceNo varchar(10),lastTime decimal(27,6))
insert tb
select 'A','白班','z11',10 union all
select 'B','二班','z11',11 union all
select 'A','二班','z12',51 union all
select 'A','三班','z11',91 union all
select 'A','大夜','z13',29 union all
select 'B','大夜','z11',30 union all
select 'B','二班','z11',50 union all
select 'B','白班','z11',85 union all
select 'A','二班','z11',60 union all
select 'A','大夜','z11',30 union all
select 'B','二班','z11',33--要求的查询统计结果是/*型别 津贴
A 20
B 12
总计 32--其中 白班 0 二班 2 三班 4 大夜 6
*/
drop table tb--这个查询应该怎么写呢?
drop table tb
create table tb(type varchar(20),classshift varchar(20),deviceNo varchar(10),lastTime decimal(27,6))
insert tb
select 'A','白班','z11',10 union all
select 'B','二班','z11',11 union all
select 'A','二班','z12',51 union all
select 'A','三班','z11',91 union all
select 'A','大夜','z13',29 union all
select 'B','大夜','z11',30 union all
select 'B','二班','z11',50 union all
select 'B','白班','z11',85 union all
select 'A','二班','z11',60 union all
select 'A','大夜','z11',30 union all
select 'B','二班','z11',33--要求的查询统计结果是/*型别 津贴
A 20
B 12
总计 32--其中 白班 0 二班 2 三班 4 大夜 6
*/
drop table tb--这个查询应该怎么写呢?
sum(case classshift when '白班' then 0 when '二班' then 2 when '三班' then 4 when '大夜' then 6 end) as 津贴
from tb
group by type
union
select '总计',sum(case classshift when '白班' then 0 when '二班' then 2 when '三班' then 4 when '大夜' then 6 end) as 津贴
from tb
型别 津贴
-------------------- -----------
A 20
B 12
总计 32(所影响的行数为 3 行)
isnull(type,'合计') as 型别,
sum(case classshift
when '白班' then 0
when '二班' then 2
when '三班' then 4
when '大夜' then 6 end
) as 津贴
from tb
group by type with rollup/**
型别 津贴
-------------------- -----------
A 20
B 12
合计 32(3 行受影响)
**/
drop table tb
go
create table tb(type varchar(20),classshift varchar(20),deviceNo varchar(10),lastTime decimal(27,6))
insert tb
select 'A','白班','z11',10 union all
select 'B','二班','z11',11 union all
select 'A','二班','z12',51 union all
select 'A','三班','z11',91 union all
select 'A','大夜','z13',29 union all
select 'B','大夜','z11',30 union all
select 'B','二班','z11',50 union all
select 'B','白班','z11',85 union all
select 'A','二班','z11',60 union all
select 'A','大夜','z11',30 union all
select 'B','二班','z11',33select isnull(type,'总计') 类别,
SUM(case classshift when '白班' then 0
when '二班' then 2
when '三班' then 4
when '大夜' then 6
else 0
end) 津贴
from tb
group by type
with rollup
/*
类别 津贴
-------------------- -----------
A 20
B 12
总计 32(3 行受影响)
*/
select type as [型别],
sum(case when classshift='白班' then 0
when classshift='二班' then 2
when classshift='三班' then 4
when classshift='大夜' then 6
end) as [津贴]
from tb
group by type
union all
select '总计',
sum(case when classshift='白班' then 0
when classshift='二班' then 2
when classshift='三班' then 4
when classshift='大夜' then 6
end) as [津贴]
from tb