表里的数据
name date
a 2009-09-08 00:00:00.000
a 2008-04-25 08:09:00.000
b 2008-11-12 00:00:00.000
b 2009-11-17 23:00:00.000
a 2009-05-18 00:00:00.000
c 2008-05-17 22:10:11.000
b 2008-05-17 22:10:11.000
c 2008-05-17 22:10:11.000
c 2008-08-17 22:10:11.000
分组查询出最大的时间
小于‘2009-05-10'的数据
name date
a 2009-09-08 00:00:00.000
a 2008-04-25 08:09:00.000
b 2008-11-12 00:00:00.000
b 2009-11-17 23:00:00.000
a 2009-05-18 00:00:00.000
c 2008-05-17 22:10:11.000
b 2008-05-17 22:10:11.000
c 2008-05-17 22:10:11.000
c 2008-08-17 22:10:11.000
分组查询出最大的时间
小于‘2009-05-10'的数据
解决方案 »
- 新手求助~~~新手求助~~~SQL 简繁转换~~~
- SQL语句性能方面的问题
- 一个很难的问题,望各位高手指教
- 求教高手如何在xp下安装sql 2000,不是客户端,想装服务器,我在xp下,好象装不了sql2000服务器
- Server2003系统里建立DSN连接sqlserver2000时,总是弹出sa用户登录及让输入密码的窗体??急急。。
- 急急!!!!!!! 求职员序号为10的所有下级职员
- 请问哪位知道只有一个数据文件时的恢复方法?我看了一些文章,都不成功!
- 请问有没有办法把这样的两台sql server 连结起来?
- 求救!!!!!!!!!!!
- 高分求助-160/168数据库设计问题
- SQL Server 无法建立维护计划
- 请教一个SQL语句!
declare @tb table (name varchar(1),date datetime)
insert into @tb
select 'a','2009-09-08 00:00:00.000' union all
select 'a','2008-04-25 08:09:00.000' union all
select 'b','2008-11-12 00:00:00.000' union all
select 'b','2009-11-17 23:00:00.000' union all
select 'a','2009-05-18 00:00:00.000' union all
select 'c','2008-05-17 22:10:11.000' union all
select 'b','2008-05-17 22:10:11.000' union all
select 'c','2008-05-17 22:10:11.000' union all
select 'c','2008-08-17 22:10:11.000'select * from @tb t
where not exists(select * from @tb where name=t.name and date>t.date and date<'2009-05-10')
and date<'2009-05-10'name date
---- -----------------------
a 2008-04-25 08:09:00.000
b 2008-11-12 00:00:00.000
c 2008-08-17 22:10:11.000(3 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2009-11-17 22:39:16
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([name] varchar(1),[date] datetime)
insert [tb]
select 'a','2009-09-08 00:00:00.000' union all
select 'a','2008-04-25 08:09:00.000' union all
select 'b','2008-11-12 00:00:00.000' union all
select 'b','2009-11-17 23:00:00.000' union all
select 'a','2009-05-18 00:00:00.000' union all
select 'c','2008-05-17 22:10:11.000' union all
select 'b','2008-05-17 22:10:11.000' union all
select 'c','2008-05-17 22:10:11.000' union all
select 'c','2008-08-17 22:10:11.000'
--------------开始查询--------------------------
select
*
from
[tb] t
where
[date]=(select max([date]) from tb where [name]=t.[name] and
datediff(dd,convert(varchar(120),[date],120),'2009-05-10')>0)----------------结果----------------------------
/* name date
---- -----------------------
c 2008-08-17 22:10:11.000
b 2008-11-12 00:00:00.000
a 2008-04-25 08:09:00.000(3 行受影响)*/
declare @tb table (name varchar(1),date datetime)
insert into @tb
select 'a','2009-09-08 00:00:00.000' union all
select 'a','2008-04-25 08:09:00.000' union all
select 'b','2008-11-12 00:00:00.000' union all
select 'b','2009-11-17 23:00:00.000' union all
select 'a','2009-05-18 00:00:00.000' union all
select 'c','2008-05-17 22:10:11.000' union all
select 'b','2008-05-17 22:10:11.000' union all
select 'c','2008-05-17 22:10:11.000' union all
select 'c','2008-08-17 22:10:11.000'select t1.* from
@tb t1
inner join (
select name
from @tb
group by name
having max(date)<'2009-05-10'
) t2 on t1.name=t2.name
/*(9 行受影响)
name date
---- -----------------------
c 2008-05-17 22:10:11.000
c 2008-08-17 22:10:11.000
c 2008-05-17 22:10:11.000(3 行受影响)*/
select name max(date) date
from 表
where date>'2009-5-10'
group by name