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

Question

我想知道是否有更好的方式来写 SELECT下面查询中的子句。 status 有三种可能的情况.它的值可以是 'A' 或 'N' 或 null。

我认为放置AND status = 'A'当您将子表值与“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
)