I have a query table table is a field value

The value
A
B
Null
B

If there is A record, the Value is variable A, if there is no A, B and null, only variables are B, if A and B is null,

Do you want to know a SQL to achieve?

CodePudding user response:

 
SELECT the TOP 1 Result FROM 
(
SELECT the 'A' AS A Result, 1 AS Seq 
FROM the test 
WHERE the EXISTS (SELECT * FROM test WHERE [value]='A') 

The UNION 

SELECT 'B' AS a Result, 2 AS Seq 
FROM the test 
The WHERE (EXISTS (SELECT * FROM test WHERE [value]='B')) 
AND (EXISTS (SELECT * FROM test where/value IS NULL)) 

The UNION 

SELECT the NULL AS the Result, 3 AS Seq 
FROM the test 
The WHERE (NOT the EXISTS (SELECT * FROM test WHERE [value]='A')) 
AND (NOT the EXISTS (SELECT * FROM test where [value]='B')) 

The UNION 

SELECT 'No record' AS a Result, 4 AS Seq 
) TB 
The ORDER BY Seq 

Feel your logic design is a little problem, if need to adjust the changes corresponding filter SQL is good,

CodePudding user response:

In case the when

CodePudding user response:

 DECLARE @ t TABLE (value VARCHAR (10) null) 
INSERT the @ t (value) 
VALUES (' A '), (" B "), (NULL), (" B ") 
SELECT the CASE WHEN the EXISTS (SELECT 1 FROM @ t WHERE value='https://bbs.csdn.net/topics/A') THEN the 'A' WHEN the EXISTS (SELECT 1 FROM @ t WHERE value='https://bbs.csdn.net/topics/B') THEN 'B' ELSE NULL END 
The DELETE FROM @ t 
INSERT the @ t (value) 
VALUES (NULL), (" B "), (NULL), (" B ") 
SELECT the CASE WHEN the EXISTS (SELECT 1 FROM @ t WHERE value='https://bbs.csdn.net/topics/A') THEN the 'A' WHEN the EXISTS (SELECT 1 FROM @ t WHERE value='https://bbs.csdn.net/topics/B') THEN 'B' ELSE NULL END 
The DELETE FROM @ t 
INSERT the @ t (value) 
VALUES (NULL), (' X '), (NULL), (' D ') 
SELECT the CASE WHEN the EXISTS (SELECT 1 FROM @ t WHERE value='https://bbs.csdn.net/topics/A') THEN the 'A' WHEN the EXISTS (SELECT 1 FROM @ t WHERE value='https://bbs.csdn.net/topics/B') THEN 'B' ELSE NULL END 

CodePudding user response:

 
IF OBJECT_ID (N 'TEMPDB for. DBO. # T') IS NOT NULL 
DROP TABLE # T 
GO 

The CREATE TABLE # T 
(ID VARCHAR (10)) 

INSERT INTO # T 
SELECT the 'A' UNION ALL 
SELECT 'B' UNION ALL 
SELECT the NULL UNION ALL 
SELECT 'B' 
GO 

The SELECT MIN (ID) FROM # T 

CodePudding user response:

 with TB as (
Select the 'A' as the value union 
Select 'B' as the value union 
Select the NULL as the value 
), the cte as (select *, case value when 'A' then 1 when the 'B' then 2 else end as level 3 from TB) 
Select the top 1 value from cte order by level