表table:
id name valid(BIT) date
1 张三 1 2010-07-01
2 李四 1 2010-07-01
3 王五 1 2010-07-01
4 张三 0 2010-08-01
5 李四 0 2010-09-01
6 张三 1 2010-09-01
问1:如何查最新日期且valid = 1的结果集
id name valid(BIT) date
3 王五 1 2010-07-01
6 张三 1 2010-09-01问2:如何查出日期为2010-08-01前且valid = 1的结果集
id name valid(BIT) date
2 李四 1 2010-07-01
3 王五 1 2010-07-01
id name valid(BIT) date
1 张三 1 2010-07-01
2 李四 1 2010-07-01
3 王五 1 2010-07-01
4 张三 0 2010-08-01
5 李四 0 2010-09-01
6 张三 1 2010-09-01
问1:如何查最新日期且valid = 1的结果集
id name valid(BIT) date
3 王五 1 2010-07-01
6 张三 1 2010-09-01问2:如何查出日期为2010-08-01前且valid = 1的结果集
id name valid(BIT) date
2 李四 1 2010-07-01
3 王五 1 2010-07-01
where not exists (select * from table where name=t.name and valid=1 and date>t.date)
and valid=1select * from table t
where not exists (select * from table where name=t.name and valid=1 and date<'2010-08-01' and date>t.date)
and valid=1 and date<'2010-08-01'
where not exists (select * from table where name=t.name and valid=1 and date>t.date)
and valid=1select * from table t
where not exists (select * from table where name=t.name and valid=1 and date<'2010-08-01' and date>t.date)
and valid=1 and date<'2010-08-01'
where valid=1
group by Name
GO
CREATE TABLE TB
(
ID INT IDENTITY,
NAME NVARCHAR(25),
VALID INT,
DATE DATETIME
)
INSERT INTO TB
SELECT '张三',1,'2010-07-01' UNION ALL
SELECT '李四',1,'2010-07-01' UNION ALL
SELECT '王五',1,'2010-07-01' UNION ALL
SELECT '张三',0,'2010-08-01' UNION ALL
SELECT '李四',0,'2010-09-01' UNION ALL
SELECT '张三',1,'2010-09-01'
--1.
SELECT T1.* FROM TB T1
WHERE NOT EXISTS(SELECT 1 FROM TB T2 WHERE T1.DATE<T2.DATE AND T1.NAME=T2.NAME) AND T1.VALID=1
--------------------
3 王五 1 2010-07-01 00:00:00.000
6 张三 1 2010-09-01 00:00:00.000
--2.
SELECT T1.* FROM TB T1
WHERE NOT EXISTS(SELECT 1 FROM TB T2 WHERE T1.DATE<T2.DATE AND T1.NAME=T2.NAME AND T2.VALID=1) AND T1.DATE<'2010-08-01'
-----------------------
2 李四 1 2010-07-01 00:00:00.000
3 王五 1 2010-07-01 00:00:00.000