我有
- 表 a 與列 lot 和 tid
- 表 b 與列 lot 和 id
- 具有列 id 和標志的表 t
我的查詢之一將變數設定@ID為以下查詢，其標志 = 'Y'
第二個查詢將變數設定@ID_Not_Flag為以下查詢，其標志 <> 'Y'
我可以在 1 個查詢中執行以下查詢（設定 @ID 和 @ID_Not_Flag）嗎？
SELECT @ID = a.tid
FROM a, b, t
WHERE b.lot = a.lot and b.id =123 and t.id = a.tid and flag = 'Y'
SELECT @ID_Not_Flag = a.tid
FROM a, b, t
WHERE b.lot = a.lot and b.id =123 and t.id = a.tid and flag <> 'Y'
Table a
lot tid
100 1
100 2
Table b
lot id
100 123
Table t
id flag
1 Y
2 N
1 個查詢的期望結果：@ID = 1，@ID_Not_Flag = 2
以下查詢結果為@ID = 1, @ID_Not_Flag = NULL
SELECT
@ID = (CASE WHEN flag = 'Y' THEN a.tid END),
@ID_Not_Flag = (CASE WHEN flag <> 'Y' THEN a.tid END)
FROM a, b, t
WHERE b.lot = a.lot and b.id =123 and t.id = a.tid
SELECT @ID,@ID_Not_Flag
結果（不正確）：
ID ID_Not_Flag
1 NULL
uj5u.com熱心網友回復：
您可以嘗試使用資料透視表。查詢的第一部分只是創建臨時表來保存資料。
/*CREATE TEMP TABLES*/
DECLARE @a TABLE ( lot int, tid int)
DECLARE @b TABLE ( lot int, id int)
DECLARE @t TABLE ( id int, flag VARCHAR(1))
INSERT INTO @a (lot, tid) VALUES (100, 1) , (100, 2)
INSERT INTO @b (lot, id) VALUES (100, 123)
INSERT INTO @t (id, flag) VALUES (1, 'Y') , (2, 'N')
/*QUERY*/
SELECT [Y] [@id] ,
[N] [@id_not_flag]
FROM (
SELECT a.lot, a.tid, b.id , t.flag
--,ID = (CASE WHEN flag = 'Y' THEN a.tid END)
--,ID_Not_Flag = (CASE WHEN flag <> 'Y' THEN a.tid END)
FROM @a a
LEFT JOIN @b b
ON a.lot = b.lot
LEFT JOIN @t t
ON t.id = a.tid) AS Src
PIVOT (max(Src.tid) FOR flag in ([Y] , [N])) Pvt
uj5u.com熱心網友回復：
您可以只使用另一個連接和 COALESCE 示例回傳 1 2
DECLARE @a Table(lot INT,tid INT);
DECLARE @b Table(lot int ,id INT);
DECLARE @t Table(id INT,flag CHAR);
INSERT INTO @a(lot, tid)
values
(100, 1),
(100, 2);
INSERT INTO @b(lot, id)
values
(100, 123)
INSERT INTO @t(id, flag)
values
(1, 'Y'),
(2, 'N');
--SELECT * FROM @a;
--SELECT * FROM @b;
--SELECT * FROM @t;
DECLARE
@ID INT,
@ID_Not_Flag INT;
SELECT
@ID = COALESCE(ty.id,@ID),
@ID_Not_Flag = COALESCE(tn.id, @ID_Not_Flag )
FROM @b AS b
INNER JOIN @a AS a ON b.lot = a.lot
LEFT OUTER JOIN @t AS ty ON ty.id = a.tid AND ty.flag = 'Y'
LEFT OUTER JOIN @t AS tn ON tn.id = a.tid AND tn.flag = 'N'
WHERE b.id = 123;
SELECT @ID , @ID_Not_Flag ;
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/446840.html