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

[wangyangcheng]

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

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

谢谢！

[caorobby]

没有任何关联联起来没有实际意义吧 如果每个只有一条记录的话
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

[caorobby]

哦理解错了

[wangyangcheng]

回复人： 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


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

[wangyangcheng]

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

[xuesaner]

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

[wangyangcheng]

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

[caorobby]

用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

[wangyangcheng]

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

[xuesaner]

技术问题一块讨论：[e-mail] QQ:124746700

[xuesaner]

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

[allureni]

有这么复杂吗？

[tttl366]

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

[borgvardt]

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

[bamimo2001]

/* 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

[shentw]

一定要有保存时间信息的这个字段，一个未整理的代码：
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
)

[xuesaner]

为什么不用我的呢？

[云中客]

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

[佳良88]

我觉得上面的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

[cary2006]

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

[cary2006]

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