如何使用外部查詢計算子查詢CASEWHEN類別？

我在這個db<>fiddle 中有一組樣本資料。資料代表基于某些標準屬于不同井型類別的一批井。我正在嘗試按井所屬的類別對井進行分組，然后根據另一組標準計算每個類別中有多少井。

我當前的查詢部分有效，但只能正確計算CASE WHEN子句層次結構中較高的井。這是因為第一個CASE WHEN有機會為資料集中的所有井分配井類別。但是，當它遍歷每個CASE WHEN子句時，查詢“看到”更少的井，因為它用完了可以為其分配類別的井。當它到達終點時，幾乎所有的油井都已經分配了一個類別，完全阻止了某些類別計數的發生。

這是我當前的查詢，它也在上面的 db<>fiddle 鏈接中：

SELECT
    dt.WellCategory,
    ISNULL(SUM(CASE WHEN dt.LeaseType IN ('F','I','S','P') OR dt.LeaseType NOT IN ('F', 'I', 'S', 'P', 'U') THEN 1 END), 0) AS [Number of Wells], -- Federal   Indian   State   Fee   Multi-Lease
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'F' THEN 1 END), 0) AS [Federal], -- Sums up how many wells from the subquery have a leasetype of "Federal"
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'I' THEN 1 END), 0) AS [Indian],
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'S' THEN 1 END), 0) AS [State],
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'P' THEN 1 END), 0) AS [Fee (Private)],
    ISNULL(SUM(CASE WHEN dt.LeaseType NOT IN ('F', 'I', 'S', 'P', 'U') THEN 1 END), 0) AS [Multiple Lease Types]
FROM
(
    SELECT -- Subquery labels wells according to their wellstatus, welltype, etc.
        c.LeaseType,
        CASE 
            WHEN w.WellStatus = 'p' AND w.WellType = 'gw' OR ((w.WellStatus = 'pai' OR w.WellStatus = 'pii') AND (w.WellType = 'gwi' OR w.WellType = 'ggi' OR w.WellType = 'gwd')) THEN 'Producing Gas Wells'
            WHEN w.WellStatus = 'p' AND w.WellType = 'ow' OR ((w.WellStatus = 'pai' OR w.WellStatus = 'pii') AND (w.WellType = 'owi' OR w.WellType = 'ogi' OR w.WellType = 'owd')) THEN 'Producing Oil Wells'
            WHEN w.WellStatus = 's' AND w.WellType = 'ow' OR ((w.WellStatus = 'sai' OR w.WellStatus = 'sii') AND (w.WellType = 'owi' OR w.WellType = 'ogi' OR w.WellType = 'owd')) THEN 'Shut-In Oil Wells'
            WHEN w.WellStatus = 's' AND w.WellType = 'gw' OR ((w.WellStatus = 'sai' OR w.WellStatus = 'sii') AND (w.WellType = 'gwi' or w.WellType = 'ggi' or w.WellType = 'gwd')) THEN 'Shut-In Gas Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'wi' THEN 'Active Water Injection Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'gi' THEN 'Active Gas Injection Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'wd' THEN 'Active Water Disposal Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'gs' THEN 'Active Gas Storage Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'ws' THEN 'Active Water Source Wells'
            WHEN w.WellStatus = 'a' AND w.WellType = 'tw' THEN 'Active Test Holes'
            WHEN w.WellStatus = 'i' AND w.WellType = 'wi' THEN 'Inactive Water Injection Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'gi' THEN 'Inactive Gas Injection Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'wd' THEN 'Inactive Water Disposal Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'gs' THEN 'Inactive Gas Storage Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'ws' THEN 'Inactive Water Source Wells'
            WHEN w.WellStatus = 'i' AND w.WellType = 'tw' THEN 'Inactive Test Holes'
            WHEN w.WellStatus = 'ta' THEN 'Temporarily-Abandoned Wells'
            WHEN w.WellStatus = 'pa' THEN 'Plugged and Abandoned Wells'
            WHEN c.LateralStatus = 'NEW' THEN 'New Permits - Not Yet Approved'
            WHEN c.LateralStatus = 'APD' THEN 'Permits Approved - Not Yet Commenced'
            WHEN c.LateralStatus = 'DRL' THEN 'Drilling Commenced - Not Yet Completed'
            WHEN c.LateralStatus = 'OPS' THEN 'Drilling Operations Suspended'
            WHEN w.WellStatus IN ('drl','ops','p','s','ta','pai','pii','sai','sii','a','i') AND w.Operator = 101600 THEN 'Open Orphan Wells (no known operator)'
            WHEN w.WellStatus IN ('drl','ops') THEN 'Total Holes Not Yet Completed'
            WHEN ((w.WellStatus = 'p' or w.WellStatus = 's') AND w.WellType <> 'LI') OR (w.WellStatus = 'pai' OR w.WellStatus = 'pii' OR w.WellStatus = 'sai' OR w.WellStatus = 'sii') THEN 'Total Wells Capable of Production'
            WHEN (w.WellStatus = 'drl' OR w.WellStatus = 'ops' OR ((w.WellStatus = 'p' or w.WellStatus = 's') AND w.WellType <> 'LI') OR w.WellStatus = 'ta' OR w.WellStatus = 'pai' OR w.WellStatus = 'pii' OR w.WellStatus = 'sai' OR w.WellStatus = 'sii' OR w.WellStatus = 'a' OR w.WellStatus = 'i') THEN 'Total Non-Plugged Wells'
            WHEN (w.WellStatus = 'drl' or w.WellStatus = 'ops' or ((w.WellStatus = 'p' or w.WellStatus = 's') and w.WellType <> 'LI') or w.WellStatus = 'ta' or w.WellStatus = 'pai' or w.WellStatus = 'pii' or w.WellStatus = 'sai' or w.WellStatus = 'sii' or w.WellStatus = 'a' or w.WellStatus = 'i' or w.WellStatus = 'pa') THEN 'Total Wells Drilled'
        END AS WellCategory
    FROM HWell w
        LEFT JOIN HConstruct c ON c.WellKey = w.PKey
) dt
GROUP BY dt.WellCategory
ORDER BY dt.WellCategory DESC

