GROUP BY具有基于另一个FIELD的首选项 [英] GROUP BY with preferences based on another FIELD

问题描述

是否有办法进行分组，但是赋予在字段中具有一定价值的记录更大的重要性?

Is there a way to do a group by but givinig more importance to record that got a certain value in a field?

示例:

value - language - externID
1       default      10
1       english      10

2       english      10

3       default      10

SELECT的结果:

Result of the SELECT:

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

IE:当存在具有相同值"的记录时，更喜欢具有"english"的记录

IE: when there is the record with same "value" prefer the record with "english"

示例:SELECT * FROM tbl，其中externID = 10并且语言为IN(默认"，英语")GROUP BY值"PREFER lang = english"

example: SELECT * FROM tbl WHERE externID = 10 AND language IN ('default','english') GROUP BY value "PREFER lang=english"

ps.我需要一个英语"示例，我可以选择不同语言之间的特定语言

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

推荐答案

在另一个问题上，Yes123添加了一些额外的内容...

On the other question, Yes123 has added a bit extra...

如果首选的语言是中文，并且使用默认值和英语来关联一个值，则该值应不返回任何结果...

If the prefered language is Chinese, and a value is asscoiated with default and english, that value should have no results returned...

我理解这意味着...

I understand it to mean this...

• 如果该组包含搜索到的语言，请返回该语言
• 如果该组不包含该语言，而仅包含默认语言，则返回default
• 如果该组不包含该语言，但包含非默认语言，则不返回任何内容

DECLARE
  @preferred VARCHAR(64)
SET
  @preferred = 'English'

SELECT
  value,
  (
    SELECT
      language
    FROM
      tbl [search]
    WHERE
      externID  = 10
      AND value = tbl.value
      AND language IN ('default', @preferred)
    ORDER BY
      NULLIF(language, 'default') DESC
    LIMIT
      1
  )
FROM
  tbl
WHERE
  externID = 10
GROUP BY
  value
HAVING
  COUNT(*) = SUM(CASE WHEN language IN ('default', @preferred) THEN 1 ELSE 0 END)

HAVING子句只是指出该值的每个记录都必须是默认"或您要搜索的语言.

The HAVING clause just states that every record for that value must either be 'default' or the language you're searching for.

子查询中的ORDER BY和LIMIT 1始终强制最后选择默认".这意味着，如果找到了首选语言，就会以首选方式显示出来.

The ORDER BY and LIMIT 1 in the sub query forces 'default' to be chosen last, always. Meaning that if the preferred language is found, that get shown in preferrence.

这篇关于GROUP BY具有基于另一个FIELD的首选项的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！