求助一个SQL语句
表A中有如下字段
date type num
2007-4-1 0:00:00 A 2
2007-4-1 0:00:00 B 2
2007-4-1 0:00:00 C 2
2007-4-1 1:00:00 A 7
2007-4-1 1:00:00 B 7
2007-4-1 1:00:00 C 7
.....
2007-4-1 21:00:00 A 8
2007-4-1 21:00:00 B 8
2007-4-1 21:00:00 C 8
....
....
2007-4-30 0:00:00 A 5
2007-4-30 0:00:00 B 5
2007-4-30 0:00:00 C 5
2007-4-30 1:00:00 A 7
2007-4-30 1:00:00 B 7
2007-4-30 1:00:00 C 7
.....
2007-4-30 21:00:00 A 10
2007-4-30 21:00:00 B 10
2007-4-30 21:00:00 C 10
我想查询出每天哪个时段的总NUM数最多,哪个时段的总NUM数最少.
即:想得到
date total_num
2007-4-1 0:00:00 6
2007-4-1 21:00:00 24
....
2007-4-30 0:00:00 15
2007-4-30 21:00:00 30
请问如何写这个SQL语句,谢谢!
[解决办法]
兄弟 帮你顶吧 我也想知道 此sql的写法
关键是效率
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb([date] datetime,type varchar(10),num int)
insert into tb([date],type,num) values( '2007-4-1 0:00:00 ' , 'A ' , 2)
insert into tb([date],type,num) values( '2007-4-1 0:00:00 ' , 'B ' , 2)
insert into tb([date],type,num) values( '2007-4-1 0:00:00 ' , 'C ' , 2)
insert into tb([date],type,num) values( '2007-4-1 1:00:00 ' , 'A ' , 7)
insert into tb([date],type,num) values( '2007-4-1 1:00:00 ' , 'B ' , 7)
insert into tb([date],type,num) values( '2007-4-1 1:00:00 ' , 'C ' , 7)
insert into tb([date],type,num) values( '2007-4-1 21:00:00 ' , 'A ' , 8)
insert into tb([date],type,num) values( '2007-4-1 21:00:00 ' , 'B ' , 8)
insert into tb([date],type,num) values( '2007-4-1 21:00:00 ' , 'C ' , 8)
insert into tb([date],type,num) values( '2007-4-30 0:00:00 ' , 'A ' , 5)
insert into tb([date],type,num) values( '2007-4-30 0:00:00 ' , 'B ' , 5)
insert into tb([date],type,num) values( '2007-4-30 0:00:00 ' , 'C ' , 5)
insert into tb([date],type,num) values( '2007-4-30 1:00:00 ' , 'A ' , 7)
insert into tb([date],type,num) values( '2007-4-30 1:00:00 ' , 'B ' , 7)
insert into tb([date],type,num) values( '2007-4-30 1:00:00 ' , 'C ' , 7)
insert into tb([date],type,num) values( '2007-4-30 21:00:00 ', 'A ', 10)
insert into tb([date],type,num) values( '2007-4-30 21:00:00 ', 'B ', 10)
insert into tb([date],type,num) values( '2007-4-30 21:00:00 ', 'C ', 10)
go
select * from
(
select t1.datetime,t1.num from
(
select convert(varchar(13),date,120) datetime,sum(num) num from tb group by convert(varchar(13),date,120)
) t1,
(
select left(datetime,10) date , max(num) num from
(
select convert(varchar(13),date,120) datetime,sum(num) num from tb group by convert(varchar(13),date,120)
) t3
group by left(datetime,10)
) t2
where left(t1.datetime,10) = t2.date and t1.num = t2.num
union all
select t1.datetime,t1.num from
(
select convert(varchar(13),date,120) datetime,sum(num) num from tb group by convert(varchar(13),date,120)
) t1,
(
select left(datetime,10) date , min(num) num from
(
select convert(varchar(13),date,120) datetime,sum(num) num from tb group by convert(varchar(13),date,120)
) t3
group by left(datetime,10)
) t2
where left(t1.datetime,10) = t2.date and t1.num = t2.num
) t
order by datetime , num
drop table tb
/*
datetime num
------------- -----------
2007-04-01 00 6
2007-04-01 21 24
2007-04-30 00 15
2007-04-30 21 30
（所影响的行数为 4 行）
*/
[解决办法]
drop table A
go
create table A([date] datetime,type char(1),num int)
insert into A
select '2007-4-1 0:00:00 ', 'A ',2
union all select '2007-4-1 0:00:00 ', 'B ',2
union all select '2007-4-1 0:00:00 ', 'C ',2
union all select '2007-4-1 1:00:00 ', 'A ',7
union all select '2007-4-1 1:00:00 ', 'B ',7
union all select '2007-4-1 1:00:00 ', 'C ',7
union all select '2007-4-1 21:00:00 ', 'A ',8
union all select '2007-4-1 21:00:00 ', 'B ',8
union all select '2007-4-1 21:00:00 ', 'C ',8
union all select '2007-4-30 0:00:00 ', 'A ',5
union all select '2007-4-30 0:00:00 ', 'B ',5
union all select '2007-4-30 0:00:00 ', 'C ',5
union all select '2007-4-30 1:00:00 ', 'A ',7
union all select '2007-4-30 1:00:00 ', 'B ',7
union all select '2007-4-30 1:00:00 ', 'C ',7
union all select '2007-4-30 21:00:00 ', 'A ',10
union all select '2007-4-30 21:00:00 ', 'B ',10
union all select '2007-4-30 21:00:00 ', 'C ',10
select * from (select convert(char(16),[date],120) as [date],sum(num) as total_num
from A
group by convert(char(16),[date],120)
)t
where not exists(select 1 from (select convert(char(16),[date],120) as [date],sum(num) as total_num
from A
group by convert(char(16),[date],120))a where left(t.[date],10)=left(a.[date],10) and a.total_num <t.total_num)
or not exists(select 1 from (select convert(char(16),[date],120) as [date],sum(num) as total_num
from A
group by convert(char(16),[date],120))a where left(t.[date],10)=left(a.[date],10) and a.total_num> t.total_num)
/*
date total_num
---------------- -----------
2007-04-01 00:00 6
2007-04-01 21:00 24
2007-04-30 00:00 15
2007-04-30 21:00 30
（所影响的行数为 4 行）
*/
[解决办法]
--借楼上数据
create table tb([date] datetime,type varchar(10),num int)
insert into tb([date],type,num) values( '2007-4-1 0:00:00 ' , 'A ' , 2)
insert into tb([date],type,num) values( '2007-4-1 0:00:00 ' , 'B ' , 2)
insert into tb([date],type,num) values( '2007-4-1 0:00:00 ' , 'C ' , 2)
insert into tb([date],type,num) values( '2007-4-1 1:00:00 ' , 'A ' , 7)
insert into tb([date],type,num) values( '2007-4-1 1:00:00 ' , 'B ' , 7)
insert into tb([date],type,num) values( '2007-4-1 1:00:00 ' , 'C ' , 7)
insert into tb([date],type,num) values( '2007-4-1 21:00:00 ' , 'A ' , 8)
insert into tb([date],type,num) values( '2007-4-1 21:00:00 ' , 'B ' , 8)
insert into tb([date],type,num) values( '2007-4-1 21:00:00 ' , 'C ' , 8)
insert into tb([date],type,num) values( '2007-4-30 0:00:00 ' , 'A ' , 5)
insert into tb([date],type,num) values( '2007-4-30 0:00:00 ' , 'B ' , 5)
insert into tb([date],type,num) values( '2007-4-30 0:00:00 ' , 'C ' , 5)
insert into tb([date],type,num) values( '2007-4-30 1:00:00 ' , 'A ' , 7)
insert into tb([date],type,num) values( '2007-4-30 1:00:00 ' , 'B ' , 7)
insert into tb([date],type,num) values( '2007-4-30 1:00:00 ' , 'C ' , 7)
insert into tb([date],type,num) values( '2007-4-30 21:00:00 ', 'A ', 10)
insert into tb([date],type,num) values( '2007-4-30 21:00:00 ', 'B ', 10)
insert into tb([date],type,num) values( '2007-4-30 21:00:00 ', 'C ', 10)
go
--查询
select * from
(select [date],num=sum(num) from tb group by [date]) a
where not exists(select 1 from (select [date],num=sum(num) from tb group by [date]) b
where b.num> a.num and datediff(day,a.[date],b.[date])=0)
union all
select * from
(select [date],num=sum(num) from tb group by [date]) a
where not exists(select 1 from (select [date],num=sum(num) from tb group by [date]) b
where b.num <a.num and datediff(day,a.[date],b.[date])=0)
order by [date]
--删除测试表
drop table tb
/*结果
date num
-------------------------------------
2007-04-01 00:00:00.0006
2007-04-01 21:00:00.00024
2007-04-30 00:00:00.00015
2007-04-30 21:00:00.00030
*/
[解决办法]
create table tb([date] datetime,type varchar(10),num int)
insert into tb([date],type,num) values( '2007-4-1 0:00:00 ' , 'A ' , 2)
insert into tb([date],type,num) values( '2007-4-1 0:00:00 ' , 'B ' , 2)
insert into tb([date],type,num) values( '2007-4-1 0:00:00 ' , 'C ' , 2)
insert into tb([date],type,num) values( '2007-4-1 1:00:00 ' , 'A ' , 7)
insert into tb([date],type,num) values( '2007-4-1 1:00:00 ' , 'B ' , 7)
insert into tb([date],type,num) values( '2007-4-1 1:00:00 ' , 'C ' , 7)
insert into tb([date],type,num) values( '2007-4-1 21:00:00 ' , 'A ' , 8)
insert into tb([date],type,num) values( '2007-4-1 21:00:00 ' , 'B ' , 8)
insert into tb([date],type,num) values( '2007-4-1 21:00:00 ' , 'C ' , 8)
insert into tb([date],type,num) values( '2007-4-30 0:00:00 ' , 'A ' , 5)
insert into tb([date],type,num) values( '2007-4-30 0:00:00 ' , 'B ' , 5)
insert into tb([date],type,num) values( '2007-4-30 0:00:00 ' , 'C ' , 5)
insert into tb([date],type,num) values( '2007-4-30 1:00:00 ' , 'A ' , 7)
insert into tb([date],type,num) values( '2007-4-30 1:00:00 ' , 'B ' , 7)
insert into tb([date],type,num) values( '2007-4-30 1:00:00 ' , 'C ' , 7)
insert into tb([date],type,num) values( '2007-4-30 21:00:00 ', 'A ', 10)
insert into tb([date],type,num) values( '2007-4-30 21:00:00 ', 'B ', 10)
insert into tb([date],type,num) values( '2007-4-30 21:00:00 ', 'C ', 10)
-----------
select convert(char(10),[date],21),min(total_num)
from (select [date] , total_num=sum(num) from tb group by [date]) t
group by convert(char(10),[date],21)
union
select convert(char(10),[date],21),max(total_num)
from (select [date] , total_num=sum(num) from tb group by [date]) t
group by convert(char(10),[date],21)
[解决办法]
select convert(char(13),[date],21),min(total_num)
from (select [date] , total_num=sum(num) from tb group by [date]) t
group by convert(char(13),[date],21)
union
select convert(char(13),[date],21),max(total_num)
from (select [date] , total_num=sum(num) from tb group by [date]) t
group by convert(char(13),[date],21)
-----------------------------------------
由于提供数据的特殊,所以楼上的办法也可实现,但如果拿每小时做一个时段的话,准确的应该是这样的
convert(char(10),[date],21) ---> convert(char(13),[date],21)
[解决办法]
建立楼主最好从使用临时表来考虑，那样速度性能才有大提高，不然使用一条语句，虽然能计算出来，但性能不看好。
这里提供最简单的临时表方法：
create table #t([date] datetime,type varchar(10),num int)
insert into #t([date],type,num) values( '2007-4-1 0:00:00 ' , 'A ' , 2)
insert into #t([date],type,num) values( '2007-4-1 0:00:00 ' , 'B ' , 2)
insert into #t([date],type,num) values( '2007-4-1 0:00:00 ' , 'C ' , 2)
insert into #t([date],type,num) values( '2007-4-1 1:00:00 ' , 'A ' , 7)
insert into #t([date],type,num) values( '2007-4-1 1:00:00 ' , 'B ' , 7)
insert into #t([date],type,num) values( '2007-4-1 1:00:00 ' , 'C ' , 7)
insert into #t([date],type,num) values( '2007-4-1 21:00:00 ' , 'A ' , 8)
insert into #t([date],type,num) values( '2007-4-1 21:00:00 ' , 'B ' , 8)
insert into #t([date],type,num) values( '2007-4-1 21:00:00 ' , 'C ' , 8)
insert into #t([date],type,num) values( '2007-4-30 0:00:00 ' , 'A ' , 5)
insert into #t([date],type,num) values( '2007-4-30 0:00:00 ' , 'B ' , 5)
insert into #t([date],type,num) values( '2007-4-30 0:00:00 ' , 'C ' , 5)
insert into #t([date],type,num) values( '2007-4-30 1:00:00 ' , 'A ' , 7)
insert into #t([date],type,num) values( '2007-4-30 1:00:00 ' , 'B ' , 7)
insert into #t([date],type,num) values( '2007-4-30 1:00:00 ' , 'C ' , 7)
insert into #t([date],type,num) values( '2007-4-30 21:00:00 ', 'A ', 10)
insert into #t([date],type,num) values( '2007-4-30 21:00:00 ', 'B ', 10)
insert into #t([date],type,num) values( '2007-4-30 21:00:00 ', 'C ', 10)
SELECT [date],SUM(num)AS total_num INTO #T1 FROM #T GROUP BY [date]
SELECT * FROM
(SELECT * FROM #T1 AS A WHERE NOT EXISTS(SELECT 1 FROM #T1 AS B WHERE CONVERT(char(10),A.[date],120)=CONVERT(char(10),B.[date],120)
AND B.total_num> A.total_num)
UNION ALL SELECT * FROM #T1 AS A WHERE NOT EXISTS(SELECT 1 FROM #T1 AS B WHERE CONVERT(char(10),A.[date],120)=CONVERT(char(10),B.[date],120)
AND B.total_num <A.total_num)
) AS S
ORDER BY [date]
DROP TABLE #T,#T1
/*
2007-04-01 00:00:00.0006
2007-04-01 21:00:00.00024
2007-04-30 00:00:00.00015
2007-04-30 21:00:00.00030
*/
[解决办法]
同意楼上意见 如此多的纪录 建议使用临时表
