select *
from tb t
where not exists(select * from tb where productid=t.productid and userdate>t.userdate)
from tb t
where not exists(select * from tb where productid=t.productid and userdate>t.userdate)
调试欢乐多
ProductID ProductName Price Discount SPrice Unit userdate
07719 兄弟TN-2050粉 380 1.07344632768 354 盒 2009-2-11 7:58:26
05377 12开荣誉证书 7 1 7 本 2009-2-13 8:09:10
05311 荣誉证书内芯 1 2 .5 张 2009-2-17 7:54:27
06290 前通P03-B501聘书 22 1 22 本 2009-2-17 8:01:20
06892 文仪进口卡纸180克A3 96 1 96 包 2009-2-17 8:02:45
显然结果不对
SELECT ProductID,ProductName,Price,Discount,SPrice,Unit,MAX(userdate) userdate
FROM 表
GORUP BY ProductID,ProductName,Price,Discount,SPrice,Unit
ALTER TABLE 表 ADD fid INT
DECLARE @n INT
SET @n=0
UPDATE 表 SET fid=@n,@n=@n+1
SELECT * FROM 表
WHERE fid IN (SELECT a.fid FROM 表 a JOIN
(SELECT ProductID,max(userdate) userdate FROM 表 GROUP BY ProductID) b
ON a.ProductID=b.ProductID AND a.userdate = b.userdate )
ALTER TABLE 表 DROP COLUMN fid
--自增列也可以这样新增:
ALTER TABLE 表 ADD fid INT IDENTITY
SELECT * FROM 表
WHERE fid IN (SELECT a.fid FROM 表 a JOIN
(SELECT ProductID,max(userdate) userdate FROM 表 GROUP BY ProductID) b
ON a.ProductID=b.ProductID AND a.userdate = b.userdate )
ALTER TABLE 表 DROP COLUMN fid
( ProductID VARCHAR(50), ProductName VARCHAR(50),
Price VARCHAR(50), Discount VARCHAR(50),
SPrice VARCHAR(50), Unit VARCHAR(50),
userdate VARCHAR(50)
)
insert into a
select '00783', '理光传真纸210*30', '10', '1', '10', '卷' ,'2008-11-3 10:38:58' union
select '04691', 'HP硒鼓Q2612A', '490', '1.0652173913', '460', '只', '2009-1-4 14:21:47' union
select '04691', 'HP硒鼓Q2612A', '490', '1.0208333333333', '480', '只', '2009-1-13 9:11:20' union
select '03549', '富得快资料档案盒HY3003(塑料)', '12', '1', '12', '只', '2009-2-1 10:11:03' union
select '03549', '富得快资料档案盒HY3003(塑料)', '12', '1', '12', '只', '2009-2-1 10:22:35'select * from a,
(SELECT ProductID,MAX(userdate) as userdate
FROM a group BY ProductID) b
where b.ProductID =a.ProductID and b.userdate = a.userdatedrop table a
( ProductID VARCHAR(50), ProductName VARCHAR(50),
Price VARCHAR(50), Discount VARCHAR(50),
SPrice VARCHAR(50), Unit VARCHAR(50),
userdate VARCHAR(50)
)
insert into a
select '00783', '理光传真纸210*30', '10', '1', '10', '卷' ,'2008-11-3 10:38:58' union
select '04691', 'HP硒鼓Q2612A', '490', '1.0652173913', '460', '只', '2009-1-4 14:21:47' union
select '04691', 'HP硒鼓Q2612A', '490', '1.0208333333333', '480', '只', '2009-1-13 9:11:20' union
select '03549', '富得快资料档案盒HY3003(塑料)', '12', '1', '12', '只', '2009-2-1 10:11:03' union
select '03549', '富得快资料档案盒HY3003(塑料)', '12', '1', '12', '只', '2009-2-1 10:22:35' select a.* from a,
(SELECT ProductID,MAX(userdate) as userdate
FROM a group BY ProductID) b
where b.ProductID =a.ProductID and b.userdate = a.userdate drop table a
00783 理光传真纸210*30 10 1 10 卷 2008-11-3 10:38:58
03549 富得快资料档案盒HY3003(塑料) 12 1 12 只 2009-2-1 10:22:35
04691 HP硒鼓Q2612A 490 1.0652173913 460 只 2009-1-4 14:21:47