每人取一条最晚的记录，求sql语句

M个用户投了N张票，每人可能超过一张标。

现在每人只取最晚投的一张作为有效票，如何写句子？

谢谢！

38 个解决方案

#1

能不能，，，具体一点啊。谢谢。

#2

谢谢啦！
table中有
voter, time, champion, 1strunnerup, 2ndrunnerup

每个voter可能有多笔记录，现在只取其time最晚的一条。

要取出全部这五个字段。

#3

select * from table x
where not exists(select * from table where voter=x.voter and time>x.time)

#4

select top 1 * from table order by time DESC

#5

select 投票人,  max(投票时间)  投票时间, 投票结果
      from 投票表
      group by 投票人, 投票时间

#6

运行错误，就执行

select 投票人, 投票时间, 投票结果
  from 投票表
  where 投票人, 投票时间 in
    select 投票人,  max(投票时间)  投票时间
      from 投票表
      group by 投票人, 投票时间

#7

select top 1 * from table where 投票人=aa order by time DESC

#8

楼主下面语句应该可以：
select voter, time, champion, 1strunnerup, 2ndrunnerup from table1 a
where not exists(select 1 from table1 where voter=a.voter and time>a.time)

#9

select voter, time, champion, 1strunnerup, 2ndrunnerup from table A
where not exists (select 1 from table where voter=a.voter and time>a.time)

#10

回复人： lxzm1001(蓝星之梦) ( ) 信誉：100  2005-9-18 2:27:35  得分: 0


select * from table x
where not exists(select * from table where voter=x.voter and time>x.time)


回复人： wgsasd311(自强不息) ( ) 信誉：100  2005-09-18 09:31:00  得分: 0

   楼主下面语句应该可以：
select voter, time, champion, 1strunnerup, 2ndrunnerup from table1 a
where not exists(select 1 from table1 where voter=a.voter and time>a.time)


----

以上两位大人的是一样的，但可否具体给我分析一下？
我不太熟悉这种写法。

另外我把他直接建成了一个视图，我觉得这样比用存储过程去插入另一张新表更方便。

#11

回复人： bigben2008(ben)
select 投票人, 投票时间, 投票结果
  from 投票表
  where 投票人, 投票时间 in
    select 投票人,  max(投票时间)  投票时间   //最后这个投票时间什么意思？我去掉之后报错
      from 投票表
      group by 投票人, 投票时间

#12

大致思路是：查询每一个人中不存在时间比自己时间大的一条记录（也就是每个人最晚投的票）

#13

max(投票时间) 投票时间 //最后这个投票时间什么意思？我去掉之后报错
＝＝＝＞是别名，写全就是：
max(投票时间) as '投票时间'

#14

谢谢wgsasd311(自强不息)~
我现在已将下面的句子作为一个视图view:
select voter, time, champion, 1strunnerup, 2ndrunnerup from table1 a
where not exists(select 1 from table1 where voter=a.voter and time>a.time)

然后我写了三条语句分别将冠亚季军的各自的票数统计出来：
select champion, count( champion ) from view group by champion
select 1strunnerup, count( 1strunnerup ) from view group by 1strunnerup
select 2ndrunnerup, count( 2ndrunnerup ) from view group by 2ndrunnerup

如何将三条语子写成一条（以实现我的目的)？
因我想将他们保存到另一张view里面。
再次感谢！

#15

#16

没有任何关联联起来没有实际意义吧如果每个只有一条记录的话
select a.champion,a.ccount,b.lstrunnerup,c.2ndrunerup from
(select 'temp'=1, champion, count( champion ) as ccount from view group by champion) a inner join
(select 'temp'=1, 1strunnerup, count( 1strunnerup ) from view group by 1strunnerup) b on a.temp=b.temp inner join
(select 'temp'=1, 2ndrunnerup, count( 2ndrunnerup ) from view group by 2ndrunnerup) c on a.temp=c.temp

#17

哦理解错了

#18

回复人： wgsasd311(自强不息) ( ) 信誉：100  2005-09-18 10:40:00  得分: 0

   create view v_2 as
select * from (
select champion, count( champion ) as 'num' from [view] group by champion
union all
select [1strunnerup], count( * ) from [view] group by [1strunnerup]
union all
select [2ndrunnerup], count( * ) from [view] group by [2ndrunnerup])aa


---
老大，报语法错误呀

#19

老是报")"附近有语法错误

#20

