sql 2005 区间数据统计

2010-04-18 06:41:27 167 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

3GPP LTE/NR信道模型 CDL(Clustered Delay Line)：簇延迟线模型，包括CDL-A、CDL-B、CDL-C三个用于NLOS的信道模型，以及CDL-D、CDL-E两个用于LOS的信道模型。阅读详情





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



--查询语句应该怎么写呢？

...全文

【人工智能】人工智能基础概念、流派、发展史人工智能初衷就是让计算机能够像人类一样具有自我思考、自我学习、自我决策的能力。依据百度百科上对人工智能定义：人工智能是研究、开发用于模拟、扩展和延伸人的智能的理论、方法、技术及应用系统的一门新的技术学科。人工智能是研究使计算机来模拟人的某些思维过程和智能行为的学科，主要包括计算机实现智能的原理、制造类似于人脑智能的计算机，使计算机能实现更高层次的应用。阅读详情

volkswageos

疑难问题 22,298 成员 121,727 内容

+ 关注 + 加入社区

切换为时间正序

htl258_Tony 2010-04-18

打赏
举报

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 行受影响)

*/

MODIFY

htl258_Tony 2010-04-18

打赏
举报

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



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 行受影响)

*/

百年树人 2010-04-18

打赏
举报

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 行受影响)

**/

sql 2005 区间数据统计

相关推荐