我有一個這樣的查詢：
select * from my_table;
結果如下：
name x1 x2 x3
---- ---- --- ---
data -2 3 -1
我想更改查詢以將正負輸出分成單獨的行，結果更改為：
name x1 x2 x3
---- ---- --- ---
data -2 3 -1
data -2 null -1
data null 3 null
uj5u.com熱心網友回復：
如果你的查詢比較復雜，你可以把它放到子查詢factoring( WITH)子句中，然后使用UNION ALLandCASE運算式：
WITH your_query (name, x1, x2, x3) AS (
select * from my_table
)
SELECT *
FROM your_query
UNION ALL
SELECT name,
CASE WHEN x1 < 0 THEN x1 END,
CASE WHEN x2 < 0 THEN x2 END,
CASE WHEN x3 < 0 THEN x3 END
FROM your_query
UNION ALL
SELECT name,
CASE WHEN x1 >= 0 THEN x1 END,
CASE WHEN x2 >= 0 THEN x2 END,
CASE WHEN x3 >= 0 THEN x3 END
FROM your_query;
uj5u.com熱心網友回復：
SELECT *
FROM my_Table
SELECT name,
CASE WHEN x1 < 0 THEN x1 else Null END,
CASE WHEN x2 < 0 THEN x2 else Null END,
CASE WHEN x3 < 0 THEN x3 else Null END
FROM my_Table
UNION ALL
SELECT name,
CASE WHEN x1 >= 0 THEN x1 else Null END,
CASE WHEN x2 >= 0 THEN x2 else Null END,
CASE WHEN x3 >= 0 THEN x3 else Null END
FROM my_Table;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qiye/369299.html