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

Answer 2

您可以使用子查询：

select t1.col1,
  case when t2.cnt > 1 then t1.col2 else 0 end col2
from table1 t1
left join
(
  select count(*) as cnt, col1
  from table1 
  group by col1
) t2
  on t1.col1 = t2.col1

请参阅带有演示的 SQL 小提琴

Answer 3

How how

how （SQL Fiddle）：

SELECT Q.letter, 
    CASE WHEN (SELECT COUNT(*) FROM (query) QQ WHERE QQ.letter = Q.letter) = 1 THEN 0 
         ELSE Q.number
    END AS number
FROM (query) Q

请注意，将"查询"替换为生成第一个结果的查询。

Answer 4

可以使用 UNION ALL 2 个单独的语句（一个用于重复的字母，一个用于只出现一次的字母）来执行此操作：

    SELECT letter, number
    FROM tableName
    WHERE letter IN (
        SELECT letter
        FROM tableName
        GROUP BY letter
        HAVING COUNT(1) > 1
    )
UNION ALL
    SELECT letter, 0
    FROM tableName
    WHERE letter IN (
        SELECT letter
        FROM tableName
        GROUP BY letter
        HAVING COUNT(1) = 1
    )