问一SQL
Table A
UserNo ProductNo DirectorNo EndDate
---------------------------------------------
1 1 3 2007-01-08
1 2 2 2007-05-08
2 1 1 2007-08-09
SELECT UserNo, ProductNo, MAX(EndDate) FROM [A]
GROUP UserNo, ProductNo
如果结果要多加一 DirectorNo 列, DirectorNo的值等于 MAX(EndDate) 的 DirectorNo
怎么写？？
谢谢
[解决办法]
select * from [A]
inner join
(SELECT UserNo, ProductNo, MAX(EndDate) EndDate FROM [A]
GROUP By UserNo, ProductNo) b
on a.UserNo=b.UserNo and a.ProductNo=b.ProductNo and a.EndDate =b.EndDate
[解决办法]
if object_id( 'pubs..tb ') is not null
drop table tb
go
create table tb(UserNo int,ProductNo int,DirectorNo int,EndDate datetime)
insert into tb(UserNo,ProductNo,DirectorNo,EndDate) values(1,1,3, '2007-01-08 ')
insert into tb(UserNo,ProductNo,DirectorNo,EndDate) values(1,2,2, '2007-05-08 ')
insert into tb(UserNo,ProductNo,DirectorNo,EndDate) values(2,1,1, '2007-08-09 ')
select a.* from tb a,
(SELECT UserNo, ProductNo, MAX(EndDate) as enddate FROM tb GROUP by UserNo, ProductNo) b
where a.UserNo = b.UserNo and a.ProductNo=b.ProductNo and a.EndDate = b.EndDate
drop table tb
/*
UserNo ProductNo DirectorNo EndDate
----------- ----------- ----------- -----------------------
2 1 1 2007-08-09 00:00:00.000
1 2 2 2007-05-08 00:00:00.000
1 1 3 2007-01-08 00:00:00.000
*/
