我有以下数据
TLi serialnumber line passfail entrydatetime
35SD215 220XSS1 Line4 1 2014/5/13 9:21
35SD215 220XSS1 Line4 1 2014/5/13 9:24
35SD150 231XSS1 Line4 0 2014/5/13 16:45
35SD150 231XSS1 Line4 0 2014/5/14 16:10
35SD150 290XSS1 Line4 0 2014/5/13 10:59
35SD215 3TZWSS1 Line4 0 2014/5/12 16:19
35SD215 3TZWSS1 Line4 0 2014/5/14 15:57
35SD150 3W0XSS1 Line4 0 2014/5/13 15:14
35SD150 3W0XSS1 Line4 0 2014/5/13 21:50
35SD150 3W0XSS1 Line4 0 2014/5/14 17:05
35SD215 410XSS1 Line4 0 2014/5/13 9:10
35SD215 410XSS1 Line4 0 2014/5/14 16:38
35SD150 421XSS1 Line4 0 2014/5/13 16:22
35SD150 421XSS1 Line4 1 2014/5/13 18:54
如何能通过一条SQL语句得出以下的结果(根据时间得到最后一笔的数据) 请高人帮忙
TLi serialnumber line passfail entrydatetime
35SD215 220XSS1 Line4 1 2014/5/13 9:24
35SD150 231XSS1 Line4 0 2014/5/14 16:10
35SD150 290XSS1 Line4 0 2014/5/13 10:59
35SD215 3TZWSS1 Line4 0 2014/5/14 15:57
35SD150 3W0XSS1 Line4 0 2014/5/14 17:05
35SD215 410XSS1 Line4 0 2014/5/14 16:38
35SD150 421XSS1 Line4 1 2014/5/13 18:54
------解决方案--------------------
试试这个：
SELECT TLi,serialnumber,line,passfail,entrydatetime
from
(
SELECT TLi,serialnumber,line,passfail,entrydatetime,
row_number() OVER(PARTITION BY TLi,serialnumber,line,passfail ORDER BY entrydatetime desc) rownum
FROM tb
)t
WHERE rownum = 1
------解决方案--------------------
create table #TB (
TLi varchar(20),
serialnumber varchar(20),
line varchar(10),
passfail bit,
entrydatetime datetime
)
insert into #TB
select '35SD215','220XSS1', 'Line4', 1, '2014/5/13 9:21' union all
select '35SD215','220XSS1', 'Line4', 1, '2014/5/13 9:24' union all
select '35SD150','231XSS1', 'Line4', 0, '2014/5/13 16:45' union all
select '35SD150','231XSS1', 'Line4', 0, '2014/5/14 16:10' union all
select '35SD150','290XSS1', 'Line4', 0, '2014/5/13 10:59' union all
select '35SD215','3TZWSS1', 'Line4', 0, '2014/5/12 16:19' union all
select '35SD215','3TZWSS1', 'Line4', 0, '2014/5/14 15:57' union all
select '35SD150','3W0XSS1', 'Line4', 0, '2014/5/13 15:14' union all
select '35SD150','3W0XSS1', 'Line4', 0, '2014/5/13 21:50' union all
select '35SD150','3W0XSS1', 'Line4', 0, '2014/5/14 17:05' union all
select '35SD215','410XSS1', 'Line4', 0, '2014/5/13 9:10' union all
select '35SD215','410XSS1', 'Line4', 0, '2014/5/14 16:38' union all
select '35SD150','421XSS1', 'Line4', 0, '2014/5/13 16:22' union all
select '35SD150','421XSS1', 'Line4', 1, '2014/5/13 18:54'
select a.* from #TB a inner join
(
select TLi,serialnumber,passfail,max(entrydatetime) as maxtime from #TB
group by TLi,serialnumber,passfail
) b
on a.TLi=b.TLi and a.serialnumber=b.serialnumber and a.passfail=b.passfail and a.entrydatetime=b.maxtime
试试看是你的结果吗？