时间查询

Question

表a    b        c     d
20081201   092152  15
20081201   092352  16
20081201   092632  11
20081201   096158  18
20081201   102130  15
20081202   092152  12
20081202   092342  13
20081202   093522  20
20081202   103652  15
20081204   092232  17
20081204   092552  19
20081204   095552  15
...
...
...
...
b为8位日期整数（排除星期六和星期天）c为6位时间整数（按每一天升序排列）
如何查询出d的值不在（上一天最大时间的d值的90%到110%之间）每月第一天的所有数据不查询
从第二天开始查询

Answer 1

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-06 21:47:11
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
DROP TABLE [tb]
GO
CREATE TABLE [tb]([b] NVARCHAR(10),[c] NVARCHAR(10),[d] INT)
INSERT [tb]
SELECT '20081201','092152',15 UNION ALL
SELECT '20081201','092352',16 UNION ALL
SELECT '20081201','092632',11 UNION ALL
SELECT '20081201','096158',18 UNION ALL
SELECT '20081201','102130',15 UNION ALL
SELECT '20081202','092152',12 UNION ALL
SELECT '20081202','092342',13 UNION ALL
SELECT '20081202','093522',20 UNION ALL
SELECT '20081202','103652',15 UNION ALL
SELECT '20081204','092232',17 UNION ALL
SELECT '20081204','092552',19 UNION ALL
SELECT '20081204','095552',15
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * 
from tb t 
where not exists(
select 1 from tb a 
where b=t.b and c=t.c and d between 
(select top 1 d from tb where b=a.b order by b,c desc)*0.9 
and (select top 1 d from tb where b=a.b order by b,c desc)*1.1)
/*
b          c          d
---------- ---------- -----------
20081201   092632     11
20081201   096158     18
20081202   092152     12
20081202   092342     13
20081202   093522     20
20081204   092232     17
20081204   092552     19(7 行受影响)
*/

Answer 2

