日期:2014-05-17 浏览次数:20498 次
CREATE TABLE Goods(
[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] [nvarchar](80) NOT NULL,
[Image_url] [varchar](400) NULL,
[UpdateTime] [datetime] NOT NULL
)
GO
CREATE TABLE Prices(
[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Price] [money] NOT NULL,
[GId] [int] NOT NULL,--商品表的Id
[Time] [datetime] NOT NULL
)
SELECT [id],[name],[image_url],[UpdateTime],SUM([Price])
FROM (
SELECT g.*,p.[Price]
FROM Goods G INNER JOIN Prices P ON g.id=p.gid
WHERE EXISTS (SELECT 1 FROM (
SELECT g.NAME ,MAX( [UpdateTime]) [UpdateTime]
FROM Goods G
GROUP BY g.NAME )b WHERE g.NAME=b.NAME AND g.[UpdateTime]=b.[UpdateTime])
UNION ALL
SELECT g.*,-1*p.[Price]
FROM Goods G INNER JOIN Prices P ON g.id=p.gid
WHERE EXISTS (SELECT 1 FROM (
SELECT g.NAME ,MAX( [UpdateTime])-1 [UpdateTimeY]--获取上一天的日期
FROM Goods G
GROUP BY g.NAME )b WHERE g.NAME=b.NAME AND g.[UpdateTime]=b.[UpdateTimeY]))a
GROUP BY [id],[name],[image_url],[UpdateTime]
HAVING SUM([Price])<0
---最近有降过价的商品.
CREATE TABLE Goods(
[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Name] [nvarchar](80) NOT NULL,
[Image_url] [varchar](400) NULL,
[UpdateTime] [datetime] NOT NULL
)
GO
CREATE TABLE Prices(
[Id] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[Price] [money] NOT NULL,
[GId] [int] NOT NULL,--商品表的Id
[Time] [datetime] NOT NULL
)
Go
Insert into Prices(Price,GId,Time)
Select 10,1,'20100101' Union All
Select 20,1,'20100102' Union All
Select 15,1,'20100103' Union All
Select 10,2,'20100101' Union All
Select 20,2,'20100102' Union ALl
Select 10,3,'20100101'
Insert Into Goods(Name,UpdateTime)
Select 'a','20100103' Union All
Select 'b','20100102' Union All
Select 'c','20100101'
Go
--------- 查出最近减价的商品
With t as
(Select *,ROW_NUMBER() Over(Partition by gid order&nbs