如果只是需要得到小时及最大的COL003
Select Left(COL001,2) As 小时,Max(COL003) As COL003 from csdl Group By Left(COL001,2)如果说需要得到这条记录所有的相关信息
Select A.*
from csdl A
Inner Join (Select Left(COL001,2) As COL001,Max(COL003) As COL003 from csdl Group By Left(COL001,2)) B
On Left(A.COL001,2)=B.COL001 And A.COL003=B.COL003
Select Left(COL001,2) As 小时,Max(COL003) As COL003 from csdl Group By Left(COL001,2)如果说需要得到这条记录所有的相关信息
Select A.*
from csdl A
Inner Join (Select Left(COL001,2) As COL001,Max(COL003) As COL003 from csdl Group By Left(COL001,2)) B
On Left(A.COL001,2)=B.COL001 And A.COL003=B.COL003
col002 MAXRENSHU
00:01 587
00:16 560
00:31 531
00:46 50301:01 481
01:16 448
01:31 429
01:46 401
..........
的结果......
就是Left(COL001,2)
把col001当主键,选出时段列中的最大值就好了
这个应当可以了吧
Create Table csdl
(COL001 Varchar(5),
COL002 Nvarchar(10),
COL003 Int,
COL004 Varchar(10),
COL005 Varchar(10))
--插入数据
Insert csdl Values('00:01', N'總共', 587, NULL, '2005.06.27')
Insert csdl Values('00:16', N'總共', 560, NULL, '2005.06.27')
Insert csdl Values('00:31', N'總共', 531, NULL, '2005.06.27')
Insert csdl Values('00:46', N'總共', 503, NULL, '2005.06.27')
Insert csdl Values('01:01', N'總共', 481, NULL, '2005.06.27')
Insert csdl Values('01:16', N'總共', 448, NULL, '2005.06.27')
Insert csdl Values('01:31', N'總共', 429, NULL, '2005.06.27')
Insert csdl Values('01:46', N'總共', 401, NULL, '2005.06.27')
Insert csdl Values('02:01', N'總共', 363, NULL, '2005.06.27')
Insert csdl Values('02:16', N'總共', 350, NULL, '2005.06.27')
Insert csdl Values('02:31', N'總共', 326, NULL, '2005.06.27')
Insert csdl Values('02:46', N'總共', 291, NULL, '2005.06.27')
--测试
--只要最大值及小时
Select Left(COL001,2) As 小时,Max(COL003) As COL003 from csdl Group By Left(COL001,2)
--最大的COL003的所有相关信息
Select A.*
from csdl A
Inner Join (Select Left(COL001,2) As COL001,Max(COL003) As COL003 from csdl Group By Left(COL001,2)) B
On Left(A.COL001,2)=B.COL001 And A.COL003=B.COL003
--删除测试环境
Drop Table csdl
--结果
/*
小时 COL003
00 587
01 481
02 363COL001 COL002 COL003 COL004 COL005
00:01 總共 587 NULL 2005.06.27
01:01 總共 481 NULL 2005.06.27
02:01 總共 363 NULL 2005.06.27
*/
create table A
(
col001 varchar(20),
col002 varchar(20),
col003 int,
col004 varchar(20),
col005 varchar(10)
)
insert A
select '00:01','總共',587,NULL,'2005.06.27' union
select '00:16','總共',560,NULL,'2005.06.27' union
select '00:31','總共',531,NULL,'2005.06.27' union
select '00:46','總共',503,NULL,'2005.06.27' union
select '01:01','總共',481,NULL,'2005.06.27' union
select '01:16','總共',448,NULL,'2005.06.27' union
select '01:31','總共',429,NULL,'2005.06.27' union
select '01:46','總共',401,NULL,'2005.06.27' union
select '02:01','總共',363,NULL,'2005.06.27' union
select '02:16','總共',350,NULL,'2005.06.27' union
select '02:31','總共',326,NULL,'2005.06.27' union
select '02:46','總共',291,NULL,'2005.06.27' union
select '03:01','總共',271,NULL,'2005.06.27' union
select '03:16','總共',265,NULL,'2005.06.27' union
select '03:31','總共',245,NULL,'2005.06.27' union
select '03:46','總共',228,NULL,'2005.06.27' union
select '04:01','總共',215,NULL,'2005.06.27' union
select '04:16','總共',219,NULL,'2005.06.27' union
select '04:31','總共',212,NULL,'2005.06.27' union
select '04:46','總共',203,NULL,'2005.06.27' union
select '05:01','總共',197,NULL,'2005.06.27' union
select '05:16','總共',188,NULL,'2005.06.27' union
select '05:31','總共',190,NULL,'2005.06.27' union
select '05:46','總共',191,NULL,'2005.06.27' union
select '06:01','總共',194,NULL,'2005.06.27' union
select '06:16','總共',179,NULL,'2005.06.27' union
select '06:31','總共',190,NULL,'2005.06.27' union
select '06:46','總共',190,NULL,'2005.06.27' union
select '07:01','總共',187,NULL,'2005.06.27' union
select '07:16','總共',188,NULL,'2005.06.27' union
select '07:31','總共',206,NULL,'2005.06.27' union
select '08:01','總共',259,NULL,'2005.06.27' union
select '08:16','總共',282,NULL,'2005.06.27' union
select '08:31','總共',303,NULL,'2005.06.27' union
select '08:46','總共',338,NULL,'2005.06.27' union
select '09:01','總共',378,NULL,'2005.06.27' union
select '09:16','總共',393,NULL,'2005.06.27' union
select '09:31','總共',412,NULL,'2005.06.27' union
select '09:46','總共',421,NULL,'2005.06.27' union
select '10:01','總共',445,NULL,'2005.06.27' union
select '10:16','總共',479,NULL,'2005.06.27' union
select '10:31','總共',496,NULL,'2005.06.27' union
select '10:46','總共',530,NULL,'2005.06.27' union
select '11:01','總共',533,NULL,'2005.06.27' union
select '11:16','總共',545,NULL,'2005.06.27' union
select '11:31','總共',544,NULL,'2005.06.27' union
select '11:46','總共',540,NULL,'2005.06.27' union
select '12:16','總共',568,NULL,'2005.06.27' union
select '12:31','總共',618,NULL,'2005.06.27' union
select '12:46','總共',659,NULL,'2005.06.27'gocreate proc findmax
as
declare @tb table(ID int identity,col001 varchar(20),col003 int)
insert @tb(col001,col003) select col001,col003 from A
select T.col001,T.col003 from @tb T
where not exists(select 1 from @tb where left(col001,2)=left(T.col001,2) and col003>T.col003)go--测试
exec findmax --删除测试环境
drop procedure findmax
drop table A--结果
/*col001 col003
-------------------- -----------
00:01 587
01:01 481
02:01 363
03:01 271
04:16 219
05:01 197
06:01 194
07:31 206
08:46 338
09:46 421
10:46 530
11:16 545
12:46 659(所影响的行数为 13 行)
*/
create proc findmax
as
select T.col001,T.col003 from A T
where not exists(select 1 from A where left(col001,2)=left(T.col001,2) and col003>T.col003)
go
1.首先我想明白了为什么GROUP BY为什么会选择所有记录的缘故.GROUP BY是按照同一个内容分组,比如按照班分组,按照地区分组什么的.要求分组的内容必须一致,也就是说比如A班有张三李四,B班有周五赵六.......但我这个需求的COL002是时间,根本没有相同的.所以GROUP BY会认为每一个时间点就是1组.因此会把所有的记录都选择出来.2.既然我们要找的是最大值,所以自然的思路就是MAX(),其实我们就是忽略了SQL有排序的功能啊.呵呵.
因此,不必要用建立临时表然后再比较.这样本身效率就很低.其实简单的方法就是将我开题中的存储过程的SQL语句改成:
select top 1 col002 as MYTIME,col003 as MAXRENSHU from csdl where col002 like @can order by col003 desc
实际测试结果如下:
MYTIME MAXRENSHU
00:01 587
01:01 481
02:01 363
测试成功!
这样1条SQL语句就搞定了.呵呵.感谢大家!马上结贴.
select top 1 col002 as MYTIME,col003 as MAXRENSHU from csdl where col002 like @can order by col003 desc能得到这个结果吗、?
MYTIME MAXRENSHU
00:01 587
01:01 481
02:01 363