根据辅助列中的值选择唯一的行 [英] Select unique rows, according to value in a secondary column

问题描述

我有一个简单的mysql表：
$ b

I have a simple mysql table:

id, code, product_name, supplier
 1, 3844, ProductName1, Supplier1
 2, 3844, ProductName1, Supplier2
 3, 1233, ProductName2, Supplier2

我想从表中查询所有不同的产品。所以sql代码是 select * from table group by code 。

I want to query all distinct products from table. So sql code is select * from table group by code.

我的问题：

我想在group by语句中设置供应商优先级（例如，供应商1是最相关的，但对于给定的代码可以不在列表中）。产品＃1和＃2是相同的。在我的结果表中，我想看到：

I want to set supplier priority in "group by" statement (e.g. Supplier1 is most relevant, but can be absent in list for a given code). Products #1 and #2 are the same. In my resulting table I want to see:

id, code, product_name, supplier
 1, 3844, ProductName1, Supplier1
 3, 1233, ProductName2, Supplier2

我完成了这项工作？

How can I accomplish this?

推荐答案

您可能需要：

You probably need this:

SELECT
  MIN(id) id,
  code,
  MIN(product_name) product_name,
  MIN(supplier) supplier
FROM
  yourtable
WHERE
  (code, CASE WHEN supplier='Supplier1' THEN '' ELSE supplier END)
  IN (SELECT   code, min(CASE WHEN supplier='Supplier1' THEN '' ELSE supplier END)
      FROM     yourtable
      GROUP BY code)
GROUP BY
  code
ORDER BY
  min(id);

请参阅小提琴 here 。

这篇关于根据辅助列中的值选择唯一的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！