在 VIEW 中编写此特定 SELECT 查询的更好方法

Question

我想知道是否有更好的方法在下面的查询中编写 SELECT 子句。 status 存在三种可能的情况。它的值可以是“A”或“N”或为空。

我认为，当您将子表值与“A”值进行比较时，将 AND status = 'A' 放在 where 子句中是多余的。有什么方法可以更有效地重写它吗？我觉得限制 WHERE 子句中的状态总是会给你一个“A”，因此执行 CASE 语句是毫无意义的。

ALTER VIEW dbo.st_review_status_vw AS
(
    SELECT c.st_id, c.ms_price_comp_fy, 
        CASE
            WHEN (SELECT status FROM 
                    (SELECT st_id, status, ms_price_comp_fy
                     FROM ms_price_comp
                     WHERE st_id = c.st_id 
                         AND ms_price_comp_fy = c.ms_price_comp_fy 
                         AND status = 'A'
                     GROUP BY st_id, status, ms_price_comp_fy)
                 AS subTable) = 'A' THEN 'C'
            ELSE 'I'
        END AS status,
        MAX(date_approved) AS date_completed
    FROM ms_price_comp AS c
    GROUP BY c.st_id, c.ms_price_comp_fy
)

Answer 1

试试这个

ALTER VIEW dbo.st_review_status_vw AS (
SELECT c.st_id, c.ms_price_comp_fy, 

    CASE WHEN MIN(status)  = 'A' THEN 'C'
    ELSE 'I'
    END AS status,

MAX(date_approved) AS date_completed
FROM ms_price_comp AS c
GROUP BY c.st_id, c.ms_price_comp_fy
)

Answer 2

这对我来说似乎是一样的

ALTER VIEW dbo.st_review_status_vw AS (
SELECT
        c.st_id
    ,   c.ms_price_comp_fy
    ,   CASE
            WHEN MIN(status) = 'A' THEN 'C'
            ELSE 'I'
        END AS status
    ,   MAX(date_approved) AS date_completed
FROM ms_price_comp AS c
GROUP BY c.st_id, c.ms_price_comp_fy
)