我有一個查詢 表 table就一個欄位value
value
A
B
null
B
如果Value里面有A記錄，變數就是A，如果沒有A，只有B和null，變數就是B，如果A和B都沒有就是null，
想知道一句SQL能實作嗎？
uj5u.com熱心網友回復：
SELECT TOP 1 Result FROM
(
SELECT 'A' AS Result, 1 AS Seq
FROM test
WHERE EXISTS ( SELECT * FROM test where [value] = 'A')
UNION
SELECT 'B' AS Result, 2 AS Seq
FROM test
WHERE ( EXISTS ( SELECT * FROM test where [value] = 'B' ) )
AND ( EXISTS ( SELECT * FROM test where [value] IS NULL ) )
UNION
SELECT NULL AS Result, 3 AS Seq
FROM test
WHERE ( NOT EXISTS ( SELECT * FROM test where [value] = 'A' ) )
AND ( NOT EXISTS ( SELECT * FROM test where [value] = 'B' ) )
UNION
SELECT 'No record' AS Result, 4 AS Seq
) tb
ORDER BY Seq
感覺你的邏輯設計有點問題，如果需要調整的話更改相應的篩選條件SQL 就好。
uj5u.com熱心網友回復：
case when
uj5u.com熱心網友回復：
DECLARE @t TABLE(value VARCHAR(10)null)
INSERT @t(value)
VALUES('A'),('B'),(NULL),('B')
SELECT CASE WHEN EXISTS (SELECT 1 FROM @t WHERE value='https://bbs.csdn.net/topics/A') THEN 'A' WHEN EXISTS (SELECT 1 FROM @t WHERE value='https://bbs.csdn.net/topics/B') then 'B' ELSE NULL END
DELETE FROM @t
INSERT @t(value)
VALUES(NULL),('B'),(NULL),('B')
SELECT CASE WHEN EXISTS (SELECT 1 FROM @t WHERE value='https://bbs.csdn.net/topics/A') THEN 'A' WHEN EXISTS (SELECT 1 FROM @t WHERE value='https://bbs.csdn.net/topics/B') then 'B' ELSE NULL END
DELETE FROM @t
INSERT @t(value)
VALUES(NULL),('X'),(NULL),('D')
SELECT CASE WHEN EXISTS (SELECT 1 FROM @t WHERE value='https://bbs.csdn.net/topics/A') THEN 'A' WHEN EXISTS (SELECT 1 FROM @t WHERE value='https://bbs.csdn.net/topics/B') then 'B' ELSE NULL END
uj5u.com熱心網友回復：
IF OBJECT_ID(N'TEMPDB.DBO.#T') IS NOT NULL
DROP TABLE #T
GO
CREATE TABLE #T
(ID VARCHAR(10))
INSERT INTO #T
SELECT 'A' UNION ALL
SELECT 'B' UNION ALL
SELECT NULL UNION ALL
SELECT 'B'
GO
SELECT MIN(ID) FROM #T
uj5u.com熱心網友回復：
with tb as(
select 'A' as value union
select 'B' as value union
select NULL as value
),cte as(select *,case value when 'A' then 1 when 'B' then 2 else 3 end as level from tb )
select top 1 value from cte order by level
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/31264.html
標籤：基礎類
上一篇：vue