一个复杂的sql语句，求指点，带表结构和测试数据，+挑战性能有关问题

一个复杂的sql语句，求指点，带表结构和测试数据，+挑战性能问题
本帖最后由 weicongjin 于 2013-05-17 23:26:48 编辑 两个表,产品表Products和价格表Prices. 一个产品对应多个价格。隔几天添加一次价格，并更新Products表的UpdateTime字段。
表结构如下：

CREATE TABLE Products(
    [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Name] [nvarchar](80) NOT NULL,
    [Image_url] [varchar](400) NULL,
[ClassId] [int] NULL,
    [UpdateTime] [datetime] NOT NULL
)
GO
CREATE TABLE Prices(
    [Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
    [Price] [money] NOT NULL,
    [PId] [int] NOT NULL,--产品表的Id
    [Time] [datetime] NOT NULL
)
GO
INSERT INTO Products VALUES ('剃须刀','http://images.csdn.net/20130517/11101.jpg',6,'2012-4-11 10:16:12')
INSERT INTO Products VALUES ('连衣裙','http://images.csdn.net/20130517/11101.jpg',6,'2012-4-12 10:16:12')
INSERT INTO Products VALUES ('小米手机','http://images.csdn.net/20130517/11101.jpg',6,'2012-4-13 10:16:12')
INSERT INTO Products VALUES ('笔记本','http://images.csdn.net/20130517/11101.jpg',6,'2012-5-11 10:16:12')
INSERT INTO Products VALUES ('联想电脑','http://images.csdn.net/20130517/11101.jpg',6,'2012-5-12 10:16:12')
INSERT INTO Products VALUES ('毛巾','http://images.csdn.net/20130517/11101.jpg',6,'2012-5-13 10:16:12')
INSERT INTO Products VALUES ('赛车','http://images.csdn.net/20130517/11101.jpg',6,'2012-5-14 10:16:12')
INSERT INTO Products VALUES ('电冰箱','http://images.csdn.net/20130517/11101.jpg',6,'2012-5-18 10:16:12')
INSERT INTO Products VALUES ('平板电脑','http://images.csdn.net/20130517/11101.jpg',6,'2012-5-22 10:16:12')
INSERT INTO Products VALUES ('冰与火','http://images.csdn.net/20130517/11101.jpg',6,'2012-5-17 20:16:12')
GO
INSERT INTO Prices VALUES (20,1,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (29,2,'2012-6-12 10:16:12')
INSERT INTO Prices VALUES (21,2,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (29,4,'2012-6-12 10:16:12')
INSERT INTO Prices VALUES (29,4,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (30,2,'2012-6-14 10:16:12')
INSERT INTO Prices VALUES (31,3,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (29,3,'2012-6-12 10:16:12')
INSERT INTO Prices VALUES (21,3,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (22,3,'2012-6-14 10:16:12')
INSERT INTO Prices VALUES (20,4,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (20,6,'2012-6-14 10:16:12')
INSERT INTO Prices VALUES (20,7,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (20,4,'2012-6-14 10:16:12')
INSERT INTO Prices VALUES (50,5,'2012-6-14 10:16:12')
INSERT INTO Prices VALUES (20,6,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (20,6,'2012-6-12 10:16:12')
INSERT INTO Prices VALUES (21,1,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (20,6,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (29,7,'2012-6-12 10:16:12') 
 
INSERT INTO Prices VALUES (20.5,7,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (11,7,'2012-6-14 10:16:12')
INSERT INTO Prices VALUES (50,5,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (24,9,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (29,10,'2012-6-12 10:16:12')
INSERT INTO Prices VALUES (59,5,'2012-6-12 10:16:12')
INSERT INTO Prices VALUES (50,5,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (20,9,'2012-6-14 10:16:12')
INSERT INTO Prices VALUES (14.7,10,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (20,8,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (29,8,'2012-6-12 10:16:12')
INSERT INTO Prices VALUES (21,8,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (20,8,'2012-6-14 10:16:12')
INSERT INTO Prices VALUES (20,1,'2012-6-14 10:16:12')
INSERT INTO Prices VALUES (30,9,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (29,1,'2012-6-12 10:16:12')
INSERT INTO Prices VALUES (40,2,'2012-6-11 10:16:12')
INSERT INTO Prices VALUES (29,9,'2012-6-12 10:16:12')
INSERT INTO Prices VALUES (27,10,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (20,10,'2012-6-14 10:16:12')

求: 最近有降过价的商品.包括降价幅度。
结果列结构是这样的：

这个用什么方法实现好呢？
我的理解是：用最后一次记录的价格减去前一次记录的价格,结果>0 的产品详细（包括降价幅度），按UpdateTime倒序。但是不会写


我下午的时候提问过，但是有一些需要补充的，只好重发一次。http://bbs.csdn.net/topics/390462327
大侠帮忙写一下实现语句，谢谢。   
PS：Products表数据量在200万左右，Prices表数据量1千万，如果用存储过程，性能会不会好一点

[解决办法]
 这样效果，只显示“用最后一次记录的价格减去前一次记录的价格,结果>0 的产品详细（包括降价幅度）”

select a.*,b.*
from Products as a
inner join Prices as b on a.Id=b.PId
where not exists(select 1 from Prices where PId=b.PId and [Time]>b.[Time])
and b.[Price]-isnull((select top 1 [Price] from Prices where PId=b.PId and [Time]<b.[Time]  order by [Time] desc),b.[Price])>0
order by a.UpdateTime desc

[解决办法]

--------- 查出最近减价的商品
With t as 
(Select  *,ROW_NUMBER() Over(Partition by pid order by [time] desc) as rn
 From Prices
)
 Select p.*,tt.cg * 100. as '降价幅度'
 From Products as p
 Inner Join (
 Select t1.PId,(t2.Price - t1.Price) / t1.Price as cg
 From t as t1
 Inner Join t as t2
 On t1.PId = t2.PId  
 
 Where t1.rn = t2.rn - 1
 And t1.Price - t2.Price  < 0
 And t1.rn = 1) as tt
 On p.Id = tt.PId
----------------------------------------------
--1剃须刀http://images.csdn.net/20130517/11101.jpg62012-04-11 10:16:12.0005.0000
--4笔记本http://images.csdn.net/20130517/11101.jpg62012-05-11 10:16:12.00045.0000
--7赛车http://images.csdn.net/20130517/11101.jpg62012-05-14 10:16:12.00086.3600
--8电冰箱http://images.csdn.net/20130517/11101.jpg62012-05-18 10:16:12.0005.0000
--9平板电脑http://images.csdn.net/20130517/11101.jpg62012-05-22 10:16:12.00020.0000
--10冰与火http://images.csdn.net/20130517/11101.jpg62012-05-17 20:16:12.00035.0000
----------

我有几个问题：一是，我的跟你给的答案有点对不上，我对了下数据，没发现什么逻辑错误。
二是，我不明白你的降价幅度是怎么算的。
三是，你说降价是指最后一次记录的价格减去前一次结果小于0，那么小米手机的最近两条记录
INSERT INTO Prices VALUES (21,3,'2012-6-13 10:16:12')
INSERT INTO Prices VALUES (22,3,'2012-6-14 10:16:12')
是没有降价的，为什么会出现在结果中呢。
四是，prices中最新的价格变化时间好像跟product对应商品中的time不一致。

查看更多 下一篇