T-sql case 返回错误值

2021-09-10 00:00:00 sql tsql sql-server

我正在努力让我的 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;

相关文章