关于分组求最大值问题，高手解决

Question

date                     name
------------------------------------------
2008.08.08 07:00:00      A1-1
2008.08.08 08:00:00      A2-1
2008.08.08 09:00:00      A2-2
2008.08.08 10:00:00      A1-2
2008.08.08 11:00:00      A1-3
2008.08.08 12:00:00      B1-1
2008.08.08 13:00:00      B1-2
2008.08.08 14:00:00      B2-2
2008.08.09 07:00:00      A1-1
2008.08.09 08:00:00      A1-2
2008.08.09 09:00:00      A2-1
2008.08.09 10:00:00      A2-2
2008.08.09 11:00:00      A2-3
2008.08.09 12:00:00      B1-1
2008.08.09 13:00:00      B2-1
2008.08.09 14:00:00      B2-2需要得到的结果：
date                     name
--------------------------------------
2008.08.08 09:00:00      A2-2
2008.08.08 11:00:00      A1-3
2008.08.08 13:00:00      B1-2
2008.08.08 14:00:00      B2-2
2008.08.09 08:00:00      A1-2
2008.08.09 11:00:00      A2-3
2008.08.09 12:00:00      B1-1
2008.08.09 14:00:00      B2-2
按照时间排序，每天都有重复的A?-?,B?-?,要求出每天最大的A1-max，A2-max，A？-max，B1-max，B2-max，B？-max
请高手指教

Answer 1

SELECT MAX(CAST(RIGHT(NAME,1) AS INT)) FROM TB
GROUP BY NAME

Answer 2

select
  *
from
  tb t
where
  not exists(select 1 
             from tb 
             where datediff(day,[date],t.[date])=0 
             and cast(right(name,len(name)-charindex('-',name)) as int)>cast(right(t.name,len(t.name)-charindex('-',t.name)) as int)
            )

Answer 3

漏了一个条件，修正一下
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([date] datetime,[name] varchar(4))
insert [tb]
select '2008.08.08 07:00:00','A1-1' union all
select '2008.08.08 08:00:00','A2-1' union all
select '2008.08.08 09:00:00','A2-2' union all
select '2008.08.08 10:00:00','A1-2' union all
select '2008.08.08 11:00:00','A1-3' union all
select '2008.08.08 12:00:00','B1-1' union all
select '2008.08.08 13:00:00','B1-2' union all
select '2008.08.08 14:00:00','B2-2' union all
select '2008.08.09 07:00:00','A1-1' union all
select '2008.08.09 08:00:00','A1-2' union all
select '2008.08.09 09:00:00','A2-1' union all
select '2008.08.09 10:00:00','A2-2' union all
select '2008.08.09 11:00:00','A2-3' union all
select '2008.08.09 12:00:00','B1-1' union all
select '2008.08.09 13:00:00','B2-1' union all
select '2008.08.09 14:00:00','B2-2'
 
---查询---
select
  *
from
  tb t
where
  not exists(select 1 
             from tb 
             where datediff(day,[date],t.[date])=0  
             and left(name,2)=left(t.name,2)
             and cast(right(name,len(name)-charindex('-',name)) as int)>cast(right(t.name,len(t.name)-charindex('-',t.name)) as int)
            )---结果---
date                                                   name 
------------------------------------------------------ ---- 
2008-08-08 09:00:00.000                                A2-2
2008-08-08 11:00:00.000                                A1-3
2008-08-08 13:00:00.000                                B1-2
2008-08-08 14:00:00.000                                B2-2
2008-08-09 08:00:00.000                                A1-2
2008-08-09 11:00:00.000                                A2-3
2008-08-09 12:00:00.000                                B1-1
2008-08-09 14:00:00.000                                B2-2（所影响的行数为 8 行）

Answer 4

