SQL:从基于 2 个字段的选择中删除重复值 [英] SQL: remove duplicate values from a select based on 2 fields

问题描述

我有这个简单的表(省略了主 ID 字段):

I have this simple table (omitting primary id field):

value - language - externID
1       default      10
1       english      10

2       english      10

3       default      10

我需要一个 SQL 来选择每个包含语言 IN 的值 ('default','english') 但是如果相同的值同时具有 default 和 english，则 sql 应该丢弃defaul"记录并仅返回英语一个.

I need a SQL that select every values that got language IN ('default','english') But in the case the same value got both default and english the sql should discard the "defaul" record and return only the english one.

示例:SELECT * FROM tbl WHERE externID = 10 AND language IN ('default','english') AND "如果有与默认值相同的记录，则首选英语"

选择的结果:

1 - english - 10
2 - english - 10
3 - default - 10

有什么办法吗?谢谢

这就像做一个 GROUP BY 值但是有英语的偏好.

It's like doing a GROUP BY value But with the preference of the english language.

ps.英语"是我需要的一个例子我可以选择不同的特定语言

ps. "english" is an example I need I can choose the specific language between vary

推荐答案

对于这个特定问题 max(language) 应该有效，因为 d 小于 e.

For this particular question max(language) should work because d is less than e.

编辑(不会破坏的解决方案):

EDIT (Sollution that won't break):

添加max(case language when 'default' then 0 else 1 end)

Edit2(更好):

添加 max(case language when 'english' then 1 else 0 end) 这样，如果向 in() 部分

Add max(case language when 'english' then 1 else 0 end) so you won't even get diplicate values if you add more languages to your in() section

编辑编号.2:

正如我所承诺的，我现在有了一个全新的星期一早晨，并得到了满足您需求的正确查询:-)

As i promised, here i am with a fresh new mondaymorning mind and got the right query for your needs :-)

试试

SELECT value,
       CASE group_concat(language)
         WHEN 'default' then 'default'
         ELSE LTRIM(REPLACE(group_concat(language SEPARATOR ' '),'default',''))
       END as language,
       externID
FROM demo
WHERE externID = 10
AND   language in ('default','english')
GROUP BY value;

说明:GROUP_CONCAT 和 LTRIM

这篇关于SQL:从基于 2 个字段的选择中删除重复值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！