小弟我又不会写SQL了，哪位高手来帮帮小弟我，多谢
日期：2014-05-18 浏览次数：20777 次
我又不会写SQL了，谁来帮帮我，谢谢！
create table t
(
日期 char(10),
时间段 char(2),
时间 datetime,
数量 int
)
insert into t select '2012-3-1','06','2012-03-01 06:10:00',100
insert into t select '2012-3-1','06','2012-03-01 06:20:00',100
insert into t select '2012-3-1','06','2012-03-01 06:30:00',100
insert into t select '2012-3-1','06','2012-03-01 06:40:00',100
insert into t select '2012-3-1','06','2012-03-01 06:50:00',100
insert into t select '2012-3-1','07','2012-03-01 07:00:00',100
insert into t select '2012-3-1','07','2012-03-01 07:10:00',200
insert into t select '2012-3-1','07','2012-03-01 07:20:00',200
insert into t select '2012-3-1','07','2012-03-01 07:30:00',200
insert into t select '2012-3-1','07','2012-03-01 07:40:00',200
insert into t select '2012-3-1','07','2012-03-01 07:50:00',200
insert into t select '2012-3-1','08','2012-03-01 08:00:00',200
/*
得到如下结果，注：每个时间段都是从10分钟开始，到整点结束
也就是说，如果要查询06点的数据,就是从06:10---07:00这个区间统计
如果要查询07点的数据,就是从07:10---08:00这个区间统计
日期 时间段 数量总和
2012-03-01 06 600
2012-03-02 07 1200
*/
drop table t
------解决方案--------------------
create table t
(
日期 char(10),
时间段 char(2),
时间 datetime,
数量 int
)
insert into t select '2012-3-1','06','2012-03-01 06:10:00',100
insert into t select '2012-3-1','06','2012-03-01 06:20:00',100
insert into t select '2012-3-1','06','2012-03-01 06:30:00',100
insert into t select '2012-3-1','06','2012-03-01 06:40:00',100
insert into t select '2012-3-1','06','2012-03-01 06:50:00',100
insert into t select '2012-3-1','07','2012-03-01 07:00:00',100
insert into t select '2012-3-1','07','2012-03-01 07:10:00',200
insert into t select '2012-3-1','07','2012-03-01 07:20:00',200
insert into t select '2012-3-1','07','2012-03-01 07:30:00',200
insert into t select '2012-3-1','07','2012-03-01 07:40:00',200
insert into t select '2012-3-1','07','2012-03-01 07:50:00',200
insert into t select '2012-3-1','08','2012-03-01 08:00:00',200
/*
得到如下结果，注：每个时间段都是从10分钟开始，到整点结束
也就是说，如果要查询06点的数据,就是从06:10---07:00这个区间统计
如果要查询07点的数据,就是从07:10---08:00这个区间统计
日期 时间段 数量总和
2012-03-01 06 600
2012-03-02 07 1200
*/
drop table t
------解决方案--------------------
- SQL code
create table t
(
日期 char(10),
时间段 char(2),
时间 datetime,
数量 int
)
insert into t select '2012-03-01','06','2012-03-01 06:10:00',100
insert into t select '2012-03-01','06','2012-03-01 06:20:00',100
insert into t select '2012-03-01','06','2012-03-01 06:30:00',100
insert into t select '2012-03-01','06','2012-03-01 06:40:00',100
insert into t select '2012-03-01','06','2012-03-01 06:50:00',100
insert into t select '2012-03-01','06','2012-03-01 07:00:00',100
insert into t select '2012-03-01','07','2012-03-01 07:10:00',200
insert into t select '2012-03-01','07','2012-03-01 07:20:00',200
insert into t select '2012-03-01','07','2012-03-01 07:30:00',200
insert into t select '2012-03-01','07','2012-03-01 07:40:00',200
insert into t select '2012-03-01','07','2012-03-01 07:50:00',200
insert into t select '2012-03-01','07','2012-03-01 08:00:00',200
insert into t select '2012-03-01','12','2012-03-01 12:10:00',300
insert into t select '2012-03-01','12','2012-03-01 12:20:00',300
insert into t select '2012-03-01','12','2012-03-01 12:30:00',300
insert into t select '2012-03-01','12','2012-03-01 12:40:00',300
insert into t select '2012-03-01','12','2012-03-01 12:50:00',300
insert into t select '2012-03-01','12','2012-03-01 13:00:00',300
insert into t select '2012-03-01','23','2012-03-01 23:10:00',400
insert into t select '2012-03-01','23','2012-03-01 23:20:00',400
insert into t select '2012-03-01','23','2012-03-01 23:30:00',400
insert into t select '2012-03-01','23','2012-03-01 23:40:00',400
insert into t select '2012-03-01','23','2012-03-01 23:50:00',400
insert into t select '2012-03-02','00','2012-03-02 00:00:00',400
insert into t select '2012-03-02','00','2012-03-02 00:10:00',500
insert into t select '2012-03-02','00','2012-03-02 00:20:00',500
insert into t select '2012-03-02','00','2012-03-02 00:30:00',500
insert into t select '2012-03-02','00','2012-03-02 00:40:00',500
insert into t select '2012-03-02','00','2012-03-02 00:50:00',500
insert into t select '2012-03-02','01','2012-03-02 01:00:00',500
insert into t select '2012-03-02','05','2012-03-02 05:10:00',600
insert into t select '2012-03-02','05','2012-03-02 05:20:00',600
insert into t select '2012-03-02','05','2012-03-02 05:30:00',600
insert into t select '2012-03-02','05','2012-03-02 05:40:00',600
insert into t select '2012-03-02','05','2012-03-02 05:50:00',600
insert into t select '2012-03-02','06','2012-03-02 06:00:00',600
select 日期,时间段,SUM(数量) 数量 from (select 日期,时间,数量,
case when DATEPART(MI,时间)=0 and DATEPART(hh,时间)<>0 then right('0'+ltrim(DATEPART(HH,时间)-1),2) when
DATEPART(MI,时间)=0 and DATEPART(hh,时间)=0 then '23'
els
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