declare @tb table(date datetime,name varchar(5))
insert @tb 
select 
'2008.08.08 07:00:00' ,     'A1-1' union all select
'2008.08.08 08:00:00'  ,    'A2-1' union all select
'2008.08.08 09:00:00'   ,   'A2-2' union all select
'2008.08.08 10:00:00'     , 'A1-2' union all select
'2008.08.08 11:00:00'    ,  'A1-3' union all select
'2008.08.08 12:00:00'    ,  'B1-1' union all select 
'2008.08.08 13:00:00'    ,  'B1-2' union all select 
'2008.08.08 14:00:00'    ,  'B2-2' union all select 
'2008.08.09 07:00:00'    ,  'A1-1' union all select 
'2008.08.09 08:00:00'    ,  'A1-2' union all select 
'2008.08.09 09:00:00'    ,  'A2-1' union all select 
'2008.08.09 10:00:00'    ,  'A2-2' union all select 
'2008.08.09 11:00:00'    ,  'A2-3' union all select 
'2008.08.09 12:00:00'    ,  'B1-1' union all select 
'2008.08.09 13:00:00'    ,  'B2-1' union all select 
'2008.08.09 14:00:00'    ,  'B2-2' select * from @tb t
where not exists(select * from @tb where convert(varchar(10),date,120)=convert(varchar(10),t.date,120) and left(name,2)=left(t.name,2) and cast(right(name,1)as int)>cast(right(t.name,1)as int))date                    name
----------------------- -----
2008-08-08 09:00:00.000 A2-2
2008-08-08 11:00:00.000 A1-3
2008-08-08 13:00:00.000 B1-2
2008-08-08 14:00:00.000 B2-2
2008-08-09 08:00:00.000 A1-2
2008-08-09 11:00:00.000 A2-3
2008-08-09 12:00:00.000 B1-1
2008-08-09 14:00:00.000 B2-2

Answer 5

josy他的A？可能是A12-99，所以left(name,2)=left(t.name,2)
不对啊

Answer 6

CREATE TABLE TEST(DATE DATETIME,NAME VARCHAR(10))
INSERT INTO TEST
SELECT '2008.08.08 07:00:00 '  ,   'A1-1 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'A2-1 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'A2-2 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'A1-2 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'A1-3 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'B1-1 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'B1-2 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'B2-2 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A1-1 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A1-2 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A2-1 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A2-2 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A2-3 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'B1-1 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'B2-1 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'B2-2 ' GO--DROP TABLE TEST
SELECT LEFT(NAME,2),'-',MAX(CAST(RIGHT(NAME,2) AS INT)) FROM TEST
GROUP BY (LEFT(NAME,2)),DATE                      
---- ---- ----------- 
A1   -    3
A2   -    2
B1   -    2
B2   -    2
A1   -    2
A2   -    3
B1   -    1
B2   -    2（所影响的行数为 8 行）

Answer 7

9楼：A？后面是可以为2为的数字，所以LEFT(NAME,2)不对啊，A？或者A？？是不确定的

Answer 8

嗯，刚说完6楼就发现了，再改一下
---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([date] datetime,[name] varchar(10))
insert [tb]
select '2008.08.08 07:00:00','A22-11' union all
select '2008.08.08 08:00:00','A22-1' union all
select '2008.08.08 09:00:00','A22-2' union all
select '2008.08.08 10:00:00','A1-2' union all
select '2008.08.08 11:00:00','A1-3' union all
select '2008.08.08 12:00:00','B1-1' union all
select '2008.08.08 13:00:00','B1-2' union all
select '2008.08.08 14:00:00','B2-2' union all
select '2008.08.09 07:00:00','A1-1' union all
select '2008.08.09 08:00:00','A1-2' union all
select '2008.08.09 09:00:00','A2-1' union all
select '2008.08.09 10:00:00','A2-2' union all
select '2008.08.09 11:00:00','A2-3' union all
select '2008.08.09 12:00:00','B1-1' union all
select '2008.08.09 13:00:00','B2-1' union all
select '2008.08.09 14:00:00','B2-2'
 
---查询---
select
  *
from
  tb t
where
  not exists(select 1 
             from tb 
             where datediff(day,[date],t.[date])=0  
             and left(name,charindex('-',name)-1)=left(t.name,charindex('-',name)-1)
             and cast(right(name,len(name)-charindex('-',name)) as int)>cast(right(t.name,len(t.name)-charindex('-',t.name)) as int)
            )---结果---
