哈哈,刚才钻死胡同里了,多谢正确应该是:SELECT 0 FROM TB WHERE orderNO='orderNO'AND isLock=1 AND clientID<>'clientID'
WITH table1(orderNO,isLock,clientID) AS ( SELECT 100000001,0,100 UNION ALL SELECT 100000002,1,100 UNION ALL SELECT 100000003,0,100 UNION ALL SELECT 100000005,1,200 ) ,query(orderNO) AS ( SELECT 100000004 UNION ALL SELECT 100000001 UNION ALL SELECT 100000002 UNION ALL SELECT 100000005 ) SELECT q.orderNO, CASE WHEN ISNULL(t.isLock,0) = 0 THEN 'true/1' WHEN (t.isLock = 1) AND (clientID = 100) THEN 'true/1' ELSE 'flase/0' END AS result FROM query q LEFT JOIN table1 t ON q.orderNO = T.orderNO orderNO result ----------- ------- 100000004 true/1 100000001 true/1 100000002 true/1 100000005 flase/0
SELECT 100000001,0,100 UNION ALL
SELECT 100000002,1,100 UNION ALL
SELECT 100000003,0,100 UNION ALL
SELECT 100000005,1,200
)
,query(orderNO) AS (
SELECT 100000004 UNION ALL
SELECT 100000001 UNION ALL
SELECT 100000002 UNION ALL
SELECT 100000005
)
SELECT q.orderNO,
CASE WHEN ISNULL(t.isLock,0) = 0 THEN
'true/1'
WHEN (t.isLock = 1) AND (clientID = 100) THEN
'true/1'
ELSE
'flase/0'
END AS result
FROM query q
LEFT JOIN table1 t
ON q.orderNO = T.orderNO
orderNO result
----------- -------
100000004 true/1
100000001 true/1
100000002 true/1
100000005 flase/0