27,580
社区成员
发帖
与我相关
我的任务
分享
---测试数据---
if object_id('[A]') is not null drop table [A]
go
create table [A]([NO] varchar(2),[STATE] varchar(6),[CTIME] datetime)
insert [A]
select 'A1','A','2008-12-1' union all
select 'A1','指定值','2008-12-15' union all
select 'A1','C','2008-12-18' union all
select 'A2','B','2008-12-14' union all
select 'A2','A','2008-12-18' union all
select 'A2','指定值','2008-12-31' union all
select 'A3','指定值','2008-12-15' union all
select 'A4','C','2008-12-16' union all
select 'A4','D','2008-12-17' union all
select 'A5','A','2008-12-15' union all
select 'A5','指定值','2008-12-19'
---查询---
select *
from A t
where not exists(select * from A where [NO]=t.[NO] and CTIME>t.CTIME)
and [STATE]='指定值'
---结果---
NO STATE CTIME
---- ------ -----------------------
A2 指定值 2008-12-31 00:00:00.000
A3 指定值 2008-12-15 00:00:00.000
A5 指定值 2008-12-19 00:00:00.000
(3 行受影响)
--TRY:
DECLARE @p table(id varchar(50))
insert into @p
select 'P1' UNION ALL
SELECT 'P2' UNION ALL
SELECT 'P3'
DECLARE @OBJOF TABLE(ITEMID1 VARCHAR(10),ITEMID2 VARCHAR(10))
INSERT INTO @OBJOF
SELECT 'P1','PD1' UNION ALL
SELECT 'P1','PD2' UNION ALL
SELECT 'P2','PD3' UNION ALL
SELECT 'P2','PD4' UNION ALL
SELECT 'P3','PD5'
DECLARE @P_DT TABLE (ID VARCHAR(40),STATE VARCHAR(30),CTIME DATETIME)
INSERT INTO @P_DT
SELECT 'PD1','A','2008-12-11' UNION ALL
SELECT 'PD2','给定值','2008-12-23' UNION ALL
SELECT 'PD3','B','2008-12-15' UNION ALL
SELECT 'PD4','C','2008-12-16' UNION ALL
SELECT 'PD5','给定值','2008-12-18'
SELECT A.ID,MAX(C.CTIME)CTIME FROM
@P A INNER JOIN @OBJOF B ON A.ID=B.ITEMID1 INNER JOIN @P_DT C ON B.ITEMID2=C.ID WHERE C.STATE='给定值'
GROUP BY A.ID
select * from p inner join objof on p.id=objof.id where objof.id2=(select id from (select
id,max(ctime)ctime from p_dt where state='给定值' group by id)b)