date                                                   name       
------------------------------------------------------ ---------- 
2008-08-08 07:00:00.000                                A22-11
2008-08-08 11:00:00.000                                A1-3
2008-08-08 13:00:00.000                                B1-2
2008-08-08 14:00:00.000                                B2-2
2008-08-09 08:00:00.000                                A1-2
2008-08-09 11:00:00.000                                A2-3
2008-08-09 12:00:00.000                                B1-1
2008-08-09 14:00:00.000                                B2-2（所影响的行数为 8 行）

Answer 9

CREATE TABLE TEST(DATE DATETIME,NAME VARCHAR(10))
INSERT INTO TEST
SELECT '2008.08.08 07:00:00 '  ,   'A1-1 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'A2-1 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'A2-2 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'A1-2 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'A1-3 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'B1-1 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'B1-2 ' UNION ALL
SELECT '2008.08.08 07:00:00 '  ,   'B2-2 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A1-1 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A1-2 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A2-1 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A2-2 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'A2-3 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'B1-1 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'B2-1 ' UNION ALL
SELECT '2008.08.09 07:00:00 '  ,   'B2-2 ' GO--DROP TABLE TEST
SELECT LEFT(NAME,CHARINDEX('-',NAME)-1),'-',MAX(CAST(RIGHT(NAME,2) AS INT)) FROM TEST
GROUP BY (LEFT(NAME,CHARINDEX('-',NAME)-1)),DATE
                            
---------- ---- ----------- 
A1         -    3
A2         -    2
B1         -    2
B2         -    2
A1         -    2
A2         -    3
B1         -    1
B2         -    2（所影响的行数为 8 行）

Answer 10

josy，在问个问题，数据中在同一天出现了重复的A1-1或者其他，但是我想取得同一天内时间最大的那个A1-1应该怎么处理啊？

Answer 11

SELECT * 
FROM TB 
WHERE 
 NOT EXISTS(SELECT 1 
            FROM TB 
            WHERE NAME=T.NAME         --相同名称
            AND DATEDIFF(DAY,[TIME],T.[TIME])=0  --同一天
            AND [TIME]>T.[TIME]  --主查询取时间最大的 
            )

Answer 12

SELECT * 
FROM TB T
WHERE 
 NOT EXISTS(SELECT 1 
            FROM TB 
            WHERE NAME=T.NAME         --相同名称
            AND DATEDIFF(DAY,[TIME],T.[TIME])=0  --同一天
            AND [TIME]>T.[TIME]  --主查询取时间最大的 
            )

Answer 13

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([date] datetime,[name] varchar(10))
insert [tb]
select '2008.08.08 07:00:00','A22-11' union all
select '2008.08.08 08:00:00','A22-1' union all
select '2008.08.08 09:00:00','A22-2' union all
select '2008.08.08 10:00:00','A1-2' union all
select '2008.08.08 11:00:00','A1-3' union all
select '2008.08.08 12:00:00','B1-1' union all
select '2008.08.08 11:00:00','B1-1' union all   --add
select '2008.08.08 13:00:00','B1-2' union all
select '2008.08.08 14:00:00','B2-2' union all
select '2008.08.09 07:00:00','A1-1' union all
select '2008.08.09 08:00:00','A1-2' union all
select '2008.08.09 09:00:00','A2-1' union all
select '2008.08.09 10:00:00','A2-2' union all
select '2008.08.09 11:00:00','A2-3' union all
select '2008.08.09 12:00:00','B1-1' union all
select '2008.08.09 13:00:00','B2-1' union all
select '2008.08.09 14:00:00','B2-2'
 
---查询---
select
  *
from
  tb t
where
  not exists(select 1 
             from tb 
             where datediff(day,[date],t.[date])=0  
             and left(name,charindex('-',name)-1)=left(t.name,charindex('-',name)-1)
             and cast(right(name,len(name)-charindex('-',name)) as int)>cast(right(t.name,len(t.name)-charindex('-',t.name)) as int)
            )