這是上面查詢生成的表：

表格1

這是我知道相同資料集可以生成的表格以及我想要復制的表格：

表#2

Table #2 is generated using a much longer query that uses several subqueries and temp tables to count how many wells belong to each category using their DISTINCT w.WellID. In addition, the full data set has a lot more entries, usually each category has at least some wells in it and not so many "0's".

I don't know how to tell the query to count wells more than once if they fall into several well categories without making the query super long and start introducing temp tables, which I would prefer not to do. *NOTE I do not want to count a well twice for same category, only count it once per category.

uj5u.com熱心網友回復：

這里有一種方法可以做到這一點，同時保持它的可讀性：

我把這個CASE陳述分解成合乎邏輯的部分。不重疊但看起來相關的部分進入他們自己的查詢以確定類別。然后我將UNION ALL它們放在一起以避免重復資料洗掉。

有很多方法可以做到這一點，這只是其中之一......

此外，就像我在聊天會話中提到的那樣，如果可以，我強烈建議您對這些資料進行規范化、創建查找表等。

WITH cte_Wells AS (
    SELECT w.WellStatus, w.WellType, c.LateralStatus, c.LeaseType, w.Operator
    FROM dbo.HWell w
        LEFT JOIN dbo.HConstruct c ON c.WellKey = w.PKey
)
SELECT
    dt.WellCategory,
    ISNULL(SUM(CASE WHEN dt.LeaseType IN ('F','I','S','P') OR dt.LeaseType NOT IN ('F', 'I', 'S', 'P', 'U') THEN 1 END), 0) AS [Number of Wells], -- Federal   Indian   State   Fee   Multi-Lease
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'F' THEN 1 END), 0) AS [Federal], -- Sums up how many wells from the subquery have a leasetype of "Federal"
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'I' THEN 1 END), 0) AS [Indian],
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'S' THEN 1 END), 0) AS [State],
    ISNULL(SUM(CASE WHEN dt.LeaseType = 'P' THEN 1 END), 0) AS [Fee (Private)],
    ISNULL(SUM(CASE WHEN dt.LeaseType NOT IN ('F', 'I', 'S', 'P', 'U') THEN 1 END), 0) AS [Multiple Lease Types]
