代码之家 › 专栏 › 技术社区 › edosoft

当为空时,查看以选择特定时段或最新时段

stored-procedures tsql sql-server

edosoft · 技术社区 · 16 年前

create table product(id int primary key identity, productid int, year int, quarter int, price money)

以及一些样本数据:

insert into product select 11, 2010, 1, 1.11
insert into product select 11, 2010, 2, 2.11
insert into product select 11, 2010, 3, 3.11
insert into product select 12, 2010, 1, 1.12
insert into product select 12, 2010, 2, 2.12
insert into product select 13, 2010, 1, 1.13

如何创建可用于返回(例如)第2季度价格的视图?我写此文是为了返回当前(=最新)价格:

CREATE VIEW vwCurrentPrices AS
    SELECT *
    FROM
    (
      SELECT  *, ROW_NUMBER() OVER (PARTITION BY productid  ORDER BY year DESC, quarter DESC) AS Ranking        
      FROM product
    ) p
    WHERE p.Ranking = 1

我想创建一个视图,这样我就可以使用如下查询 select * from vwProduct where quarter = 2

2 回复 | 直到 16 年前

Anthony Faull 16 年前

CREATE TABLE quarters ([year] int, [quarter] int, PRIMARY KEY([year],[quarter]))

INSERT INTO quarters ([year], [quarter])
SELECT 2000 + number/4 [year], number%4 + 1 [quarter]
FROM master.dbo.spt_values
WHERE type = 'P' AND number BETWEEN 0 and 199

CREATE VIEW pricing WITH SCHEMABINDING AS 
WITH pricerank AS
    (SELECT q.[year], q.[quarter], p.productid, p.price, 
    p.[year] effectiveyear, p.[quarter] effectivequarter, 
    ROW_NUMBER() OVER (PARTITION BY p.productid, 
    q.[year], q.[quarter] ORDER BY p.[year] DESC, p.[quarter] DESC) AS ranking 
    FROM dbo.quarters q INNER JOIN dbo.product p
    ON p.[year] = q.[year] AND p.[quarter] <= q.[quarter] OR p.[year] < q.[year]
    WHERE q.[year] <= YEAR(GETDATE()))
SELECT [year], [quarter], productid, price
FROM pricerank
WHERE ranking = 1

CREATE UNIQUE CLUSTERED INDEX IX_pricing ON pricing([year],[quarter],productid)

edosoft 16 年前

经过一番试验,我想出了一个自定义项,实现了我想要的结果。它首先过滤所需日期(季度+年度)之前的所有价格,然后获取最高行数(即最新结果)。

CREATE FUNCTION dbo.fTest(@year int, @quarter int)
RETURNS TABLE 
AS

return
(
    select * from
    (
        SELECT  *, 
        ROW_NUMBER() OVER (PARTITION BY productid  ORDER BY year DESC, quarter DESC ) AS rownum
        FROM product
        where DateAdd(month, (quarter - 1) * 3, DateAdd(Year, year - 1900, 0)) <= DateAdd(month, (@quarter - 1) * 3, DateAdd(Year, @year - 1900, 0))
    ) p
    where rownum = 1
)

推荐文章