sql - SQL:修改具有唯一值的查询结果行

Question

我有一个sqlite查询，返回带有列[字母，数字]的以下内容：

("a", 1) 
("a", 2)
("b", 3)
("c", 3)

如果字母是不同的，我想将数字列检索为0。怎么做？

预期产量：

("a", 1) 
("a", 2)
("b", 0)
("c", 0)

Answer 1

SELECT tba.mychar
-- if tbu.mychar is null, then the letter is not unique
--   when it happens, the letter is not "unique" thus use the number column
--   else use zero for "unique" letters
, CASE WHEN tbu.mychar IS NULL THEN tba.mynum ELSE 0 END AS newnum
FROM mytab tba
LEFT JOIN (
    -- this subquery only returns the letters that don't repeat
    SELECT mychar
    FROM mytab
    GROUP BY mychar
    HAVING COUNT(*) = 1
) AS tbu ON tba.mychar=tbu.mychar