22,209
社区成员
发帖
与我相关
我的任务
分享
if object_id('tb') is not null
drop table tb
create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime decimal(27,6))
insert tb
select 'A','s10','z11',10 union all
select 'B','s10','z11',11 union all
select 'A','s11','z12',51 union all
select 'A','s10','z11',91 union all
select 'A','s10','z13',29 union all
select 'B','s10','z11',30 union all
select 'B','s10','z11',50 union all
select 'B','s10','z11',85 union all
select 'A','s10','z11',60 union all
select 'A','s10','z11',30 union all
select 'B','s10','z11',33
--要求的查询结果
/*
型别 合计 10以下 10-30 31-60 61-90 90以上
A 6 0 3 2 0 1
B 5 0 2 2 1 0
合计 11 0 5 4 1 1
*/
drop table tb
--查询语句应该怎么写呢?
select isnull(type,'合计') 型别,
COUNT(1) 合计,
SUM(case when lasttime<10 then 1 else 0 end) [10以下],
SUM(case when lasttime between 10 and 30 then 1 else 0 end) [10-30],
SUM(case when lasttime between 31 and 60 then 1 else 0 end) [31-60],
SUM(case when lasttime between 61 and 90 then 1 else 0 end) [61-90],
SUM(case when lasttime >90 then 1 else 0 end) [90以上]
from tb
group by type
with cube
/*
型别 合计 10以下 10-30 31-60 61-90 90以上
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
A 6 0 3 2 0 1
B 5 0 2 2 1 0
合计 11 0 5 4 1 1
(3 行受影响)
*/
MODIFYif object_id('tb') is not null
drop table tb
create table tb(type varchar(20),accessoryNo varchar(20),deviceNo varchar(10),lastTime decimal(27,6))
insert tb
select 'A','s10','z11',10 union all
select 'B','s10','z11',11 union all
select 'A','s11','z12',51 union all
select 'A','s10','z11',91 union all
select 'A','s10','z13',29 union all
select 'B','s10','z11',30 union all
select 'B','s10','z11',50 union all
select 'B','s10','z11',85 union all
select 'A','s10','z11',60 union all
select 'A','s10','z11',30 union all
select 'B','s10','z11',33
select type 型别,
COUNT(1) 合计,
SUM(case when lasttime<10 then 1 else 0 end) [10以下],
SUM(case when lasttime between 10 and 30 then 1 else 0 end) [10-30],
SUM(case when lasttime between 31 and 60 then 1 else 0 end) [31-60],
SUM(case when lasttime between 61 and 90 then 1 else 0 end) [61-90],
SUM(case when lasttime >90 then 1 else 0 end) [90以上]
from tb
group by type
/*
型别 合计 10以下 10-30 31-60 61-90 90以上
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
A 6 0 3 2 0 1
B 5 0 2 2 1 0
(2 行受影响)
*/
select
isnull(type,'合计') as 型别,
count(1) as 合计,
sum(case when lasttime<10 then 1 else 0 end) as [10以下],
sum(case when lasttime between 10 and 30 then 1 else 0 end) as [10-30],
sum(case when lasttime between 31 and 60 then 1 else 0 end) as [10-30],
sum(case when lasttime between 61 and 90 then 1 else 0 end) as [10-30],
sum(case when lasttime>90 then 1 else 0 end) as [90以上]
from tb
group by type with rollup
/**
型别 合计 10以下 10-30 10-30 10-30 90以上
-------------------- ----------- ----------- ----------- ----------- ----------- -----------
A 6 0 3 2 0 1
B 5 0 2 2 1 0
合计 11 0 5 4 1 1
(3 行受影响)
**/