表中记录如下:日期 类别 数量
2010-1-5 A 52
2010-2-8 A 34
2010-3-4 A 21
2010-2-5 B 28
2010-2-7 B 38
2010-2-9 C 256
2010-3-4 C 123
2010-3-8 C 321
2010-4-1 C 111
2010-3-5 D 92
2010-3-8 D 95
===================================
按类别查,每个类别只取一条记录 ,
要查找小于2010-3-1的记录,
如果没有小于2010-3-1的记录,就取大于2010-3-1的第一条记录。
===================================
结果如下:日期 类别 数量
2010-2-8 A 34
2010-2-7 B 38
2010-2-9 C 256
2010-3-5 D 92
先谢了!
2010-1-5 A 52
2010-2-8 A 34
2010-3-4 A 21
2010-2-5 B 28
2010-2-7 B 38
2010-2-9 C 256
2010-3-4 C 123
2010-3-8 C 321
2010-4-1 C 111
2010-3-5 D 92
2010-3-8 D 95
===================================
按类别查,每个类别只取一条记录 ,
要查找小于2010-3-1的记录,
如果没有小于2010-3-1的记录,就取大于2010-3-1的第一条记录。
===================================
结果如下:日期 类别 数量
2010-2-8 A 34
2010-2-7 B 38
2010-2-9 C 256
2010-3-5 D 92
先谢了!
if object_id('[tb]') is not null drop table [tb]
create table [tb] (日期 datetime,类别 varchar(1),数量 int)
insert into [tb]
select '2010-1-5','A',52 union all
select '2010-2-8','A',34 union all
select '2010-3-4','A',21 union all
select '2010-2-5','B',28 union all
select '2010-2-7','B',38 union all
select '2010-2-9','C',256 union all
select '2010-3-4','C',123 union all
select '2010-3-8','C',321 union all
select '2010-4-1','C',111 union all
select '2010-3-5','D',92 union all
select '2010-3-8','D',95
goselect * from [tb] a where not exists(select 1 from tb where 类别=a.类别 and 日期<'2010-3-1' and 日期>a.日期) and a.日期<'2010-3-1'
union all
select * from [tb] a where not exists(select 1 from tb where 类别=a.类别 and 日期<'2010-3-1') and
(not exists(select 1 from tb where 类别=a.类别 and 日期<a.日期 and 日期>'2010-3-1') and a.日期>'2010-3-1')--结果:
日期 类别 数量
----------------------- ---- -----------
2010-02-08 00:00:00.000 A 34
2010-02-07 00:00:00.000 B 38
2010-02-09 00:00:00.000 C 256
2010-03-05 00:00:00.000 D 92
if rs.eof then '有小于2010-3-1的记录执行下面
sql="select * from tablename where riqi>'2010-3-1'"
else 有小雨2010-3-1的 执行下面
......
IF OBJECT_ID('[t1]') IS NOT NULL
DROP TABLE [t1]
GO
CREATE TABLE [t1] ([日期] [datetime],[类别] [nvarchar](10),[数量] [int])
INSERT INTO [t1]
SELECT '2010-1-5','A','52' UNION ALL
SELECT '2010-2-8','A','34' UNION ALL
SELECT '2010-3-4','A','21' UNION ALL
SELECT '2010-2-5','B','28' UNION ALL
SELECT '2010-2-7','B','38' UNION ALL
SELECT '2010-2-9','C','256' UNION ALL
SELECT '2010-3-4','C','123' UNION ALL
SELECT '2010-3-8','C','321' UNION ALL
SELECT '2010-4-1','C','111' UNION ALL
SELECT '2010-3-5','D','92' UNION ALL
SELECT '2010-3-8','D','95'--SELECT * FROM [t1]-->SQL查询如下:
;WITH t AS
(
SELECT *, rn = ROW_NUMBER()OVER(
PARTITION BY 类别 ORDER BY CASE
WHEN 日期<'2010-3-1' THEN 0
ELSE 1
END, ABS(DATEDIFF(dd, 日期, '2010-3-1'))
)
FROM t1
)
SELECT 日期,类别,数量
FROM t
WHERE rn=1
/*
日期 类别 数量
----------------------- ---------- -----------
2010-02-08 00:00:00.000 A 34
2010-02-07 00:00:00.000 B 38
2010-02-09 00:00:00.000 C 256
2010-03-05 00:00:00.000 D 92(4 行受影响)
*/
insert into @a
select '2010-1-5','A',52 union all
select '2010-2-8','A',34 union all
select '2010-3-4','A',21 union all
select '2010-2-5','B',28 union all
select '2010-2-7','B',38 union all
select '2010-2-9','C',256 union all
select '2010-3-4','C',123 union all
select '2010-3-8','C',321 union all
select '2010-4-1','C',111 union all
select '2010-3-5','D',92 union all
select '2010-3-8','D',95
SELECT * FROM @a a WHERE 日期=isnull
((select max(日期) from @a where 类别=a.类别 and 日期<'2010-3-1'),
(select min(日期) from @a where 类别=a.类别 and 日期>'2010-3-1'))--result
/*日期 类别 数量
-------------------------------------------------- ---- -----------
2010-02-08 00:00:00.000 A 34
2010-02-07 00:00:00.000 B 38
2010-02-09 00:00:00.000 C 256
2010-03-05 00:00:00.000 D 92(所影响的行数为 4 行)*/
IF object_id('ta_a')IS NOT NULL DROP TABLE ta_a
CREATE TABLE ta_a(DATE DATETIME,class VARCHAR(10),num INT )
INSERT INTO ta_a
SELECT '2010-1-5', 'A' ,52 UNION ALL
SELECT '2010-2-8' ,'A' ,34 UNION ALL
SELECT '2010-3-4' ,'A', 21 UNION ALL
SELECT '2010-2-5' ,'B' ,28 UNION ALL
SELECT '2010-2-7', 'B', 38 UNION ALL
SELECT '2010-2-9' ,'C', 256 UNION ALL
SELECT '2010-3-4', 'C' ,123 UNION ALL
SELECT '2010-3-8', 'C', 321 UNION ALL
SELECT '2010-4-1' ,'C' ,111 UNION ALL
SELECT '2010-3-5' ,'D' ,92 UNION ALL
SELECT '2010-3-8' ,'D' ,95select * from ta_a a where not exists(select 1 from tb where class=a.class and date<'2010-3-1' and date>a.date) and a.date<'2010-3-1'
union all
select * from ta_a a where not exists(select 1 from tb where class=a.class and DATE <'2010-3-1') and
(not exists(select 1 from tb where class=a.class AND date<a.date and date>'2010-3-1') and a.date>'2010-3-1')