请教这个查询语句怎么写?

[pingfan520]

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

这个该怎么写,先谢谢大家!

[mengmou]

select idnumber,status,max(lasttime) as lasttime
from tablename
where status = '0'
group by idnumber,status

[SoftwKLC]

---也可以试试这种,不过得不到你b那条记录
Select * From TableName A Where Status='0' And Not Exists
(Select 1 From TableName Where idnumber=A.idnumber And lasttime>A.lasttime)

[mugua604]

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 )
这样就可以得到了

[hellowork]

----方法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)

[hellowork]

抱歉,更正一下,方法1的子查询中忘了status = 0这个条件:
----方法1:
select * from table as a where a.status = 0 and
not exists(select 1 from table where status = 0 and 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)

[JavCof]

-- 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 行受影响)

[pingfan520]

先谢谢大家,
现在条件变一下,又该怎么写呢,有劳各位了!

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

c 1 2007-08-11
c 1 2007-08-12
c 1 2007-08-13

现在我要查询出如果有status为'0'得,则查出最后一条记录,如果status全部为1,则查询出最前一条记录
idnumber status lasttime
a 0 2007-08-13
b 0 2007-08-12
c 1 2007-08-11

[JavCof]

-- 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' UNION ALL
SELECT 'c', 1, '2007-08-11' UNION ALL
SELECT 'c', 1, '2007-08-12' UNION ALL
SELECT 'c', 1, '2007-08-13'

GO

--Query The Record

SELECT idnumber, status, MAX(lasttime) time
FROM tblTest
GROUP BY idnumber, status HAVING status = 0
UNION ALL SELECT idnumber, status, MIN(lasttime)
FROM tblTest
GROUP BY idnumber, status HAVING status = 1 AND idnumber = 'c'

GO

--Query Result

idnumber status time

a 0 2007-08-13
b 0 2007-08-12
c 1 2007-08-11

[hellowork]

----创建测试数据
declare @t table(idnumber varchar(10),status bit,lasttime varchar(10))
insert @t
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' union all
select 'c', 1, '2007-08-11' union all
select 'c', 1, '2007-08-12' union all
select 'c', 1, '2007-08-13'

----方法1:
select * from @t as a where
(a.status = 0 and exists(select 1 from @t where idnumber = a.idnumber and status = 1) and
not exists(select 1 from @t where status = 0 and idnumber = a.idnumber and lasttime > a.lasttime))
or
(a.status = 1 and not exists(select 1 from @t where idnumber = a.idnumber and status = 0) and
not exists(select 1 from @t where status = 1 and idnumber = a.idnumber and lasttime < a.lasttime))

----方法2:
select * from @t as a where
(a.status = 0 and exists(select 1 from @t where idnumber = a.idnumber and status = 1) and
a.lasttime = (select top 1 lasttime from @t where status = 0 and idnumber = a.idnumber order by lasttime DESC))
or
(a.status = 1 and not exists(select 1 from @t where idnumber = a.idnumber and status = 0) and
a.lasttime = (select top 1 lasttime from @t where status = 1 and idnumber = a.idnumber order by lasttime))

----方法3:
select * from @t as a where
(a.status = 0 and exists(select 1 from @t where idnumber = a.idnumber and status = 1) and
a.lasttime = (select max(lasttime) from @t where status = 0 and idnumber = a.idnumber group by idnumber))
or
(a.status = 1 and not exists(select 1 from @t where idnumber = a.idnumber and status = 0) and
a.lasttime = (select MIN(lasttime) from @t where status = 1 and idnumber = a.idnumber group by idnumber))


/*结果
idnumber status lasttime
---------- ------ ----------
a 0 2007-08-13
b 0 2007-08-12
c 1 2007-08-11
*/

[pingfan520]

谢谢大家了,谢谢hellowork(一两清风),佩服,佩服

[rockywu]

Select * from TB
Select idnumber,status,lasttime=max(lasttime) From TB
Where status='0' Group by idnumber,status
Union All
Select idnumber,status,lasttime=min(lasttime)
From TB Where Status=1 And idnumber Not In (Select idnumber From TB
Where status='0' Group by idnumber
)
Group by idnumber,status