总表S
itemcode DATE PRICE
A 2011.01.01 11
B 2011.05.01 24
C 2011.10.09 13
D 2011.01.01 23
A 2011.05.29 25
C 2011.01.11 14
B 2011.10.09 15
D 2011.05.06 15
B 2011.01.01 18
A 2011.10.15 28
D 2011.10.08 9
C 2011.05.05 17
输出表1 取每个itemcode的最早日期 itemcode DATE PRICE
A 2011.01.01 11
B 2011.01.01 18
C 2011.01.11 14
D 2011.01.01 23
输出表2 取每个itemcode的最近日期 itemcode DATE PRICE
A 2011.10.15 28
B 2011.10.09 15
C 2011.10.09 13
D 2011.10.08 9
itemcode DATE PRICE
A 2011.01.01 11
B 2011.05.01 24
C 2011.10.09 13
D 2011.01.01 23
A 2011.05.29 25
C 2011.01.11 14
B 2011.10.09 15
D 2011.05.06 15
B 2011.01.01 18
A 2011.10.15 28
D 2011.10.08 9
C 2011.05.05 17
输出表1 取每个itemcode的最早日期 itemcode DATE PRICE
A 2011.01.01 11
B 2011.01.01 18
C 2011.01.11 14
D 2011.01.01 23
输出表2 取每个itemcode的最近日期 itemcode DATE PRICE
A 2011.10.15 28
B 2011.10.09 15
C 2011.10.09 13
D 2011.10.08 9
解决方案 »
- exists关联子查询问题
- 如何对A表插入B表
- 超时时间已到。在操作完成之前超时时间已过或服务器未响应。
- 一直比较疑问关于数据文件大小的问题?
- 昨天看了世贸大厦正被灾难所震栗,回家后发现我的数据也丢了.太惨了.
- :( 好郁闷的,通不过,帮看看呀 "将 varchar 转换为数据类型 numeric 时...
- 如何删除sysobjects表中以"DF"开头的记录?
- C#编程添加有GUID列的表至数据库
- 关于复杂SQL语句的问题 现在有两个表Tab1和Tab2,两个表分别用字段Main1和Main2关联,现在我想把Tab2中纪录的Stat2字段,全部通过Main1和
- 请问在erwin中如何将建立的model导成sql语句。
- 触发器编写问题,求教
- 用户信息表1(tb_1) 更新 用户信息表2 (tb_2)
insert into S select 'A','2011.01.01',11
insert into S select 'B','2011.05.01',24
insert into S select 'C','2011.10.09',13
insert into S select 'D','2011.01.01',23
insert into S select 'A','2011.05.29',25
insert into S select 'C','2011.01.11',14
insert into S select 'B','2011.10.09',15','
insert into S select 'D','2011.05.06',15
insert into S select 'B','2011.01.01',18
insert into S select 'A','2011.10.15',28
insert into S select 'D','2011.10.08',9
insert into S select 'C','2011.05.05',17
go
select * from S a where not exists(select 1 from S where itemcode=a.itemcode and [date]<a.[date])
/*
itemcode DATE PRICE
---------- ----------------------- -----------
A 2011-01-01 00:00:00.000 11
D 2011-01-01 00:00:00.000 23
C 2011-01-11 00:00:00.000 14
B 2011-01-01 00:00:00.000 18(4 行受影响)
*/
go
drop table S
insert into S select 'A','2011.01.01',11
insert into S select 'B','2011.05.01',24
insert into S select 'C','2011.10.09',13
insert into S select 'D','2011.01.01',23
insert into S select 'A','2011.05.29',25
insert into S select 'C','2011.01.11',14
insert into S select 'B','2011.10.09',15','
insert into S select 'D','2011.05.06',15
insert into S select 'B','2011.01.01',18
insert into S select 'A','2011.10.15',28
insert into S select 'D','2011.10.08',9
insert into S select 'C','2011.05.05',17
go
select * from S a where not exists(select 1 from S where itemcode=a.itemcode and [date]>a.[date])
/*
itemcode DATE PRICE
---------- ----------------------- -----------
C 2011-10-09 00:00:00.000 13
B 2011-10-09 00:00:00.000 15
A 2011-10-15 00:00:00.000 28
D 2011-10-08 00:00:00.000 9(4 行受影响)*/
go
drop table S
go
--> -->
if not object_id(N'Tempdb..#S') is null
drop table #S
Go
Create table #S([itemcode] nvarchar(1),[DATE] Datetime,[PRICE] int)
Insert #S
select N'A','2011.01.01',11 union all
select N'B','2011.05.01',24 union all
select N'C','2011.10.09',13 union all
select N'D','2011.01.01',23 union all
select N'A','2011.05.29',25 union all
select N'C','2011.01.11',14 union all
select N'B','2011.10.09',15 union all
select N'D','2011.05.06',15 union all
select N'B','2011.01.01',18 union all
select N'A','2011.10.15',28 union all
select N'D','2011.10.08',9 union all
select N'C','2011.05.05',17
Go
SELECT
*
FROM (Select *,ROW_NUMBER()OVER(PARTITION BY [itemcode] ORDER BY [DATE] DESC) AS row1,ROW_NUMBER()OVER(PARTITION BY [itemcode] ORDER BY [DATE] asc) AS row2 from #S
)t
WHERE row2=1
SELECT
*
FROM (Select *,ROW_NUMBER()OVER(PARTITION BY [itemcode] ORDER BY [DATE] DESC) AS row1,ROW_NUMBER()OVER(PARTITION BY [itemcode] ORDER BY [DATE] DESC) AS row2 from #S
)t
WHERE row1=1 SELECT
*
FROM (Select *,ROW_NUMBER()OVER(PARTITION BY [itemcode] ORDER BY [DATE] DESC) AS row1,ROW_NUMBER()OVER(PARTITION BY [itemcode] ORDER BY [DATE] asc) AS row2 from #S
)t
WHERE row1=1 OR row2=1
select * from t3 a where [date]=(select max([date]) from t3 where a.itemcode=itemcode)
select t.* from s t where not exists (select 1 from s where itemcode = t.itemcode and DATE < t.DATE)输出表2 取每个itemcode的最近日期 select t.* from s t where DATE = (select max(DATE) from s where itemcode = t.itemcode)
select t.* from s t where not exists (select 1 from s where itemcode = t.itemcode and DATE > t.DATE)