idnumber status lasttime
a 0 2007-08-10
a 1 2007-08-11
a 0 2007-08-12
a 0 2007-08-13b 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这个该怎么写,先谢谢大家!
a 0 2007-08-10
a 1 2007-08-11
a 0 2007-08-12
a 0 2007-08-13b 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这个该怎么写,先谢谢大家!
解决方案 »
- 求个从月报出季报的SQL
- 如此存贮过程也死锁?
- 触发器语句的写法
- 相同内容的储存过程需要调用不同数据库中的表放在Master表中怎么让他选择正确的数据库?
- 急求一函数,查找自动编号列中的缺少的编号?
- 在windows 2003群集下如何安装sql Server 2000
- 能给我讲解一下下面的代码吗?谢谢了
- 我在SQLSERVER2000里做查询,从一张表里做select语句,请问如何得到这个中间查询记录集的表名???
- 两张字段相同的表,怎么合并.看看,100等你拿!
- 连接远程SQL SERVER Server=xxx.xxx.xxx.xxx;Address=xxx.xxx.xxx.xxx 是什么意思
- sql2005..SSIS生成excel问题,急。。。
- 触发器问题
from tablename
where status = '0'
group by idnumber,status
Select * From TableName A Where Status='0' And Not Exists
(Select 1 From TableName Where idnumber=A.idnumber And lasttime>A.lasttime)
(Select 1 From TableName Where idnumber=A.idnumber And lasttime>A.lasttime and A.Status== Status )
这样就可以得到了
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)
----方法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)
现在条件变一下,又该怎么写呢,有劳各位了!idnumber status lasttime
a 0 2007-08-10
a 1 2007-08-11
a 0 2007-08-12
a 0 2007-08-13b 1 2007-08-11
b 0 2007-08-12
b 1 2007-08-13c 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
idnumber CHAR(1),
status TINYINT,
lasttime SMALLDATETIME
)GO--Insert Test RecordSetINSERT 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 RecordSELECT 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 Resultidnumber status timea 0 2007-08-13
b 0 2007-08-12
c 1 2007-08-11
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
*/
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