有表如下,要从中把开始和结束日期在2007-01-26到2007-02-25之间同时开始日期最大的几条数据取出来。开始日期 结束日期 A B C
2006-12-26 2007-01-25 A A 1000
2006-12-26 2007-01-25 A B 1000
2007-01-12 2007-01-25 B D 1000
2007-01-12 2007-01-25 C W 1000
2007-01-26 2007-02-25 D F 1000
2007-01-26 2007-02-25 A W 1000
2007-02-12 2007-02-25 A B 1000
2007-02-12 2007-02-25 B L 1000
2007-02-26 2007-03-25 E T 1000
2007-02-26 2007-03-25 F N 1000
2006-12-26 2007-01-25 A A 1000
2006-12-26 2007-01-25 A B 1000
2007-01-12 2007-01-25 B D 1000
2007-01-12 2007-01-25 C W 1000
2007-01-26 2007-02-25 D F 1000
2007-01-26 2007-02-25 A W 1000
2007-02-12 2007-02-25 A B 1000
2007-02-12 2007-02-25 B L 1000
2007-02-26 2007-03-25 E T 1000
2007-02-26 2007-03-25 F N 1000
解决方案 »
- 通过C#登录SQL数据库,用sp_password 'oldp','newp','username'修改登录名密码提示无权限是怎么回事??
- 如何设置数据表中实数的类型。是float还是numeric...?如果是float类型怎么设置小数点是2位?
- sql2008数据库同步并发的问题
- Sql 查询问题求助
- 怎样才算一个良好的数据库 期待偶像们的帮忙
- SQL Server的线程与纤程
- 急急急,求大侠救命
- 请教SQL Server 7.0数据库设计的基本问题!
- 从sql server里取字段类型为text的数据,该用resultset的哪个方法?
- 这句sql该怎么写?
- 求一SQL语句
- XML的概念问题
--得到A最大的
SELECT *
FROM TABLENAME T
WHERE EXISTS(SELECT 1 FROM TABLENAME WHERE T.开始日期=开始日期 AND T.结束日期=结束日期 AND T.A>A)
--得到B最大的
SELECT *
FROM TABLENAME T
WHERE EXISTS(SELECT 1 FROM TABLENAME WHERE T.开始日期=开始日期 AND T.结束日期=结束日期 AND T.B>B)
开始日期<'20070225'+1 and 结束日期>='20070126'
2007-02-12 2007-02-25 A B 1000
2007-02-12 2007-02-25 B L 1000
(
开始日期 datetime ,
结束日期 datetime,
A varchar(10),
B varchar(10),
C int
)
delete dtimeinsert into dtime
select '2006-12-26' , '2007-01-25' , 'A' , 'A' , 1000
union all
select '2006-12-26' , '2007-01-25' , 'A' , 'B' , 1000
union all
select '2007-01-12' , '2007-01-25' , 'B' , 'D' , 1000
union all
select '2007-01-12 ' , '2007-01-25' , 'C' , 'W' , 1000
union all
select '2007-01-26' , '2007-02-25 ' , 'D' , 'F' , 1000
union all
select '2007-01-26' , '2007-02-25 ' , 'A' , 'W' , 1000
union all
select '2007-02-12' , '2007-02-25 ' , 'A' , 'B' , 1000
union all
select '2007-02-12 ' , '2007-02-25 ' , 'B' , 'L' , 1000
union all
select '2007-02-26' , '2007-03-25 ' , ' E' , 'T', 1000
union all
select '2007-02-26' , '2007-03-25' , 'F' , 'N' , 1000
select max(开始日期),结束日期, A,B,C from dtime
where 开始日期 >= '2007-01-26' and 开始日期 < '2007-02-25 '
and
结束日期 > '2007-01-26' and 结束日期 <= '2007-02-25'
group by 结束日期, A,B,C
where
开始日期>='2007-01-26'
and
结束日期<='2007-02-25'
order by 开始日期 desc
WHERE 开始日期 IN
(
SELECT MAX(开始日期) FROM Table
WHERE 开始日期 BETWEEN '2007-01-26' AND '2007-02-25'
)
AND 开始日期 BETWEEN '2007-01-26' AND '2007-02-25'
AND 结束日期 BETWEEN '2007-01-26' AND '2007-02-25'