FROM (
    SELECT w.LeaseType, x.WellCategory
    FROM cte_Wells w
        CROSS APPLY (
            SELECT WellCategory = CASE 
                                    WHEN w.WellStatus = 'p' AND w.WellType = 'gw' OR (w.WellStatus IN ('pai','pii') AND w.WellType IN ('gwi','ggi','gwd')) THEN 'Producing Gas Wells'
                                    WHEN w.WellStatus = 'p' AND w.WellType = 'ow' OR (w.WellStatus IN ('pai','pii') AND w.WellType IN ('owi','ogi','owd')) THEN 'Producing Oil Wells'
                                    WHEN w.WellStatus = 's' AND w.WellType = 'gw' OR (w.WellStatus IN ('sai','sii') AND w.WellType IN ('gwi','ggi','gwd')) THEN 'Shut-In Gas Wells'
                                    WHEN w.WellStatus = 's' AND w.WellType = 'ow' OR (w.WellStatus IN ('sai','sii') AND w.WellType IN ('owi','ogi','owd')) THEN 'Shut-In Oil Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'wi' THEN 'Active Water Injection Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'gi' THEN 'Active Gas Injection Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'wd' THEN 'Active Water Disposal Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'gs' THEN 'Active Gas Storage Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'ws' THEN 'Active Water Source Wells'
                                    WHEN w.WellStatus = 'a' AND w.WellType = 'tw' THEN 'Active Test Holes'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'wi' THEN 'Inactive Water Injection Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'gi' THEN 'Inactive Gas Injection Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'wd' THEN 'Inactive Water Disposal Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'gs' THEN 'Inactive Gas Storage Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'ws' THEN 'Inactive Water Source Wells'
                                    WHEN w.WellStatus = 'i' AND w.WellType = 'tw' THEN 'Inactive Test Holes'
                                    WHEN w.WellStatus = 'ta' THEN 'Temporarily-Abandoned Wells'
                                    WHEN w.WellStatus = 'pa' THEN 'Plugged and Abandoned Wells'
                                    ELSE NULL
                                END
        ) x
    WHERE x.WellCategory IS NOT NULL
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, x.WellCategory
    FROM cte_Wells w
        CROSS APPLY (
            SELECT WellCategory = CASE 
                                    WHEN w.LateralStatus = 'NEW' THEN 'New Permits - Not Yet Approved'
                                    WHEN w.LateralStatus = 'APD' THEN 'Permits Approved - Not Yet Commenced'
                                    WHEN w.LateralStatus = 'DRL' THEN 'Drilling Commenced - Not Yet Completed'
                                    WHEN w.LateralStatus = 'OPS' THEN 'Drilling Operations Suspended'
                                    ELSE NULL
                                END
        ) x
    WHERE x.WellCategory IS NOT NULL
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Open Orphan Wells (no known operator)'
    FROM cte_Wells w
    WHERE w.WellStatus IN ('drl','ops','p','s','ta','pai','pii','sai','sii','a','i') AND w.Operator = 101600
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Total Holes Not Yet Completed'
    FROM cte_Wells w
    WHERE w.WellStatus IN ('drl','ops')
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Total Wells Capable of Production'
    FROM cte_Wells w
    WHERE (w.WellStatus IN ('p','s') AND w.WellType <> 'LI') OR w.WellStatus IN ('pai','pii','sai','sii')
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Total Non-Plugged Wells'
    FROM cte_Wells w
    WHERE (w.WellStatus IN ('p','s') AND w.WellType <> 'LI') OR w.WellStatus IN ('drl','ops','ta','pai','pii','sai','sii','a','i')
    -----------------
    UNION ALL
    -----------------
    SELECT w.LeaseType, WellCategory = 'Total Wells Drilled'
    FROM cte_Wells w
    WHERE (w.WellStatus IN ('p','s') AND w.WellType <> 'LI') or w.WellStatus IN ('drl','ops','ta','pai','pii','sai','sii','a','i','pa')
) dt
GROUP BY dt.WellCategory
ORDER BY dt.WellCategory DESC

我已經根據您的示例資料和建議的預期結果檢查了此查詢的結果，它們似乎匹配。所以這會讓你得到你想要的。但是，由于我不知道資料，您需要檢查它是否存在邏輯錯誤和性能。

注意：此查詢的結果不會生成空類別的計數。如果這是您需要的東西，請對此解決方案發表評論，我可以修復它。解決方法是添加一個包含所有類別的表，然后左連接到計數結果。

uj5u.com熱心網友回復：

您可以執行此操作的一種方法是為每個條件生成一行，然后匯總那些回傳匹配項的行，這可以使用表生成器來完成cross apply，該values表生成器可確保case為所有孔評估所有運算式。

目前這種方法假設每口井只有一個WellCategory值。如果可以有多個，那么您將需要自己進行這些更改。我也沒有將輸出包裝在 a 中，pivot因為我認為這通常最好在您的表示層而不是原始 SQL 中完成。

我建議將 SQL 輸出保持在這種格式，因為表示工具在動態處理新類別（例如LeaseType添加新類別）方面比 SQL 好得多，在這種規范化格式中，不需要更改腳本或表示層，除非有任何定制標簽。

如果您想保留旋轉的輸出，您可以簡單地將此查詢包裝在您當前的外層中select：

詢問

select c.LeaseType
      ,cat.WellCategory
      ,count(w.PKey) as Wells
