请教个SQL语句
现在有下面一个表
RIV_NAME Username RIV_NUM RIV2_EXAMINE RIV2_DATE
A 张山 71 杨行镇 2007-3-12 11:10:37
A 张山 83 杨行镇 2007-3-12 13:57:28
A 张山 77 杨行镇 2007-3-12 13:58:08
B 李四 91 杨行镇 2007-2-23 13:52:14
B 李四 80 月浦镇 2007-3-23 14:41:35
B 张山 73 杨行镇 2007-3-23 15:05:05
C 王五 90 杨行镇 2007-3-27 13:13:11
C 王五 94 杨行镇 2007-4-27 13:19:12
C 王五 92 杨行镇 2007-3-27 13:19:36
C 王五 95 杨行镇 2007-4-27 13:23:16
我想显示成
RIV_NAME RIV_NUM1 RIV_NUM2 RIV_NUM3 RIV2_EXAMINE RIV2_DATE
A 71 83 77 杨行镇 3月
B 91 80 73 杨行镇 3月
C 90 92 空 杨行镇 3月
请大家帮忙.我想不出怎么写.谢谢了
------解决方案--------------------
select RIV_NAME, RIV_NUM1, RIV_NUM2, RIV_NUM3, RIV2_EXAMINE, RIV2_DATE from [表] a where not exists(select 1 from [表] where riv_name=a.riv_name and riv2_date <a.riv2_date)
[解决办法]
--修改下數據，即可得到你的結果
Create Table 表
(RIV_NAMEVarchar(10),
UsernameNvarchar(10),
RIV_NUMInt,
RIV2_EXAMINENvarchar(10),
RIV2_DATE DateTime)
Insert 表 Select 'A ', N '张山 ', 71, N '杨行镇 ', '2007-3-12 11:10:37 '
Union All Select 'A ', N '张山 ', 83, N '杨行镇 ', '2007-3-12 13:57:28 '
Union All Select 'A ', N '张山 ', 77, N '杨行镇 ', '2007-3-12 13:58:08 '
Union All Select 'B ', N '李四 ', 91, N '杨行镇 ', '2007-3-23 13:52:14 '
Union All Select 'B ', N '李四 ', 80, N '月浦镇 ', '2007-3-23 14:41:35 '
Union All Select 'B ', N '张山 ', 73, N '杨行镇 ', '2007-3-23 15:05:05 '
Union All Select 'C ', N '王五 ', 90, N '杨行镇 ', '2007-3-27 13:13:11 '
Union All Select 'C ', N '王五 ', 94, N '杨行镇 ', '2007-4-27 13:19:12 '
Union All Select 'C ', N '王五 ', 92, N '杨行镇 ', '2007-3-27 13:19:36 '
Union All Select 'C ', N '王五 ', 95, N '杨行镇 ', '2007-4-27 13:23:16 '
GO
Declare @S Varchar(8000)
Select @S = 'Select RIV_NAME '
Select @S = @S + ', Max(Case ID When ' + Rtrim(ID) + ' Then Rtrim(RIV_NUM) Else ' ' ' ' End) As [RIV_NUM ' + Rtrim(ID) + '] ' From
(Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = 3) As ID, * From 表 A Where Month(RIV2_DATE) = 3) A Group By ID
Select @S = @S+ ' , Max(RIV2_EXAMINE) As RIV2_EXAMINE, DateName(mm, RIV2_DATE) As RIV2_DATE From (Select (Select Count(*) From 表 Where RIV_NAME = A.RIV_NAME And RIV2_DATE <= A.RIV2_DATE And Month(RIV2_DATE) = 3) As ID, * From 表 A Where Month(RIV2_DATE) = 3) A Where Month(RIV2_DATE) = 3 Group By RIV_NAME, DateName(mm, RIV2_DATE) '
EXEC(@S)
GO
Drop Table 表
--Result
/*
RIV_NAMERIV_NUM1RIV_NUM2RIV_NUM3RIV2_EXAMINERIV2_DATE
A718377杨行镇三月
B918073杨行镇三月
C9092杨行镇三月
*/
