none
Group By Having语句请教 RRS feed

  • Pergunta

  • 表A有如下数据:
    ID        通道      post          LogOnTime
    001         1       收银         2009-1-1   10:10:10
    002         1       登记         2009-1-1   10:10:13
    003         1       开票         2009-1-1   10:11:12
    004         2       收银         2009-1-2   9:00:00
    005         2       登记         2009-1-2   9:01:20
    006         2       开票         2009-1-2   9:12:30
    001         1       收银         2009-1-3   10:10:10
    002         1       登记         2009-1-3   10:10:13
    003         1       开票         2009-1-3   10:11:12
    004         2       收银         2009-1-4   9:00:00
    005         2       登记         2009-1-4   9:01:20
    006         2       开票         2009-1-4   9:12:30 
    .
    .
    .
    想得到按照通道分组后,时间最新的具有各个不同post值的记录,
    预得到如下信息:
    001         1       收银         2009-1-3   10:10:10
    002         1       登记         2009-1-3   10:10:13
    003         1       开票         2009-1-3   10:11:12
    004         2       收银         2009-1-4   9:00:00
    005         2       登记         2009-1-4   9:01:20
    006         2       开票         2009-1-4   9:12:30
    是使用Group By Having语句吗?该怎么实现呢?谢谢
    guiwenyang
    quarta-feira, 21 de outubro de 2009 12:43

Respostas

  • 恩,那就直接用Group by好了
    select *
    from A
    where LogOnTime in
    	(
    	select max(LogOnTime) 
    	from A
    	group by 通道,post
    	)
    
    • Marcado como Resposta guiwenyang sexta-feira, 23 de outubro de 2009 02:20
    quarta-feira, 21 de outubro de 2009 13:48
    Moderador

Todas as Respostas

  • 楼主如果用的是SQLServer2005或者2008的话,用Rank排行函数最适合你了。

    --测试环境
    create table A (ID varchar(10), 通道 int, post varchar(10), LogOnTime datetime)
    insert into A
    select '001',1,'收银','2009-1-1 10:10:10'
    union all select '002',1,'登记','2009-1-1 10:10:13'
    union all select '003',1,'开票','2009-1-1 10:11:12'
    union all select '004',2,'收银','2009-1-2 9:00:00'
    union all select '005',2,'登记','2009-1-2 9:01:20'
    union all select '006',2,'开票','2009-1-2 9:12:30'
    union all select '001',1,'收银','2009-1-3 10:10:10'
    union all select '002',1,'登记','2009-1-3 10:10:13'
    union all select '003',1,'开票','2009-1-3 10:11:12'
    union all select '004',2,'收银','2009-1-4 9:00:00'
    union all select '005',2,'登记','2009-1-4 9:01:20'
    union all select '006',2,'开票','2009-1-4 9:12:30'  
    
    --查询
    select ID , 通道, post, LogOnTime
    from 
    	(select *
    		,RANK() OVER (PARTITION BY 通道,post order by LogOnTime desc) as RANK
    	from A
    	) alias
    where RANK=1
    order by ID
    /*
    	ID         通道          post       LogOnTime
    	---------- ----------- ---------- -----------------------
    	001        1           收银         2009-01-03 10:10:10.000
    	002        1           登记         2009-01-03 10:10:13.000
    	003        1           开票         2009-01-03 10:11:12.000
    	004        2           收银         2009-01-04 09:00:00.000
    	005        2           登记         2009-01-04 09:01:20.000
    	006        2           开票         2009-01-04 09:12:30.000
    
    	(6 行受影响)
    */
    
    --清理环境
    drop table A
    quarta-feira, 21 de outubro de 2009 13:25
    Moderador
  • 我想使用标准SQL语句,因为我要求查询既要支持SQLServer又支持Oracle
    guiwenyang
    quarta-feira, 21 de outubro de 2009 13:40
  • 恩,那就直接用Group by好了
    select *
    from A
    where LogOnTime in
    	(
    	select max(LogOnTime) 
    	from A
    	group by 通道,post
    	)
    
    • Marcado como Resposta guiwenyang sexta-feira, 23 de outubro de 2009 02:20
    quarta-feira, 21 de outubro de 2009 13:48
    Moderador
  • 你可以试试这个,不过注意如果有最新更新时间一样的纪录都会列出来的
    select * from table1 as t1
    where t1.[LogOnTime] =
    (
      select top 1 t2.[LogOnTime]
      from table1 as t2
      where t1.channel=t2.channel and t1.post=t2.post
      order by t2.[LogOnTime] desc
    )
    order by t1.channel, t1.post, t1.[LogOnTime] desc
    quarta-feira, 21 de outubro de 2009 14:20
  • select 
        *
    from ta a
    where not exists(select 1 from ta where id = a.id and LogOnTime> a.LogOnTime)

    More: blog.csdn.net/happyflystone
    quarta-feira, 21 de outubro de 2009 16:13
  • Oracle支持group by 和exists函数。
    quinta-feira, 22 de outubro de 2009 01:31