select a.投票人, a.投票时间, a.投票结果
  from 投票表 a
  where a.投票时间=( select max(投票时间)
      from 投票表 b
      where a.投票人=b.投票人
      group by 投票人, 投票时间)

#21

create view v_2 as
select * from (
select champion, count( champion ) as 'num' from [view] group by champion
union all
select [1strunnerup], count( * ) from [view] group by [1strunnerup]
union all
select [2ndrunnerup], count( * ) from [view] group by [2ndrunnerup])aa

对不起，我刚刚忘记加后面的别名aa了，所以报错。

另外可能您还没有了解我的意图。
我是想从[view]里面得出
          冠军票数   亚军票数   季军票数
CHINA        3         2          1
USA          3         4          5
RUSSIAN      1         2          3

#22

用union all 后没有标识怎样分清冠亚
select * from (
select 'champion', champion, count( champion ) as 'num' from [view] group by champion
union all
select '1',[1strunnerup], count( * ) from [view] group by [1strunnerup]
union all
select '2',[2ndrunnerup], count( * ) from [view] group by [2ndrunnerup]) aa

#23

TO: wgsasd311(自强不息)

所以我试图写成这样：
create view v_2 as
select * from (
select champion, count( champion ) as 'champion' from [view] group by champion
union all
select [1strunnerup], count( * ) as '1strunnerup' from [view] group by [1strunnerup]
union all
select [2ndrunnerup], count( * ) as '2ndrunnerup' from [view] group by [2ndrunnerup])aa

#24

技术问题一块讨论：suncersnow@tom.com QQ:124746700

#25

select a.投票人, a.投票时间, a.投票结果
  from 投票表 a
  where a.投票时间=( select max(b.投票时间)
      from 投票表 b
      where a.投票人=b.投票人
      group by b.投票人, b.投票时间)
技术问题一块讨论,互相学习,我是搞pb的欢迎大家加我：
suncersnow@tom.com  QQ:124746700

#26

#27

select * from (
select '0' as 'mingci', champion, count( champion ) as 'num' from [view] group by champion
union all
select '1',[1strunnerup], count( * ) from [view] group by [1strunnerup]
union all
select '2',[2ndrunnerup], count( * ) from [view] group by [2ndrunnerup]) aa
---
但是这样会报错，说没有为第一列(属于aa列)指定列。

#28

不好意思，上面语句请去掉：
---
但是这样会报错，说没有为第一列(属于aa列)指定列。

#29

一定要有保存时间信息的这个字段，一个未整理的代码：
FROM DC_CustomerStorageDetail A
INNER JOIN DC_fnVerifyCustomerStorageList(@StorageIDs, @StorageDetailIDs) C ON C.StorageDetailID = A.StorageDetailID
AND A.StatusID NOT IN (3, 4, 5) --不良品不需校验
INNER JOIN sms_ProductInfo D WITH(NOLOCK) ON D.PN_Code1 = A.IMEI
AND NOT EXISTS ( --只取最后一条生产纪录
SELECT TOP 1 1
FROM sms_ProductInfo WITH(NOLOCK)
WHERE PN_Code1 = D.PN_Code1
AND PN_CreateDate > D.PN_CreateDate
)

#30

为什么不用我的呢？

#31

可以根据投票时间来确定
或者根据编号来确定

#32

我觉得上面的SQL不够简易,而且效率也不高,我下给出是经典来的,只要改一下相应的字段就可以了:
SELECT   t1.*
    FROM t_opr_bih_register t1,
         (SELECT   patientid_chr, MAX (inpatient_dat) inpatient_dat
              FROM t_opr_bih_register
          GROUP BY patientid_chr) t2
   WHERE t1.patientid_chr = t2.patientid_chr
     AND t1.inpatient_dat = t2.inpatient_dat
ORDER BY t1.patientid_chr

#33

select a.voter, a.time, a.champion, a.1strunnerup, a.2ndrunnerup from table1 a,(select voter,min(time) from table1 group by voter) b where a.voter=b.voter and a.time=b.time

#34

不好意思,因为求最晚,把min改为max

#35

有这么复杂吗？

#36

select *
from table
where time=max(time)
group by 投票人

#37

select distinct(voter), max(time), champion, 1strunnerup, 2ndrunnerup from Table
group by voter

#38

/* table
man -- 投票人
dt -- 投票日间
*/

select *
from table a,
(select max(dt) as dt,man from table group by man) b
where a.man = b.man
and a.dt = b.dt

#1