一个SQL的逻辑判断问题

[inghot]

一个表数据如下：
orderNO isLock clientID
100000001 0 100
100000002 1 100
100000003 0 100

我现在要判断一个订单号是否存在里面，如果存在的话，是否isLock为1，这个SQL语句该怎么写？
如100000004 它不存在，返回true/1
100000001 它存在但是isLock为0，返回true/1
100000002 它存在，并且isLock为1，clientID为100,返回true/1；否则返回false/0

[还在加载中灬]

SELECT 0 FROM TB WHERE orderNO='orderNO'AND isLock=1 AND clientID='clientID'

这个查询没有结果就是true/1，有结果就是false/0

[發糞塗牆]

三个if判断

[inghot]

哈哈，刚才钻死胡同里了，多谢 正确应该是：SELECT 0 FROM TB WHERE orderNO='orderNO'AND isLock=1 AND clientID<>'clientID'

[Tiger_Zhao]

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