mysql - 通过在sql中对多个列进行分组来查找列中的最小值

Question

+----+-------+-----------------------------+-------------------------+-------------------+-------------+
| Id | grade | shot_name                   | submitted_by_supervisor | version_submitted | submit_type |
+----+-------+-----------------------------+-------------------------+-------------------+-------------+
| 27 | A     | elx_reel01_scn1020_shot1720 | Salil Devji             | 33                | Fresh       |
| 27 | A     | elx_reel01_scn1020_shot1720 | Deepali                 | 34                | Fresh       |
| 37 | A     | elx_reel01_scn1030_shot3480 | Salil Devji             | 15                | Fresh       |
| 37 | A     | elx_reel01_scn1030_shot3480 | Salil Devji             | 20                | Fresh       |
| 7  | B     | elx_reel01_scn1010_shot1030 | Darshan                 | 4                 | Fresh       |
| 7  | B     | elx_reel01_scn1010_shot1030 | Varion                  | 6                 | Fresh       |
| 17 | B     | elx_reel01_scn1010_shot1140 | Varion                  | 17                | Fresh       |
| 17 | B     | elx_reel01_scn1010_shot1140 | Varion                  | 14                | Fresh       |
+----+-------+-----------------------------+-------------------------+-------------------+-------------+

我有列(submit_type)插入新列，该值由 CASE 条件设置这是我的 sql 查询:

SELECT   s.shot_id,
         s.shot_name ,
         isn.reviewer AS 'submitted_by_supervisor',
         isn.version  AS 'version_submitted',
         isn.grade    AS 'grade',
         CASE
                  WHEN isn.version = Min(isn.version) THEN 'FRESH'
                  ELSE 'Once Submitted'
         end AS 'submit_type'
FROM     viewd_elx.india_supe_note isn
JOIN     viewd_elx.shot s
ON       s.shot_id = isn.shot_id
JOIN     viewd_elx.team t
ON       isn.shot_id = t.shot_id
JOIN     viewd_elx.viewd_team vt
ON       isn.shot_id = vt.shot_id
WHERE    isn.promoted='Yes'
AND      isn.grade IN ('A',
                       'B')
GROUP BY isn.grade,
         s.shot_id,
         isn.version; 

提到的查询在(submit_type)列的所有字段中给出“新鲜”值。这是不正确的。

我真正需要的是，我正在对列进行分组并找到列的 MIN(值)，

工作流程:-A 级 => 组 (shot_name) => min(version_submited) => 在 (submit_type) 中设置“新鲜”，否则“提交后”

在“A”级具有相同的 shot_name，将此(shot_name)分组，然后查找 min(version_submited)，如果找到最小值，则设置“新鲜”，否则在(submit_type)列中设置“一旦提交”值。也适用于 B 级，如 A 级。

我需要这样的结果=>

+----+-------+-----------------------------+-------------------------+-------------------+----------------+
| Id | grade | shot_name                   | submitted_by_supervisor | version_submitted | submit_type    |
+----+-------+-----------------------------+-------------------------+-------------------+----------------+
| 27 | A     | elx_reel01_scn1020_shot1720 | Salil Devji             | 33                | Fresh          |
| 27 | A     | elx_reel01_scn1020_shot1720 | Deepali                 | 34                | Once Submitted |
| 37 | A     | elx_reel01_scn1030_shot3480 | Salil Devji             | 15                | Fresh          |
| 37 | A     | elx_reel01_scn1030_shot3480 | Salil Devji             | 20                | Once Submitted |
| 7  | B     | elx_reel01_scn1010_shot1030 | Darshan                 | 4                 | Fresh          |
| 7  | B     | elx_reel01_scn1010_shot1030 | Varion                  | 6                 | Once Submitted |
| 17 | B     | elx_reel01_scn1010_shot1140 | Varion                  | 17                | Once Submitted |
| 17 | B     | elx_reel01_scn1010_shot1140 | Varion                  | 14                | Fresh          |
+----+-------+-----------------------------+-------------------------+-------------------+----------------+

Answer 1

这里的问题是您也在 isn.version 字段上进行分组，并尝试计算该组中相同字段的最小值。它只会返回相同的值，这就是为什么所有内容都是“新鲜”的。您需要在子查询 ( Derived Table ) 中单独确定一组 (grade, shot_id) 的最小 isn.version 值，然后用它来检查无论是否最小。

SELECT   s.shot_id,
         s.shot_name ,
         isn.reviewer AS submitted_by_supervisor,
         isn.version  AS version_submitted,
         isn.grade    AS grade,
         CASE
             WHEN isn.version = dt.min_version THEN 'FRESH'
             ELSE 'Once Submitted'
         END AS 'submit_type'
FROM     viewd_elx.india_supe_note isn
JOIN     viewd_elx.shot s
ON       s.shot_id = isn.shot_id
JOIN     viewd_elx.team t
ON       isn.shot_id = t.shot_id
JOIN     viewd_elx.viewd_team vt
ON       isn.shot_id = vt.shot_id
JOIN     (
           SELECT grade, 
                  shot_id, 
                  MIN(version) AS min_version 
           FROM viewd_elx.india_supe_note 
           WHERE promoted = 'YES' 
             AND grade IN ('A', 'B') 
           GROUP BY grade, 
                    shot_id
        ) AS dt 
ON       dt.grade = isn.grade 
         AND dt.shot_id = isn.shot_id 
WHERE    isn.promoted='Yes'
         AND isn.grade IN ('A','B')
GROUP BY s.shot_id, 
         s.shot_name, 
         isn.reviewer, 
         isn.version, 
         isn.grade, 
         dt.min_version