and
   not exists (select 1 from tb where name=t.name and date>t.date) date                    name
----------------------- ----------
2008-08-08 07:00:00.000 A22-11
2008-08-08 11:00:00.000 A1-3
2008-08-08 13:00:00.000 B1-2
2008-08-09 08:00:00.000 A1-2
2008-08-09 11:00:00.000 A2-3
2008-08-09 12:00:00.000 B1-1
2008-08-09 14:00:00.000 B2-2(7 行受影响)
?

Answer 14

取出的结果不对，还是存在重复 ，当我们数据存在多条最大时则都会取出，比如A1-5为最大，如果有在同一天存在多天A1-5，则都取出来了，而希望的结果是，时间最大的A1-5取出，麻烦再次解决下，顺便问下如何给分？呵呵，新手

Answer 15

--创建测试表create table testtable([date] datetime, [name] varchar(255))
goinsert testtable
select '2008.08.08 07:00:00','A1-1' union all
select '2008.08.08 08:00:00','A2-1' union all
select '2008.08.08 09:00:00','A2-2' union all
select '2008.08.08 10:00:00','A1-2' union all
select '2008.08.08 11:00:00','A1-3' union all
select '2008.08.08 12:00:00','B1-1' union all
select '2008.08.08 13:00:00','B1-2' union all
select '2008.08.08 14:00:00','B2-2' union all
select '2008.08.09 07:00:00','A1-1' union all
select '2008.08.09 08:00:00','A1-2' union all
select '2008.08.09 09:00:00','A2-1' union all
select '2008.08.09 10:00:00','A2-2' union all
select '2008.08.09 11:00:00','A2-3' union all
select '2008.08.09 12:00:00','B1-1' union all
select '2008.08.09 13:00:00','B2-1' union all
select '2008.08.09 14:00:00','B2-2'
go--创建得到日期函数CREATE FUNCTION dayfiled (@inputdate datetime)
RETURNS varchar(2)
AS
begin
declare @redayfild varchar(2)
select @redayfild  = case (datename(day,@inputdate)) when '1' then  '0' + datename(day,@inputdate)
when '2' then  '0' + datename(day,@inputdate)
when '3' then  '0' + datename(day,@inputdate)
when '4' then  '0' + datename(day,@inputdate)
when '5' then  '0' + datename(day,@inputdate)
when '6' then  '0' + datename(day,@inputdate)
when '7' then  '0' + datename(day,@inputdate)
when '8' then  '0' + datename(day,@inputdate)
when '9' then  '0' + datename(day,@inputdate)
else datename(day,@inputdate)
end
RETURN(@redayfild)
end
--SQL Code:
select e.[date],e.[name] from (
select datetimefiled,max(namefield) namefield from (
select datetimefiled,namefield,left(namefield,2)namefield2 from(
select datename(year,[date])+'.'+datename(month,[date])+'.'+dbo.dayfiled([date]) datetimefiled,left([name],charindex('-',[name],0)-1) + right([name],len([name])-charindex('-',[name],0)) namefield from testtable 
) a
)b  
group by datetimefiled,namefield2)d 
left join (
select [date] ,[name],datename(year,[date])+'.'+datename(month,[date])+'.'+dbo.dayfiled([date]) datetimefiled,left([name],charindex('-',[name],0)-1) + right([name],len([name])-charindex('-',[name],0)) namefield from testtable 
) e
on d.datetimefiled = e.datetimefiled  and d.namefield = e.namefield
order by [date]--得到的结果：date                    name
-------------------------------
2008-08-08 09:00:00.000 A2-2
2008-08-08 11:00:00.000 A1-3
2008-08-08 13:00:00.000 B1-2
2008-08-08 14:00:00.000 B2-2
2008-08-09 08:00:00.000 A1-2
2008-08-09 11:00:00.000 A2-3
2008-08-09 12:00:00.000 B1-1
2008-08-09 14:00:00.000 B2-2