select *
from tablename a
where not exists(select 1 from tablename where 编号=a.编号 and 时间>a.时间)
from tablename a
where not exists(select 1 from tablename where 编号=a.编号 and 时间>a.时间)
解决方案 »
- 字段与参数的简单问题
- 小弟有个分页存储过程,其中对sp_executesql使用不明白,请帮忙讲解下。
- 远程调试存过出问题,请问如何解决?
- 今天面试口口声声说自己懂SQL,结果考官随便问个问题我就挂了,20分买答案
- SQLSERVER2000存储过程事务问题
- 调用.sql文件有没有比较好的参数传递方法?
- 怎样搜索sql server实例中所有库名如 master,tempdb、mrpdb...
- 一个有难度得查询
- 大虾,救命啊!
- 关于sql server2000 IIS VDM的安全问题。
- 问你一个比较郁闷的更新问题啊,sql语句的,在线急等!!!!!!!!!!!!!!!!
- 请问如何用存储过程返回一张表???已知传入两个参数,如何用存储过程返回一张查询表?(sql server2000)
from tablename
where 编号='11'
union all
select *
from tablename a
where not exists(select 1 from tablename where 编号=a.编号 and 类别='11')
and not exists(select 1 from tablename where 编号=a.编号 and 时间>a.时间)
--生成测试环境
create table t1 (编号 varchar(3), 类别 varchar(2),时间 datetime,primary key(编号, 类别))
insert into t1
select '001','01','2005-8-1 08:20:01' union all
select '001','02','2005-8-1 08:21:01' union all
select '001','11','2005-8-2 08:23:01' union all
select '002','01','2005-8-1 09:22:01' union all
select '002','02','2005-8-2 09:23:01' union all
select '002','03','2005-8-2 09:24:04' union all
select '003','01','2005-8-1 10:20:01' union all
select '003','11','2005-8-2 10:23:01' union all
select '004','01','2005-8-1 10:25:01' union all
select '005','01','2005-8-1 10:25:01'
go
--测试
select 编号 ,类别,时间
from t1 a where not exists
( select 1 from t1 where a.编号=编号 and
case 类别 when '11' then '9' else cast(时间 as varchar(25)) end >
case a.类别 when '11' then '9' else cast(a.时间 as varchar(25)) end )
--结果
/*
编号 类别 时间
---- ---- ------------------------------------------------------
001 11 2005-08-02 08:23:01.000
002 03 2005-08-02 09:24:04.000
003 11 2005-08-02 10:23:01.000
004 01 2005-08-01 10:25:01.000
005 01 2005-08-01 10:25:01.000(所影响的行数为 5 行)*/
--删除测试数据
drop table t1
where not exists(select 1 from 表 where [编号]=t.[编号] and [时间]>t.[时间])
or
[类别]=11
(
[编号] varchar(10),
[类别] varchar(10),
[时间] datetime
)
insert @tb
select '001','01','2005-8-1 08:20:01' union
select '001','02','2005-8-1 08:21:01' union
select '001','11','2005-8-2 08:23:01' union
select '002','01','2005-8-1 09:22:01' union
select '002','02','2005-8-2 09:23:01' union
select '002','03','2005-8-2 09:24:04' union
select '003','01','2005-8-1 10:20:01' union
select '003','11','2005-8-2 10:23:01' union
select '004','01','2005-8-1 10:25:01' union
select '005','01','2005-8-1 10:25:01' --结果
select * from @tb t
where not exists(select 1 from @tb where [编号]=t.[编号] and [时间]>t.[时间])
or
[类别]=11--结果
/*编号 类别 时间
---------- ---------- --------------------------
001 11 2005-08-02 08:23:01.000
002 03 2005-08-02 09:24:04.000
003 11 2005-08-02 10:23:01.000
004 01 2005-08-01 10:25:01.000
005 01 2005-08-01 10:25:01.000(所影响的行数为 5 行)
*/
declare @tb table
(
[编号] varchar(10),
[类别] varchar(10),
[时间] datetime
)
insert @tb
select '001','01','2005-8-1 08:20:01' union
select '001','02','2005-8-1 08:21:01' union
select '001','11','2005-8-2 08:23:01' union
select '002','01','2005-8-1 09:22:01' union
select '002','02','2005-8-2 09:23:01' union
select '002','03','2005-8-2 09:24:04' union
select '003','01','2005-8-1 10:20:01' union
select '003','11','2005-8-2 10:23:01' union
select '004','01','2005-8-1 10:25:01' union
select '005','01','2005-8-1 10:25:01' --结果
select * from @tb t
where [类别]=11
or
( not exists(select 1 from @tb where [编号]=t.[编号] and [时间]>t.[时间])
and
not exists(select 1 from @tb where [编号]=t.[编号] and [类别]=11)
)
--结果
/*
编号 类别 时间
---------- ---------- ------------------
001 11 2005-08-02 08:23:01.000
002 03 2005-08-02 09:24:04.000
003 11 2005-08-02 10:23:01.000
004 01 2005-08-01 10:25:01.000
005 01 2005-08-01 10:25:01.000(所影响的行数为 5 行)
*/
where [类别]=11
or
( not exists(select 1 from @tb where [编号]=t.[编号] and [时间]>t.[时间])
and
not exists(select 1 from @tb where [编号]=t.[编号] and [类别]=11)
)