请教这个查询语句怎么写?
idnumber status lasttime
a 0 2007-08-10
a 1 2007-08-11
a 0 2007-08-12
a 0 2007-08-13
b 1 2007-08-11
b 0 2007-08-12
b 1 2007-08-13
现在我要查询出 status 为 '0 ' 得最后一条记录
即结果为
idnumber status lasttime
a 0 2007-08-13
b 0 2007-08-12
这个该怎么写,先谢谢大家!
[解决办法]
select idnumber,status,max(lasttime) as lasttime
from tablename
where status = '0 '
group by idnumber,status
[解决办法]
---也可以试试这种,不过得不到你b那条记录
Select * From TableName A Where Status= '0 ' And Not Exists
(Select 1 From TableName Where idnumber=A.idnumber And lasttime> A.lasttime)
[解决办法]
Select * From TableName A Where A.Status= '0 ' And Not Exists
(Select 1 From TableName Where idnumber=A.idnumber And lasttime> A.lasttime and A.Status== Status )
这样就可以得到了
[解决办法]
----方法1:
select * from table as a where a.status = 0 and
not exists(select 1 from table where idnumber = a.idnumber and lasttime > a.lasttime)
----方法2:
select * from table as a where a.status = 0 and
a.lasttime = (select top 1 lasttime from table where status = 0 and idnumber = a.idnumber order by lasttime DESC)
----方法3:
select * from table as a where a.status = 0 and
a.lasttime = (select max(lasttime) from table where status = 0 and idnumber = a.idnumber group by idnumber)
[解决办法]
-- Create Table tblTest
CREATE TABLE tblTest(
idnumber CHAR(1),
status TINYINT,
lasttime SMALLDATETIME
)
GO
--Insert Test RecordSet
INSERT INTO tblTest SELECT 'a ', 0, '2007-08-10 ' UNION ALL
SELECT 'a ', 1, '2007-08-11 ' UNION ALL
SELECT 'a ', 0, '2007-08-12 ' UNION ALL
SELECT 'a ', 0, '2007-08-13 ' UNION ALL
SELECT 'b ', 1, '2007-08-11 ' UNION ALL
SELECT 'b ', 0, '2007-08-12 ' UNION ALL
SELECT 'b ', 1, '2007-08-13 '
GO
--Query The Record
SELECT idnumber, status, max(lasttime) AS lasttime
FROM tblTest
GROUP BY idnumber, status HAVING status = 0
--Query Result
idnumber status lasttime
a 0 2007-08-13
b 0 2007-08-12
(2 行受影响)
