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

如果两个特定值在同一天发生,则求和值

sql-server-2014 tsql

MISNole · 技术社区 · 7 年前

Species  Total  HarvestMonthDayYear  WeaponID  WeaponType
Deer     623    1/14/2017            1         Gun
Deer     10     1/14/2017            3         Reg Bow
Deer     15     1/14/2017            4         Crossbow
Deer     5      9/20/2017            4         Crossbow
Deer     9      9/21/2017            2         Muzzleloader
Deer     2      9/21/2017            3         Reg Bow
Deer     1      9/21/2017            4         Crossbow
Deer     1      9/21/2017            8         Shotgun
Deer     10     9/22/2017            1         Gun
Deer     1      9/22/2017            3         Reg Bow

我想要完成的是,当“注册弓”和“弩”在同一天收获,我想把结果合并成一行命名为“射箭”。最终数据集应该如下所示:

Species Total  HarvestMonthDayYear  WeaponID  WeaponType
Deer    623    1/14/2017            1         Gun
Deer    25     1/14/2017            10        Archery
Deer    5      9/20/2017            4         Crossbow
Deer    9      9/21/2017            2         Muzzleloader
Deer    3      9/21/2017            10        Archery
Deer    1      9/21/2017            8         Shotgun
Deer    10     9/22/2017            1         Gun
Deer    1      9/22/2017            2         Muzzleloader
Deer    1      9/22/2017            3         Reg Bow

这可能吗?我甚至想不出一个方法来开始编码。

3 回复 | 直到 7 年前

Tim Biegeleisen 7 年前

你可以将其表述为一个联盟:

WITH cte AS (
    SELECT Species, HarvestMonthDayYear, SUM(Total) AS Total
    FROM yourTable
    WHERE
        WeaponType IN ('Reg Bow', 'Crossbow')
    GROUP BY
        Species, HarvestMonthDayYear
    HAVING COUNT(DISTINCT WeaponType) = 2
)
SELECT
    t1.Species, t1.Total, t1.HarvestMonthDayYear, t1.WeaponID, t1.WeaponType
FROM yourTable t1
LEFT JOIN cte t2
    ON t1.Species = t2.Species AND
       t1.HarvestMonthDayYear = t2.HarvestMonthDayYear
WHERE
    t2.Species IS NULL OR
    (t2.Species IS NOT NULL AND WeaponType NOT IN ('Reg Bow', 'Crossbow'))
UNION ALL
SELECT
    Species, Total, HarvestMonthDayYear, 10, 'Archery'
FROM cte;

Rextester

Stéphane CLEMENT 7 年前

SELECT Species, HarvestMonthDayYear, SUM(Total) AS Total
    , CASE WHEN WeaponType IN ('Reg Bow', 'Crossbow') THEN 10 ELSE WeaponID END AS WeaponID
    , MAX(CASE WHEN WeaponType IN ('Reg Bow', 'Crossbow') THEN 'Archery' ELSE WeaponType END) AS WeaponType 
FROM yourTable
GROUP BY
     Species, HarvestMonthDayYear
     , (CASE WHEN WeaponType IN ('Reg Bow', 'Crossbow') THEN 10 ELSE WeaponID END)

SQLBadPanda 7 年前

这应该给你你需要的,使用CTE,但没有联合(测试)和稍微更高的性能。。。

WITH HarvestResults AS
(
SELECT  Species,
        Total,
        HarvestMonthDayYear,
        CASE WHEN WeaponType IN ('Crossbow','Reg Bow') THEN 10 ELSE WeaponID END AS WeaponID,
        WeaponType,
        LAG(HarvestMonthDayYear,1) OVER(ORDER BY Species,HarvestMonthDayYear) AS PrevHarvestDate,
        LEAD(HarvestMonthDayYear,1) OVER(ORDER BY Species,HarvestMonthDayYear) AS NextHarvestDate,
        LAG(WeaponType,1) OVER(ORDER BY Species,HarvestMonthDayYear) AS PrevWeaponType,
        LEAD(WeaponType,1) OVER(ORDER BY Species,HarvestMonthDayYear) AS NextWeponType
FROM dbo.Harvest
)
SELECT  Species,
        SUM(Total) AS TOTAL,
        HarvestMonthDayYear,
        CASE WHEN WeaponType IN ('Crossbow','Reg Bow') 
             AND ((PrevHarvestDate = HarvestMonthDayYear OR NextHarvestDate = HarvestMonthDayYear)
             AND  (PrevWeaponType IN ('Crossbow','Reg Bow') OR NextWeponType IN ('Crossbow','Reg Bow')))
             THEN 10 ELSE WeaponID END AS WeaponID,
        CASE WHEN WeaponType IN ('Crossbow','Reg Bow') 
             AND ((PrevHarvestDate = HarvestMonthDayYear OR NextHarvestDate = HarvestMonthDayYear)
             AND  (PrevWeaponType IN ('Crossbow','Reg Bow') OR NextWeponType IN ('Crossbow','Reg Bow')))
             THEN 'Archery' ELSE WeaponType END AS WeaponType
FROM HarvestResults
GROUP BY Species,
         HarvestMonthDayYear,
         CASE WHEN WeaponType IN ('Crossbow','Reg Bow') 
              AND ((PrevHarvestDate = HarvestMonthDayYear OR NextHarvestDate = HarvestMonthDayYear)
              AND  (PrevWeaponType IN ('Crossbow','Reg Bow') OR NextWeponType IN ('Crossbow','Reg Bow')))
              THEN 10 ELSE WeaponID END,
         CASE WHEN WeaponType IN ('Crossbow','Reg Bow') 
              AND ((PrevHarvestDate = HarvestMonthDayYear OR NextHarvestDate = HarvestMonthDayYear)
              AND  (PrevWeaponType IN ('Crossbow','Reg Bow') OR NextWeponType IN ('Crossbow','Reg Bow')))
              THEN 'Archery' ELSE WeaponType END
ORDER BY Species,
         HarvestMonthDayYear,
         WeaponID,
         WeaponType;