Çë½Ìsql¡£ - MS-SQL Server / ÒÉÄÑÎÊÌâ

SQL code:
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

--²éѯÓï¾äÓ¦¸ÃÔõôдÄØ£¿

---

SQL code:

with t as(
select [type] as Ðͱð,
count(*) as ºÏ¼Æ,
(select count(*) from tb as tt1 where lastTime<10 and tt1.[type] = t1.[type]) as [10ÒÔÏÂ],
(select count(*) from tb as tt2 where lastTime between 10 and 30 and tt2.[type] = t1.[type]) as [10ÖÁ30],
(select count(*) from tb as tt3 whe