首页 文章

MYSQL查询获取所有结果，其中一列在不同的组中发生多次并匹配某些条件

提问于
浏览 次
1

假设我有以下数据：

object    size    color    units
------    ----    -----    -----
ball      small   red      3
ball      small   red      2
ball      medium  blue     2
ball      medium  blue     1
ball      big     yellow   2
hat       big     green    3
hat       big     green    4
umbrella  medium  blue     1
umbrella  medium  blue     4
umbrella  big     blue     4
umbrella  huge    red      2
umbrella  huge    green    1
book      small   white    4
book      small   brown    3
book      medium  brown    2

我需要一个查询，返回所有对象的所有对象颜色大小变化，其中至少有一个变体的大小和颜色的总单位至少为5，因此查询将返回这些行：

object    size    color    total_units
------    ----    -----    ------
ball      small   red      5
ball      medium  blue     3
ball      big     yellow   2
umbrella  medium  blue     5
umbrella  big     blue     4
umbrella  huge    red      2
umbrella  huge    green    1

所有球及其总尺寸颜色变化的总和出现在结果中的原因是因为存在至少2个不同的球并且其中至少一个的总单位是5或更多

所有遮阳伞及其总尺寸颜色变化出现在结果中的原因是因为至少有两个不同的遮阳伞，其中至少一个的总单位是5或更多

帽子没有出现在结果中的原因是因为虽然有超过5个单位（7）的大绿帽，但至少有2个不同的帽子

之所以没有这些书出现在结果中是因为即使有3本不同的书，也没有至少5个单位 .

感谢您向我展示如何实现这一目标！

mysql sql mariadb

2 回答

  • 1

    • 在派生表中，我们可以识别具有至少1个组合的唯一 Object 值，以及其中一个组合至少总共5个单位 .

    • 加入主表只获取那些行（post GROUP BY ） .

    Query

    SELECT 
  t.object, t.size, t.color, SUM(t.units) AS total_units 
FROM 
your_table AS t
JOIN 
(
  SELECT dt.object 
  FROM 
  (
    SELECT
      object, size, color, SUM(units) AS total_units 
    FROM your_table 
    GROUP BY 
      object, size, color
  ) AS dt 
  GROUP BY dt.object 
  HAVING COUNT(*) > 1 AND 
         SUM(dt.total_units >= 5) /* atleast one with 5 units */
) AS dt2 
  ON dt2.object = t.object
GROUP BY t.object, t.size, t.color;

    Result

    | object   | size   | color  | total_units |
| -------- | ------ | ------ | ----------- |
| ball     | big    | yellow | 2           |
| ball     | medium | blue   | 3           |
| ball     | small  | red    | 5           |
| umbrella | big    | blue   | 4           |
| umbrella | huge   | green  | 1           |
| umbrella | huge   | red    | 2           |
| umbrella | medium | blue   | 5           |

    View on DB Fiddle

    回复于
  • 1

    使用窗口函数（在最新版本的MySQL和MariaDB数据库中），这很容易：

    select object, size, color, total_units
from (select object, size, color, sum(units) as total_units,
             max(sum(units)) over (partition by object) as max_total_units,
             count(*) over (partition by object) as cnt
      from t
      group by object, size, color
     ) t
where max_total_units >= 5 and cnt > 1;
    回复于

相关问题