MySQL 案例语句中布尔“真”的总和

stefanos_1992 发表于 Dev

stefanos_1992

我想问你我是否可以计算案例陈述中的“真实”。

t_1 As (
SELECT *,
-- Spot Guide not to be Empty 
CASE 
    WHEN (total_spots_number <> 0 AND (spot1_availability IS NULL AND spot2_availability IS NULL AND spot3_availability IS NULL)) THEN 'cannot be empty'
    WHEN (total_spots_number = 1 AND SUM(spot1_availability IS NOT NULL AND spot2_availability IS NOT NULL AND spot3_availability IS NOT NULL) <> 1) THEN 'one spot is incomplete'
    WHEN (total_spots_number = 2 AND SUM(spot1_availability IS NOT NULL AND spot2_availability IS NOT NULL AND spot3_availability IS NOT NULL) <> 2) THEN 'two spots are incomplete'
    WHEN (total_spots_number = 3 AND SUM(spot1_availability IS NOT NULL AND spot2_availability IS NOT NULL AND spot3_availability IS NOT NULL) <> 3) THEN 'three spots are incomplete'
    ELSE 'nothing to check'
END AS QA_Check_1
FROM Main_Data
)
SELECT * FROM t_1

巴尔马尔

条件表达式的值是1如果它是真的，所以你可以简单地添加它们。

WITH availability AS (
    SELECT (spot1_availability IS NOT NULL) + (spot2_availability IS NOT NULL) + (spot3_availability IS NOT NULL) AS availability_count
    FROM Main_Data
)

本文收集自互联网，转载请注明来源。

如有侵权，请联系 [email protected] 删除。