from @HWell as w
    left join @HConstruct as c
        on w.Pkey = c.WellKey
    cross apply(values(case when w.WellStatus = 'p' AND w.WellType = 'gw' OR ((w.WellStatus = 'pai' OR w.WellStatus = 'pii') AND (w.WellType = 'gwi' OR w.WellType = 'ggi' OR w.WellType = 'gwd')) then 'Producing Gas Wells' end)
                     ,(case when w.WellStatus = 'p' AND w.WellType = 'ow' OR ((w.WellStatus = 'pai' OR w.WellStatus = 'pii') AND (w.WellType = 'owi' OR w.WellType = 'ogi' OR w.WellType = 'owd')) then 'Producing Oil Wells' end) -- Need to count DISTINCT WellID's to get to 4770
                     ,(case when w.WellStatus = 's' AND w.WellType = 'ow' OR ((w.WellStatus = 'sai' OR w.WellStatus = 'sii') AND (w.WellType = 'owi' OR w.WellType = 'ogi' OR w.WellType = 'owd')) then 'Shut-In Oil Wells' end)
                     ,(case when w.WellStatus = 's' AND w.WellType = 'gw' OR ((w.WellStatus = 'sai' OR w.WellStatus = 'sii') AND (w.WellType = 'gwi' or w.WellType = 'ggi' or w.WellType = 'gwd')) then 'Shut-In Gas Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'wi' then 'Active Water Injection Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'gi' then 'Active Gas Injection Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'wd' then 'Active Water Disposal Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'gs' then 'Active Gas Storage Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'ws' then 'Active Water Source Wells' end)
                     ,(case when w.WellStatus = 'a' AND w.WellType = 'tw' then 'Active Test Holes' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'wi' then 'Inactive Water Injection Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'gi' then 'Inactive Gas Injection Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'wd' then 'Inactive Water Disposal Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'gs' then 'Inactive Gas Storage Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'ws' then 'Inactive Water Source Wells' end)
                     ,(case when w.WellStatus = 'i' AND w.WellType = 'tw' then 'Inactive Test Holes' end)
                     ,(case when w.WellStatus = 'ta' then 'Temporarily-Abandoned Wells' end)
                     ,(case when w.WellStatus = 'pa' then 'Plugged and Abandoned Wells' end)
                     ,(case when c.LateralStatus = 'NEW' then 'New Permits - Not Yet Approved' end)
                     ,(case when c.LateralStatus = 'APD' then 'Permits Approved - Not Yet Commenced' end)
                     ,(case when c.LateralStatus = 'DRL' then 'Drilling Commenced - Not Yet Completed' end)
                     ,(case when c.LateralStatus = 'OPS' then 'Drilling Operations Suspended' end)
                     ,(case when w.WellStatus IN ('drl','ops','p','s','ta','pai','pii','sai','sii','a','i') AND w.Operator = 101600 then 'Open Orphan Wells (no known operator)' end)
                     ,(case when w.WellStatus IN ('drl','ops') then 'Total Holes Not Yet Completed' end)
                     ,(case when ((w.WellStatus = 'p' or w.WellStatus = 's') AND w.WellType <> 'LI') OR (w.WellStatus = 'pai' OR w.WellStatus = 'pii' OR w.WellStatus = 'sai' OR w.WellStatus = 'sii') then 'Total Wells Capable of Production' end)
                     ,(case when (w.WellStatus = 'drl' OR w.WellStatus = 'ops' OR ((w.WellStatus = 'p' or w.WellStatus = 's') AND w.WellType <> 'LI') OR w.WellStatus = 'ta' OR w.WellStatus = 'pai' OR w.WellStatus = 'pii' OR w.WellStatus = 'sai' OR w.WellStatus = 'sii' OR w.WellStatus = 'a' OR w.WellStatus = 'i') then 'Total Non-Plugged Wells' end)
                     ,(case when (w.WellStatus = 'drl' or w.WellStatus = 'ops' or ((w.WellStatus = 'p' or w.WellStatus = 's') and w.WellType <> 'LI') or w.WellStatus = 'ta' or w.WellStatus = 'pai' or w.WellStatus = 'pii' or w.WellStatus = 'sai' or w.WellStatus = 'sii' or w.WellStatus = 'a' or w.WellStatus = 'i' or w.WellStatus = 'pa') then 'Total Wells Drilled' end)
               ) as cat(WellCategory)
where cat.WellCategory is not null
group by c.LeaseType
        ,cat.WellCategory
order by c.LeaseType
        ,Wells desc;

輸出

標籤：sql sql-server count sql-server-2012 subquery

上一篇：最近7天到當前的原始SELECT（不帶FROM）

下一篇：根據觸發器和子句與SQLServer比較行并計算值