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--查询语句应该怎么写呢?
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--查询语句应该怎么写呢?
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 行受影响)
**/
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',33select 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 行受影响)
*/
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 行受影响)
*/MODIFY