我的表包含 3 個主要屬性：每個 NUMBER_ID 的 STAGING_ID、DEVELOPMENT_ID 和 PRODUCTION_ID。我想根據這個表查詢一個表，上面寫著：
IF there are NO null values in any of the three columns, select STAGING_ID in 'MAIN_ID' column and 'LEVEL_DESCRIPTION' to be STAGING
IF there is a NULL value in only STAGING_ID (not DEVELOPMENT_ID or PRODUCTION_ID), then select the next level, which would be DEVELOPMENT_ID in 'MAIN_ID' column, and 'LEVEL_DESCRIPTION' to be DEVELOPMENT
IF there is a NULL value in STAGING & DEVELOPMENT (not PRODUCT_ID), then select the next level, which would be PRODUCT_ID in 'MAIN_ID' column, and 'LEVEL_DESCRIPTION' to be PRODUCTION
所以級別總是遵循這個層次結構，分期 > 開發 > 生產。
So in the picture,
FIRST ROW, should be: LEVEL_DESCRIPTION: STAGING, MAIN_ID: 3532
SECOND ROW, should be: LEVEL_DESCRIPTION: DEVELOPMENT, MAIN_ID: 3532
THIRD ROW, should be: LEVEL_DESCRIPTION: PRODUCTION, MAIN_ID: 1081
因此，當我基于此表進行查詢時，我需要 3 列：
NUMBER_ID、MAIN_ID、LEVEL_DESCRIPTION
我能做到嗎？我正在考慮一個案例陳述，但我無法制定這個。
DROP TABLE TBL_1;
CREATE TABLE TBL_1
(
number_id int,
production_id int,
develpoment_id int,
staging_id int
);
INSERT INTO TBL_1
values
(11111,2331, 1233, 3532),
(11122,9080, 1291, null),
(11144,1082, null, null)
uj5u.com熱心網友回復：
使用 COALESCE 和 CASE：
SELECT *,
COALESCE(staging_id, development_id, production_id, 'Not found') AS main_id,
CASE WHEN staging_id IS NOT NULL THEN 'Staging'
WHEN development_id IS NOT NULL THEN 'Development'
WHEN production_id IS NOT NULL THEN 'Production'
ELSE 'Not found'
END AS Level_description
FROM TBL_1;
db<>小提琴演示
轉載請註明出處，本文鏈接：https://www.uj5u.com/ruanti/421785.html
標籤：
上一篇：新課程開始時的標簽
下一篇：同時更新和洗掉Postgres