Num Value Time
1 1.5 2009/05/01 10:02:21
1 3.5 2009/05/01 10:03:21
2 2.8 2009/05/01 10:04:21
2 1.5 2009/05/01 10:05:21
3 3 2009/05/01 10:06:21
3 2.1 2009/05/01 10:07:21
2 3.4 2009/05/02 10:02:21
2 3.5 2009/05/02 10:03:21
3 3.6 2009/05/02 10:04:21
3 2 2009/05/02 10:05:21
4 1.3 2009/05/02 10:06:21
4 1.9 2009/05/02 10:07:21
5 1.5 2009/05/03 10:02:21
5 1.3 2009/05/03 10:03:21
2 2.9 2009/05/03 10:04:21
2 2 2009/05/03 10:05:21
3 2.8 2009/05/03 10:06:21
4 2.9 2009/05/04 10:02:21
4 2.4 2009/05/04 10:03:21
1 2.3 2009/05/04 10:04:21
1 1.3 2009/05/04 10:05:21
2 3 2009/05/04 10:06:21
3 3.3 2009/05/05 10:02:21
3 3 2009/05/05 10:03:21
4 2.9 2009/05/05 10:04:21我想要的结果是1 3.5 2009/05/01 10:03:21
3 3 2009/05/01 10:06:21
2 3.4 2009/05/02 10:02:21
3 3.6 2009/05/02 10:04:21
2 3 2009/05/04 10:06:21
3 3.3 2009/05/05 10:02:211:按照Num和Time分组,以Time天为单位
2:Time asc
3:Value大于3的第一条(如果没有大于3的值该Num就不要了)
1 1.5 2009/05/01 10:02:21
1 3.5 2009/05/01 10:03:21
2 2.8 2009/05/01 10:04:21
2 1.5 2009/05/01 10:05:21
3 3 2009/05/01 10:06:21
3 2.1 2009/05/01 10:07:21
2 3.4 2009/05/02 10:02:21
2 3.5 2009/05/02 10:03:21
3 3.6 2009/05/02 10:04:21
3 2 2009/05/02 10:05:21
4 1.3 2009/05/02 10:06:21
4 1.9 2009/05/02 10:07:21
5 1.5 2009/05/03 10:02:21
5 1.3 2009/05/03 10:03:21
2 2.9 2009/05/03 10:04:21
2 2 2009/05/03 10:05:21
3 2.8 2009/05/03 10:06:21
4 2.9 2009/05/04 10:02:21
4 2.4 2009/05/04 10:03:21
1 2.3 2009/05/04 10:04:21
1 1.3 2009/05/04 10:05:21
2 3 2009/05/04 10:06:21
3 3.3 2009/05/05 10:02:21
3 3 2009/05/05 10:03:21
4 2.9 2009/05/05 10:04:21我想要的结果是1 3.5 2009/05/01 10:03:21
3 3 2009/05/01 10:06:21
2 3.4 2009/05/02 10:02:21
3 3.6 2009/05/02 10:04:21
2 3 2009/05/04 10:06:21
3 3.3 2009/05/05 10:02:211:按照Num和Time分组,以Time天为单位
2:Time asc
3:Value大于3的第一条(如果没有大于3的值该Num就不要了)
解决方案 »
- 根据 * 字符,拆分字符串
- 一个关于流程计算方法的问题,高手请进!
- 请达人帮忙指点一下订房系统的数据库设计问题~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
- 求一SQL语句
- 急 大侠们请指教 根据不同ID批量修改不相同数据
- SQL取出某列最大值的所有记录(行),有点疑问请教
- Sybase里如何实现左连接?
- 请问:在什么情况下会出现有"cannot create new connection in manual transaction"的错误!
- 请教:SQL SERVER 7。0安全问题。
- 在线求教一条SQL语句,跪求
- 附加数据库时的错误
- 怎么创建这个约束?要求班级的人数不得超过50人。
from tb t
where value>3
and not exists(select 1 from tb where value>3 and num=t.num and [time]<t.[time])
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Num] int,[Value] numeric(2,1),[Time] datetime)
insert [tb]
select 1,1.5,'2009/05/01 10:02:21' union all
select 1,3.5,'2009/05/01 10:03:21' union all
select 2,2.8,'2009/05/01 10:04:21' union all
select 2,1.5,'2009/05/01 10:05:21' union all
select 3,3,'2009/05/01 10:06:21' union all
select 3,2.1,'2009/05/01 10:07:21' union all
select 2,3.4,'2009/05/02 10:02:21' union all
select 2,3.5,'2009/05/02 10:03:21' union all
select 3,3.6,'2009/05/02 10:04:21' union all
select 3,2,'2009/05/02 10:05:21' union all
select 4,1.3,'2009/05/02 10:06:21' union all
select 4,1.9,'2009/05/02 10:07:21' union all
select 5,1.5,'2009/05/03 10:02:21' union all
select 5,1.3,'2009/05/03 10:03:21' union all
select 2,2.9,'2009/05/03 10:04:21' union all
select 2,2,'2009/05/03 10:05:21' union all
select 3,2.8,'2009/05/03 10:06:21' union all
select 4,2.9,'2009/05/04 10:02:21' union all
select 4,2.4,'2009/05/04 10:03:21' union all
select 1,2.3,'2009/05/04 10:04:21' union all
select 1,1.3,'2009/05/04 10:05:21' union all
select 2,3,'2009/05/04 10:06:21' union all
select 3,3.3,'2009/05/05 10:02:21' union all
select 3,3,'2009/05/05 10:03:21' union all
select 4,2.9,'2009/05/05 10:04:21'
---查询---
select *
from tb t
where value>=3
and not exists(select 1 from tb where value>=3 and num=t.num and datediff(dd,[time],t.[time])=0 and [time]<t.[time])---结果---
Num Value Time
----------- --------------------------------------- -----------------------
1 3.5 2009-05-01 10:03:21.000
3 3.0 2009-05-01 10:06:21.000
2 3.4 2009-05-02 10:02:21.000
3 3.6 2009-05-02 10:04:21.000
2 3.0 2009-05-04 10:06:21.000
3 3.3 2009-05-05 10:02:21.000(6 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-13 22:48:13
-- 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]([Num] int,[Value] numeric(2,1),[Time] datetime)
insert [tb]
select 1,1.5,'2009/05/01 10:02:21' union all
select 1,3.5,'2009/05/01 10:03:21' union all
select 2,2.8,'2009/05/01 10:04:21' union all
select 2,1.5,'2009/05/01 10:05:21' union all
select 3,3,'2009/05/01 10:06:21' union all
select 3,2.1,'2009/05/01 10:07:21' union all
select 2,3.4,'2009/05/02 10:02:21' union all
select 2,3.5,'2009/05/02 10:03:21' union all
select 3,3.6,'2009/05/02 10:04:21' union all
select 3,2,'2009/05/02 10:05:21' union all
select 4,1.3,'2009/05/02 10:06:21' union all
select 4,1.9,'2009/05/02 10:07:21' union all
select 5,1.5,'2009/05/03 10:02:21' union all
select 5,1.3,'2009/05/03 10:03:21' union all
select 2,2.9,'2009/05/03 10:04:21' union all
select 2,2,'2009/05/03 10:05:21' union all
select 3,2.8,'2009/05/03 10:06:21' union all
select 4,2.9,'2009/05/04 10:02:21' union all
select 4,2.4,'2009/05/04 10:03:21' union all
select 1,2.3,'2009/05/04 10:04:21' union all
select 1,1.3,'2009/05/04 10:05:21' union all
select 2,3,'2009/05/04 10:06:21' union all
select 3,3.3,'2009/05/05 10:02:21' union all
select 3,3,'2009/05/05 10:03:21' union all
select 4,2.9,'2009/05/05 10:04:21'
--------------开始查询--------------------------
select
*
from
tb t
where
[value]>=3
and
[time]=(select max(time) from tb where [value]>=3 and num=t.num and datediff(dd,[time],t.[time])=0 )
----------------结果----------------------------
/* Num Value Time
----------- --------------------------------------- -----------------------
1 3.5 2009-05-01 10:03:21.000
3 3.0 2009-05-01 10:06:21.000
2 3.5 2009-05-02 10:03:21.000
3 3.6 2009-05-02 10:04:21.000
2 3.0 2009-05-04 10:06:21.000
3 3.0 2009-05-05 10:03:21.000(6 行受影响)*/
如果是要查询1:按照Num和Time分组,以Time天为单位
2:Time asc
3:Value大于3的当天Time时间最晚的一条我想要的结果是
1 3.5 2009/05/01 10:03:21
3 2.1 2009/05/01 10:07:21
2 3.5 2009/05/02 10:03:21
3 2 2009/05/02 10:05:21
2 2.9 2009/05/03 10:04:21
2 2 2009/05/03 10:05:21
2 3 2009/05/04 10:06:21
3 3 2009/05/05 10:03:21解决立刻结贴!
2:Time asc
3:Value大于3的当天Time时间最晚的一条我想要的结果是1 3.5 2009/05/01 10:03:21
3 2.1 2009/05/01 10:07:21
2 3.5 2009/05/02 10:03:21
3 2 2009/05/02 10:05:21
2 3 2009/05/04 10:06:21
3 3 2009/05/05 10:03:21解决立刻结贴!
from tb t
where value>=3 and not exists(
select 1 from tb
where (value>=3 and num=t.num and datediff(dd,[time],t.[time])=0 and [time]>t.[time])
)Num Value Time
----------- ----- ------------------------------------------------------
1 3.5 2009-05-01 10:03:21.000
3 3.0 2009-05-01 10:06:21.000
2 3.5 2009-05-02 10:03:21.000
3 3.6 2009-05-02 10:04:21.000
2 3.0 2009-05-04 10:06:21.000
3 3.0 2009-05-05 10:03:21.000(所影响的行数为 6 行)
select *
from tb t
where value>=3
and not exists(select 1 from tb where value>=3 and num=t.num and datediff(dd,[time],t.[time])=0 and [time]>t.[time])---结果---
Num Value Time
----------- --------------------------------------- -----------------------
1 3.5 2009-05-01 10:03:21.000
3 3.0 2009-05-01 10:06:21.000
2 3.5 2009-05-02 10:03:21.000
3 3.6 2009-05-02 10:04:21.000
2 3.0 2009-05-04 10:06:21.000
3 3.0 2009-05-05 10:03:21.000(6 行受影响)
3 2.1 2009/05/01 10:07:21
2 3.5 2009/05/02 10:03:21
3 2 2009/05/02 10:05:21
2 3 2009/05/04 10:06:21
3 3 2009/05/05 10:03:21
-- Author :SQL77(只为思齐老)
-- Date :2010-03-13 22:48:51
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([Num] int,[Value] numeric(2,1),[Time] datetime)
insert #TB
select 1,1.5,'2009/05/01 10:02:21' union all
select 1,3.5,'2009/05/01 10:03:21' union all
select 2,2.8,'2009/05/01 10:04:21' union all
select 2,1.5,'2009/05/01 10:05:21' union all
select 3,3,'2009/05/01 10:06:21' union all
select 3,2.1,'2009/05/01 10:07:21' union all
select 2,3.4,'2009/05/02 10:02:21' union all
select 2,3.5,'2009/05/02 10:03:21' union all
select 3,3.6,'2009/05/02 10:04:21' union all
select 3,2,'2009/05/02 10:05:21' union all
select 4,1.3,'2009/05/02 10:06:21' union all
select 4,1.9,'2009/05/02 10:07:21' union all
select 5,1.5,'2009/05/03 10:02:21' union all
select 5,1.3,'2009/05/03 10:03:21' union all
select 2,2.9,'2009/05/03 10:04:21' union all
select 2,2,'2009/05/03 10:05:21' union all
select 3,2.8,'2009/05/03 10:06:21' union all
select 4,2.9,'2009/05/04 10:02:21' union all
select 4,2.4,'2009/05/04 10:03:21' union all
select 1,2.3,'2009/05/04 10:04:21' union all
select 1,1.3,'2009/05/04 10:05:21' union all
select 2,3,'2009/05/04 10:06:21' union all
select 3,3.3,'2009/05/05 10:02:21' union all
select 3,3,'2009/05/05 10:03:21' union all
select 4,2.9,'2009/05/05 10:04:21'
--------------开始查询--------------------------
--DROP TABLE #T
select idd=identity(int,1,1),* into #t from #tb WHERE Value>=3SELECT *,
(select count(1) from #t where IDD<=T.IDD AND NUM=T.NUM
AND CONVERT(VARCHAR(10),[Time],120)=CONVERT(VARCHAR(10),T.[Time],120)
AND IDD>(SELECT ISNULL(MAX(IDD),0) FROM #T WHERE IDD<T.IDD AND NUM!=T.NUM
AND CONVERT(VARCHAR(10),[Time],120)=CONVERT(VARCHAR(10),T.[Time],120)))CNT INTO #TT
FROM #T T
SELECT * FROM #TTSELECT * FROM #TT T WHERE
CNT=(SELECT MIN(CNT) FROM #TT WHERE CONVERT(VARCHAR(10),[Time],120)=CONVERT(VARCHAR(10),T.[Time],120))SELECT * FROM #TT T WHERE
CNT=(SELECT MAX(CNT) FROM #TT WHERE CONVERT(VARCHAR(10),[Time],120)=CONVERT(VARCHAR(10),T.[Time],120))
----------------结果----------------------------/*
idd Num Value Time CNT
----------- ----------- ----- ------------------------------------------------------ -----------
1 1 3.5 2009-05-01 10:03:21.000 1
2 3 3.0 2009-05-01 10:06:21.000 1
3 2 3.4 2009-05-02 10:02:21.000 1
4 2 3.5 2009-05-02 10:03:21.000 2
5 3 3.6 2009-05-02 10:04:21.000 1
6 2 3.0 2009-05-04 10:06:21.000 1
7 3 3.3 2009-05-05 10:02:21.000 1
8 3 3.0 2009-05-05 10:03:21.000 2(所影响的行数为 8 行)idd Num Value Time CNT
----------- ----------- ----- ------------------------------------------------------ -----------
1 1 3.5 2009-05-01 10:03:21.000 1
2 3 3.0 2009-05-01 10:06:21.000 1
3 2 3.4 2009-05-02 10:02:21.000 1
5 3 3.6 2009-05-02 10:04:21.000 1
6 2 3.0 2009-05-04 10:06:21.000 1
7 3 3.3 2009-05-05 10:02:21.000 1(所影响的行数为 6 行)idd Num Value Time CNT
----------- ----------- ----- ------------------------------------------------------ -----------
1 1 3.5 2009-05-01 10:03:21.000 1
2 3 3.0 2009-05-01 10:06:21.000 1
4 2 3.5 2009-05-02 10:03:21.000 2
6 2 3.0 2009-05-04 10:06:21.000 1
8 3 3.0 2009-05-05 10:03:21.000 2(所影响的行数为 5 行)*/
-- Author :SQL77(只为思齐老)
-- Date :2010-03-13 22:48:51
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([Num] int,[Value] numeric(2,1),[Time] datetime)
insert #TB
select 1,1.5,'2009/05/01 10:02:21' union all
select 1,3.5,'2009/05/01 10:03:21' union all
select 2,2.8,'2009/05/01 10:04:21' union all
select 2,1.5,'2009/05/01 10:05:21' union all
select 3,3,'2009/05/01 10:06:21' union all
select 3,2.1,'2009/05/01 10:07:21' union all
select 2,3.4,'2009/05/02 10:02:21' union all
select 2,3.5,'2009/05/02 10:03:21' union all
select 3,3.6,'2009/05/02 10:04:21' union all
select 3,2,'2009/05/02 10:05:21' union all
select 4,1.3,'2009/05/02 10:06:21' union all
select 4,1.9,'2009/05/02 10:07:21' union all
select 5,1.5,'2009/05/03 10:02:21' union all
select 5,1.3,'2009/05/03 10:03:21' union all
select 2,2.9,'2009/05/03 10:04:21' union all
select 2,2,'2009/05/03 10:05:21' union all
select 3,2.8,'2009/05/03 10:06:21' union all
select 4,2.9,'2009/05/04 10:02:21' union all
select 4,2.4,'2009/05/04 10:03:21' union all
select 1,2.3,'2009/05/04 10:04:21' union all
select 1,1.3,'2009/05/04 10:05:21' union all
select 2,3,'2009/05/04 10:06:21' union all
select 3,3.3,'2009/05/05 10:02:21' union all
select 3,3,'2009/05/05 10:03:21' union all
select 4,2.9,'2009/05/05 10:04:21'
--------------开始查询--------------------------
--DROP TABLE #T
select idd=identity(int,1,1),* into #t from #tb WHERE Value>=3SELECT *,
(select count(1) from #t where IDD<=T.IDD AND NUM=T.NUM
AND CONVERT(VARCHAR(10),[Time],120)=CONVERT(VARCHAR(10),T.[Time],120)
AND IDD>(SELECT ISNULL(MAX(IDD),0) FROM #T WHERE IDD<T.IDD AND NUM!=T.NUM
AND CONVERT(VARCHAR(10),[Time],120)=CONVERT(VARCHAR(10),T.[Time],120)))CNT INTO #TT
FROM #T T
SELECT * FROM #TTSELECT * FROM #TT T WHERE
CNT=(SELECT MIN(CNT) FROM #TT WHERE CONVERT(VARCHAR(10),[Time],120)=CONVERT(VARCHAR(10),T.[Time],120)AND NUM=T.NUM )SELECT * FROM #TT T WHERE
CNT=(SELECT MAX(CNT) FROM #TT WHERE CONVERT(VARCHAR(10),[Time],120)=CONVERT(VARCHAR(10),T.[Time],120) AND NUM=T.NUM)
----------------结果----------------------------/*
idd Num Value Time CNT
----------- ----------- ----- ------------------------------------------------------ -----------
1 1 3.5 2009-05-01 10:03:21.000 1
2 3 3.0 2009-05-01 10:06:21.000 1
3 2 3.4 2009-05-02 10:02:21.000 1
4 2 3.5 2009-05-02 10:03:21.000 2
5 3 3.6 2009-05-02 10:04:21.000 1
6 2 3.0 2009-05-04 10:06:21.000 1
7 3 3.3 2009-05-05 10:02:21.000 1
8 3 3.0 2009-05-05 10:03:21.000 2(所影响的行数为 8 行)idd Num Value Time CNT
----------- ----------- ----- ------------------------------------------------------ -----------
1 1 3.5 2009-05-01 10:03:21.000 1
2 3 3.0 2009-05-01 10:06:21.000 1
3 2 3.4 2009-05-02 10:02:21.000 1
5 3 3.6 2009-05-02 10:04:21.000 1
6 2 3.0 2009-05-04 10:06:21.000 1
7 3 3.3 2009-05-05 10:02:21.000 1(所影响的行数为 6 行)idd Num Value Time CNT
----------- ----------- ----- ------------------------------------------------------ -----------
1 1 3.5 2009-05-01 10:03:21.000 1
2 3 3.0 2009-05-01 10:06:21.000 1
4 2 3.5 2009-05-02 10:03:21.000 2
5 3 3.6 2009-05-02 10:04:21.000 1
6 2 3.0 2009-05-04 10:06:21.000 1
8 3 3.0 2009-05-05 10:03:21.000 2(所影响的行数为 6 行)*/
select 1,3.5,'2009/05/01 10:03:21'
这样个时间一样 哪个是最晚的啊
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Num] int,[Value] numeric(2,1),[Time] datetime)
insert [tb]
SELECT 1,1.5, '2009/05/01 10:02:21' UNION ALL
SELECT 1 ,3.5 ,'2009/05/01 10:03:21' UNION ALL
SELECT 2 ,2.8 ,'2009/05/01 10:04:21' UNION ALL
SELECT 2, 1.5, '2009/05/01 10:05:21' UNION ALL
SELECT 3, 3, '2009/05/01 10:06:21' UNION ALL
SELECT 3, 2.1 , '2009/05/01 10:07:21' UNION ALL
SELECT 2 ,3.4 , '2009/05/02 10:02:21' UNION ALL
SELECT 2 ,3.5 , '2009/05/02 10:03:21' UNION ALL
SELECT 3 , 3.6, '2009/05/02 10:04:21' UNION ALL
SELECT 3 ,2, '2009/05/02 10:05:21' UNION ALL
SELECT 4 ,1.3 ,'2009/05/02 10:06:21' UNION ALL
SELECT 4 ,1.9, '2009/05/02 10:07:21' UNION ALL
SELECT 5 ,1.5 , '2009/05/03 10:02:21' UNION ALL
SELECT 5 ,1.3 , '2009/05/03 10:03:21' UNION ALL
SELECT 2 ,2.9, '2009/05/03 10:04:21' UNION ALL
SELECT 2 ,2, '2009/05/03 10:05:21' UNION ALL
SELECT 3 ,2.8 ,'2009/05/03 10:06:21' UNION ALL
SELECT 4 ,2.9 ,'2009/05/04 10:02:21' UNION ALL
SELECT 4 ,2.4 ,'2009/05/04 10:03:21' UNION ALL
SELECT 1 ,2.3 ,'2009/05/04 10:04:21' UNION ALL
SELECT 1 ,1.3, '2009/05/04 10:05:21' UNION ALL
SELECT 2 ,3 ,'2009/05/04 10:06:21' UNION ALL
SELECT 3 ,3.3, '2009/05/05 10:02:21' UNION ALL
SELECT 3 ,3 ,'2009/05/05 10:03:21' UNION ALL
SELECT 4 ,2.9, '2009/05/05 10:04:21'--SELECT * FROM tb1)Query
SELECT *
FROM
(
SELECT num,value,Time,RANK = RANK()OVER(PARTITION BY num,CONVERT(varchar(12) , Time, 112 ) ORDER BY time )
FROM
(
SELECT * FROM tb
)B
WHERE value >= 3
)C
WHERE C.RANK = 12)Result
num value Time RANK
----------- --------------------------------------- ----------------------- --------------------
1 3.5 2009-05-01 10:03:21.000 1
2 3.4 2009-05-02 10:02:21.000 1
2 3.0 2009-05-04 10:06:21.000 1
3 3.0 2009-05-01 10:06:21.000 1
3 3.6 2009-05-02 10:04:21.000 1
3 3.3 2009-05-05 10:02:21.000 1(6 row(s) affected)
select 1,3.5,'2009/05/01 10:03:21'当然是下面时间大于上面啦,多了一分钟哦
select *
from tb t
where exists(select 1 from tb where value>=3 and num=t.num and datediff(dd,[time],t.[time])=0)
and not exists(select 1 from tb where num=t.num and datediff(dd,[time],t.[time])=0 and [time]>t.[time])---结果---
Num Value Time
----------- --------------------------------------- -----------------------
1 3.5 2009-05-01 10:03:21.000
3 2.1 2009-05-01 10:07:21.000
2 3.5 2009-05-02 10:03:21.000
3 2.0 2009-05-02 10:05:21.000
2 3.0 2009-05-04 10:06:21.000
3 3.0 2009-05-05 10:03:21.000(6 行受影响)
-- Author :fredrickhu(小F,向高手学习)
-- Date :2010-03-13 22:48:13
-- 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]([Num] int,[Value] numeric(2,1),[Time] datetime)
insert [tb]
select 1,1.5,'2009/05/01 10:02:21' union all
select 1,3.5,'2009/05/01 10:03:21' union all
select 2,2.8,'2009/05/01 10:04:21' union all
select 2,1.5,'2009/05/01 10:05:21' union all
select 3,3,'2009/05/01 10:06:21' union all
select 3,2.1,'2009/05/01 10:07:21' union all
select 2,3.4,'2009/05/02 10:02:21' union all
select 2,3.5,'2009/05/02 10:03:21' union all
select 3,3.6,'2009/05/02 10:04:21' union all
select 3,2,'2009/05/02 10:05:21' union all
select 4,1.3,'2009/05/02 10:06:21' union all
select 4,1.9,'2009/05/02 10:07:21' union all
select 5,1.5,'2009/05/03 10:02:21' union all
select 5,1.3,'2009/05/03 10:03:21' union all
select 2,2.9,'2009/05/03 10:04:21' union all
select 2,2,'2009/05/03 10:05:21' union all
select 3,2.8,'2009/05/03 10:06:21' union all
select 4,2.9,'2009/05/04 10:02:21' union all
select 4,2.4,'2009/05/04 10:03:21' union all
select 1,2.3,'2009/05/04 10:04:21' union all
select 1,1.3,'2009/05/04 10:05:21' union all
select 2,3,'2009/05/04 10:06:21' union all
select 3,3.3,'2009/05/05 10:02:21' union all
select 3,3,'2009/05/05 10:03:21' union all
select 4,2.9,'2009/05/05 10:04:21'
--------------开始查询--------------------------
select
num,[value],[time]
from
(select
num,[value],[time],id=rank()over(partition by num,convert(varchar(10),[time],112) order by [time])
from
tb
where
[value]>=3)tt
where
tt.id=1
order by 3
----------------结果----------------------------
/*num value time
----------- --------------------------------------- -----------------------
1 3.5 2009-05-01 10:03:21.000
3 3.0 2009-05-01 10:06:21.000
2 3.4 2009-05-02 10:02:21.000
3 3.6 2009-05-02 10:04:21.000
2 3.0 2009-05-04 10:06:21.000
3 3.3 2009-05-05 10:02:21.000(6 行受影响)
*/
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([Num] int,[Value] numeric(2,1),[Time] datetime)
insert [tb]
select 1,1.5,'2009/05/01 10:02:21' union all
select 1,3.5,'2009/05/01 10:03:21' union all
select 2,2.8,'2009/05/01 10:04:21' union all
select 2,1.5,'2009/05/01 10:05:21' union all
select 3,3,'2009/05/01 10:06:21' union all
select 3,2.1,'2009/05/01 10:07:21' union all
select 2,3.4,'2009/05/02 10:02:21' union all
select 2,3.5,'2009/05/02 10:03:21' union all
select 3,3.6,'2009/05/02 10:04:21' union all
select 3,2,'2009/05/02 10:05:21' union all
select 4,1.3,'2009/05/02 10:06:21' union all
select 4,1.9,'2009/05/02 10:07:21' union all
select 5,1.5,'2009/05/03 10:02:21' union all
select 5,1.3,'2009/05/03 10:03:21' union all
select 2,2.9,'2009/05/03 10:04:21' union all
select 2,2,'2009/05/03 10:05:21' union all
select 3,2.8,'2009/05/03 10:06:21' union all
select 4,2.9,'2009/05/04 10:02:21' union all
select 4,2.4,'2009/05/04 10:03:21' union all
select 1,2.3,'2009/05/04 10:04:21' union all
select 1,1.3,'2009/05/04 10:05:21' union all
select 2,3,'2009/05/04 10:06:21' union all
select 3,3.3,'2009/05/05 10:02:21' union all
select 3,3,'2009/05/05 10:03:21' union all
select 4,2.9,'2009/05/05 10:04:21'
select distinct t.* into #t
from tb t
join tb t1 on t.num=t1.num and datediff(dd,t.[time],t1.[time])=0
and t1.value>=3 and t.[time]>=t1.[time]
select *
from #t t where
not exists(select 1 from #t tb where num=t.num and datediff(dd,[time],t.[time])=0 and [time]>t.[time])
order by [Time]Num Value Time
----------- ----- ------------------------------------------------------
1 3.5 2009-05-01 10:03:21.000
3 2.1 2009-05-01 10:07:21.000
2 3.5 2009-05-02 10:03:21.000
3 2.0 2009-05-02 10:05:21.000
2 3.0 2009-05-04 10:06:21.000
3 3.0 2009-05-05 10:03:21.000(所影响的行数为 6 行)
---2005
SELECT [Num],[Value],[Time] FROM (
SELECT *,MIN(Time) OVER(PARTITION BY [Num],[Time]) AS MIN_TIME FROM [tb] WHERE Value>3
) A
WHERE MIN_TIME=[TIME]--2000
SELECT [Num],[Value],[Time] FROM (
SELECT *,(SELECT MIN(TIME) FROM [tb] WHERE A.NUM=NUM AND A.TIME=TIME AND Value>3) AS MIN_TIME FROM [tb] A WHERE Value>3
) A
WHERE MIN_TIME=[TIME]Num Value Time
----------- --------------------------------------- -----------------------
1 3.5 2009-05-01 10:03:21.000
2 3.4 2009-05-02 10:02:21.000
2 3.5 2009-05-02 10:03:21.000
3 3.6 2009-05-02 10:04:21.000
3 3.3 2009-05-05 10:02:21.000(5 行受影响)这样对吗