SQL 语法求助 ，高手赐教(已经重新排版)

总表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

SQL code

select * from s t where DATE=(select min(date) from s where itemcode=t.itemcode)

select * from s t where DATE=(select max(date) from s where itemcode=t.itemcode)

SQL code

create table S(itemcode varchar(10),[DATE] datetime,PRICE int)
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

SQL code

create table S(itemcode varchar(10),[DATE] datetime,PRICE int)
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

SQL code

use Tempdb
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

SQL code

select  * from t3 a where [date]=(select min([date]) from t3  where a.itemcode=itemcode)
select  * from t3 a where [date]=(select max([date]) from t3  where a.itemcode=itemcode)

输出表1 取每个itemcode的最早日期 

select t.* from s t where DATE = (select min(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)

输出表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)