----------------------------------------------------------------
-- Author  :SQL77(只为思齐老)
-- Date    :2010-04-06 21:51:32
-- Version:
--      Microsoft SQL Server  2000 - 8.00.2039 (Intel X86) 
-- May  3 2005 23:18:38 
-- Copyright (c) 1988-2003 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([b] datetime,[c] varchar(6),[d] int)
insert #tb
select '20081201','092152',15 union all
select '20081201','092352',16 union all
select '20081201','092632',11 union all
select '20081201','096158',18 union all
select '20081201','102130',15 union all
select '20081202','092152',12 union all
select '20081202','092342',13 union all
select '20081202','093522',20 union all
select '20081202','103652',15 union all
select '20081204','092232',17 union all
select '20081204','092552',19 union all
select '20081204','095552',15
--------------开始查询--------------------------select * from #tb t where not  exists(select 1 from #tb tt where b<t.b 
and  
 t.d >=(select top 1 d from #tb where b<=tt.b order by b desc,c desc)*0.9 
and 
t.d <=(select top 1 d from #tb where b<=tt.b order by b desc,c desc)*1.1) 
and day(b)<>1
----------------结果----------------------------
/* （所影响的行数为 12 行）b                                                      c      d           
------------------------------------------------------ ------ ----------- 
2008-12-02 00:00:00.000                                092152 12
2008-12-02 00:00:00.000                                092342 13
2008-12-02 00:00:00.000                                093522 20
2008-12-04 00:00:00.000                                092232 17
2008-12-04 00:00:00.000                                092552 19（所影响的行数为 5 行）
*/

Answer 3

use tempdb
if object_id('tb') is not null drop table tb
go
create table tb([b] varchar(50),[c] varchar(50),[d] INT)
insert into tb
select '20081201','092152',15 union all
select '20081201','092352',16 union all
select '20081201','092632',11 union all
select '20081201','096158',18 union all
select '20081201','102130',15 union all
select '20081202','092152',12 union all
select '20081202','092342',13 union all
select '20081202','093522',20 union all
select '20081202','103652',15 union all
select '20081204','092232',17 union all
select '20081204','092552',19 union all
select '20081204','095552',15 
go
--select * from tb
;with cte
as
(
select b,c,d,(select max(d) from tb t where b < tb.b) e from tb
)
select b,c,d from cte 
where e is not null and d not between 0.9*e and 1.1*e/*
b              c           d
-------------- ----------- -----------
20081202       092152      12
20081202       092342      13
20081202       093522      20
20081202       103652      15
20081204       092232      17
20081204       095552      15
*/

Answer 4

看了77的结果，似乎读懂了：
--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-06 21:47:11
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([b] NVARCHAR(10),[c] NVARCHAR(10),[d] INT)
INSERT [tb]
SELECT '20081201','092152',15 UNION ALL
SELECT '20081201','092352',16 UNION ALL
SELECT '20081201','092632',11 UNION ALL
SELECT '20081201','096158',18 UNION ALL
SELECT '20081201','102130',15 UNION ALL
SELECT '20081202','092152',12 UNION ALL
SELECT '20081202','092342',13 UNION ALL
SELECT '20081202','093522',20 UNION ALL
SELECT '20081202','103652',15 UNION ALL
SELECT '20081204','092232',17 UNION ALL
SELECT '20081204','092552',19 UNION ALL
SELECT '20081204','095552',15
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * 
from tb t 
where not exists(
    select 1 from tb a 
    where b=t.b and c=t.c and d between 
        (select top 1 d from tb where b=a.b order by b,c desc)*0.9 
        and (select top 1 d from tb where b=a.b order by b,c desc)*1.1)
and RIGHT(b,2)<>'01'
/*
b          c          d
---------- ---------- -----------
20081202   092152     12
20081202   092342     13
20081202   093522     20
20081204   092232     17
20081204   092552     19(5 行受影响)
*/

Answer 5

use tempdb
if object_id('tb') is not null drop table tb
go
create table tb([b] varchar(50),[c] varchar(50),[d] INT)
insert into tb
select '20081201','092152',15 union all
select '20081201','092352',16 union all
select '20081201','092632',11 union all
select '20081201','096158',18 union all
select '20081201','102130',15 union all
select '20081202','092152',12 union all
select '20081202','092342',13 union all
select '20081202','093522',20 union all
select '20081202','103652',15 union all
select '20081204','092232',17 union all
select '20081204','092552',19 union all
select '20081204','095552',15 
go
--select * from tb
;with cte
as
(
    select b,c,d,(select top 1 d from tb t where b = tb.b order by b,c desc) e 
    from tb where RIGHT(b,2)<>'01'
)
select b,c,d,e from cte 
where d not between 0.9*e and 1.1*e
/*
b                                                  c                                                  d           e
-------------------------------------------------- -------------------------------------------------- ----------- -----------
20081202                                           092152                                             12          15
20081202                                           092342                                             13          15
20081202                                           093522                                             20          15
20081204                                           092232                                             17          15
20081204                                           092552                                             19          15(5 行受影响)
*/CTE

Answer 6

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-06 21:47:11
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tbIF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([b] NVARCHAR(10),[c] NVARCHAR(10),[d] INT)
INSERT [tb]
SELECT '20081201','092152',15 UNION ALL
SELECT '20081201','092352',16 UNION ALL
SELECT '20081201','092632',11 UNION ALL
SELECT '20081201','096158',18 UNION ALL
SELECT '20081201','102130',15 UNION ALL
SELECT '20081202','092152',12 UNION ALL
SELECT '20081202','092342',13 UNION ALL
SELECT '20081202','093522',20 UNION ALL
SELECT '20081202','103652',15 UNION ALL
SELECT '20081204','092232',17 UNION ALL
SELECT '20081204','092552',19 UNION ALL
SELECT '20081204','095552',15
GO
--SELECT * FROM [tb]-->SQL查询如下:
select * 
from tb t 
where not exists(
    select 1 from tb a 
    where b=t.b and c=t.c and d between 
        (select top 1 d from tb where b=a.b order by b,c desc)*0.9 
        and (select top 1 d from tb where b=a.b order by b,c desc)*1.1)
    and exists(select 1 from tb where left(b,4)=left(t.b,4) and b<t.b)
/*
b          c          d
---------- ---------- -----------
20081202   092152     12
20081202   092342     13
20081202   093522     20
20081204   092232     17
20081204   092552     19(5 行受影响)
*/如果自每月自产生记录起算第一天，按此代码

Answer 7

IF NOT OBJECT_ID('[tb]') IS NULL
    DROP TABLE [tb]
GO
CREATE TABLE [tb]([b] NVARCHAR(10),[c] NVARCHAR(10),[d] INT)
INSERT [tb]
SELECT '20081201','092152',15 UNION ALL
SELECT '20081201','092352',16 UNION ALL
SELECT '20081201','092632',11 UNION ALL
SELECT '20081201','096158',18 UNION ALL
SELECT '20081201','102130',15 UNION ALL
SELECT '20081202','092152',12 UNION ALL
SELECT '20081202','092342',13 UNION ALL
SELECT '20081202','093522',20 UNION ALL
SELECT '20081202','103652',15 UNION ALL
SELECT '20081203','092232',17 UNION ALL
SELECT '20081204','092232',17 UNION ALL
SELECT '20081204','092552',19 UNION ALL
SELECT '20081204','095552',15
GOwith dt as (
select b,c,d,row_number() over (partition by b order by c desc) as row
from tb
where b not like '%01')
select bbb.b,bbb.c,bbb.d
from (select * from dt where row = 1) aaa inner join
(select * from dt where row = 1) bbb
on cast(aaa.b as datetime) = cast(bbb.b as datetime) - 1
--where (bbb.d < aaa.d*0.9 or bbb.d > aaa.d*1.1)
where (bbb.d not between aaa.d*0.9 and aaa.d*1.1)

Answer 8

--设表名为t
select a.* from t as a
left join (select * from t as a 
where not exists(select * from t where b=a.b and c>a.c)) as b --每天的最大时间的d值
on a.b=b.b-1
where a.b%100<>1 and 
(b.b is null or convert(numeric(18,2),a.d)/b.d<90/100 or convert(numeric(18,2),a.d)/b.d>110/100)