时间戳

首页 > 解决方案 > SQL。创建一个查询，将在非连续年份中查找非空金额

sql-server - SQL。创建一个查询，将在非连续年份中查找非空金额

问题描述

我需要找到任何在非连续年份利润不为零的公司（例如 2007 年利润 = 10,000.00；2008 年利润 = 零；2009 年利润 = 12,000.00）。

以下是数据示例：

Company year    Profit
ABW 2004    22566.68216
ABW 2005    23302.83199
ABW 2006    24015.42061
ABW 2007    25921.28214
ABW 2008    27549.34573
ABW 2009    24639.93533
ABW 2010    24289.14152
ABW 2011    25354.78247
ABW 2012    ---
AFG 2004    220.0562878
AFG 2005    252.4078925
AFG 2006    275.3509409
AFG 2007    373.5914162
AFG 2008    ----
AFG 2009    450.659239
AFG 2010    561.1976175
AFG 2011    613.9791916
AFG 2012    687.245475
AGO 2004    1229.342988
AGO 2005    1706.543616
AGO 2006    2440.631716
AGO 2007    3412.718998
AGO 2008    ----
AGO 2009    ----
AGO 2010    4218.649126
AGO 2011    5159.233666
AGO 2012    5482.428049
ALB 2004    2320.89233
ALB 2005    2620.820724
ALB 2006    2872.260625
ALB 2007    3380.894192
ALB 2008    4108.414124

谢谢你的帮助！

jb

标签： sql-server

解决方案

这就是你所追求的吗？

IF OBJECT_ID('tempdb..#TestData', 'U') IS NOT NULL 
BEGIN DROP TABLE #TestData; END;

CREATE TABLE #TestData (
    company CHAR(3) NOT NULL,
    [year] INT NOT NULL,
    profit DECIMAL(18,10) NULL 
    );
INSERT #TestData (company, year, profit) VALUES
    ('ABW', 2004, 22566.68216),
    ('ABW', 2005, 23302.83199),
    ('ABW', 2006, 24015.42061),
    ('ABW', 2007, 25921.28214),
    ('ABW', 2008, 27549.34573),
    ('ABW', 2009, 24639.93533),
    ('ABW', 2010, 24289.14152),
    ('ABW', 2011, 25354.78247),
    ('ABW', 2012, NULL),
    ('AFG', 2004, 220.0562878),
    ('AFG', 2005, 252.4078925),
    ('AFG', 2006, 275.3509409),
    ('AFG', 2007, 373.5914162),
    ('AFG', 2008, NULL),
    ('AFG', 2009, 450.659239 ),
    ('AFG', 2010, 561.1976175),
    ('AFG', 2011, 613.9791916),
    ('AFG', 2012, 687.245475 ),
    ('AGO', 2004, 1229.342988),
    ('AGO', 2005, 1706.543616),
    ('AGO', 2006, 2440.631716),
    ('AGO', 2007, 3412.718998),
    ('AGO', 2008, NULL),
    ('AGO', 2009, NULL),
    ('AGO', 2010, 4218.649126),
    ('AGO', 2011, 5159.233666),
    ('AGO', 2012, 5482.428049),
    ('ALB', 2004, 2320.89233 ),
    ('ALB', 2005, 2620.820724),
    ('ALB', 2006, 2872.260625),
    ('ALB', 2007, 3380.894192),
    ('ALB', 2008, 4108.414124);

--==============================================

SELECT 
    *
FROM (
    SELECT 
        td.company,
        td.year,
        td.profit,
        prev_prof_year = MAX(CASE WHEN td.profit IS NOT NULL THEN td.year END) OVER (PARTITION BY td.company ORDER BY td.year ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
    FROM
        #TestData td
    ) ppy
WHERE 
    ppy.profit IS NOT NULL 
    AND ppy.year = ppy.prev_prof_year + 2;

结果：

company year        profit                                  prev_prof_year
------- ----------- --------------------------------------- --------------
AFG     2009        450.6592390000                          2007

推荐阅读