問題描述

我正在努力讓我的 SQL 查詢工作.當我的 IN 找不到一行時，我想像圖片中的那樣返回一行，但我希望將 ifuserholdscertificate 設置為NO".

I'm struggling to get my SQL query to work. When my IN FAILS to find a row I want to return a row just like the ones in the picture but I want the ifuserholdscertificate to be set to 'NO'.

SELECT tuc.id,
       tu.firstName,
       tuc.uid,
       tuc.value,
       tc.name,
       count(tuc.value) over (PARTITION BY tuc.uid) AS 'amount',
       'certificate DESIRABLE' AS 'typeofthing' ,
       'YES' AS 'HasorNot',
       ifuserholdscertificate = CASE
                                    WHEN count(tuc.value) = 0 THEN 'NO'
                                    ELSE 'YES'
                                END
FROM t_user_certificates tuc,
     t_certificates tc,
     t_users tu
WHERE tuc.value IN (4,
                    12,
                    31)
  AND tuc.value = tc.id
  AND tu.id = tuc.uid
GROUP BY tuc.id,
         tu.firstName,
         tuc.uid,
         tuc.value,
         tc.name

這是查詢生成的數據！

正如您所看到的，即使有些人在數量行中只得到 2，它仍然不會獲取一行并將 ifuserholdscertificate 設置為NO".

As you can see even if some people only get 2 in the amount row it will still not fetch a row and set ifuserholdscertificate to 'NO'.

按請求更新！

select  tuc.id,
count(tuc.value) as 'counten',
tu.firstName,
tuc.uid,
tuc.value, 
tc.name, 
count(tuc.value) over (PARTITION BY tuc.uid) as 'amount',
'certificateDESIRABLE'  as 'typeofthing' ,
'YES' as 'HasorNot',
HasOrders = CASE
                WHEN count(tuc.value) = 0 THEN 'NO'
                ELSE 'YES'
            END
from t_user_certificates tuc                   
left outer join t_certificates tc
on tuc.value = tc.id
left outer join t_users tu
on tu.id = tuc.uid
GROUP BY tuc.id, tu.firstName, tuc.uid, tuc.value, tc.name

在計數中總是 1 并且總是是"

Alwyas one 1 in the count and always 'YES'

推薦答案

這可能更符合您的要求.你總是返回 1，沒有用戶沒有證書，因為你在 where 子句中有 IN 位.因此，即使使用外連接，您也只能從 t_user_certificates 返回具有這些值的行.它實際上變成了一個內部連接.ANSI 連接語法是您的朋友.它將 JOIN 邏輯與過濾器分開.

This might be more like what you're looking for. You're always returning 1, and no users w/o certificates because you have the IN bit in the where clause. So even with an outer join, you only return rows from t_user_certificates with those values. It effectively becomes an inner join. ANSI join syntax is your friend. It separates JOIN logic from filters.

SELECT tuc.id,
       tu.firstName,
       tuc.uid,
       tuc.value,
       tc.name,
       count(tuc.value) AS 'amount',
       'certificate DESIRABLE' AS 'typeofthing' ,
       'YES' AS 'HasorNot',
       ifuserholdscertificate = CASE
                                    WHEN count(tuc.value) > 0 THEN 'YES'
                                    ELSE 'NO'
                                END
FROM
    t_users tu
LEFT JOIN t_user_certificates tuc
    ON
    tu.id = tuc.uid
    AND 
    tuc.value IN
    (
        4
        , 12
        , 31
    )
LEFT JOIN
     t_certificates tc
    ON
    tuc.value = tc.id

GROUP BY tuc.id,
         tu.firstName,
         tuc.uid,
         tuc.value,
         tc.name;

這篇關于T-sql case 返回錯誤值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！