保持特定列的分組最小值的行，但使用附加條件

從這個資料集中：

mysql> SELECT * FROM document_signature;
 ---- ------------- ------------- --------- ------- ----------- 
| id | document_id | employee_id | user_id | order | status    |
 ---- ------------- ------------- --------- ------- ----------- 
|  1 |           1 |           2 |    NULL |     0 | SIGNED    |
|  2 |           1 |           3 |    NULL |     1 | NOTSIGNED |
|  3 |           1 |           4 |    NULL |     1 | NOTSIGNED |
|  4 |           2 |           3 |    NULL |     0 | NOTSIGNED |
|  5 |           3 |        NULL |       1 |     0 | SIGNED    |
|  6 |           3 |           1 |    NULL |     0 | NOTSIGNED |
 ---- ------------- ------------- --------- ------- ----------- 
6 rows in set (0.00 sec)

我想找到具有最小值的行order，但只能從狀態為的行中找到NOTSIGNED，即使每個行不止一個document_id

使用此查詢：

SELECT s.*
FROM document_signature s
WHERE `order` =
      (SELECT MIN(s2.`order`)
       FROM document_signature s2
       WHERE s.document_id = s2.document_id
         AND s2.status = 'NOTSIGNED');

這些是我得到的結果：

 ---- ------------- ------------- --------- ------- ----------- 
| id | document_id | employee_id | user_id | order | status    |
 ---- ------------- ------------- --------- ------- ----------- 
|  2 |           1 |           3 |    NULL |     1 | NOTSIGNED |
|  3 |           1 |           4 |    NULL |     1 | NOTSIGNED |
|  4 |           2 |           3 |    NULL |     0 | NOTSIGNED |
|  5 |           3 |        NULL |       1 |     0 | SIGNED    |
|  6 |           3 |           1 |    NULL |     0 | NOTSIGNED |
 ---- ------------- ------------- --------- ------- ----------- 
5 rows in set (0.00 sec)

我的問題是：為什么status SIGNED結果集中有一行，我在這里做錯了什么？

uj5u.com熱心網友回復：

雖然，兩者document_id=3都有不同的值status，但它們具有相同的order值。因此，您的MIN()結果實際上對兩者都是正確的status。這里的快速解決方法是添加另一個status='NOSIGNED'條件，如下所示：

SELECT s.*
FROM document_signature s
WHERE `order` =
      (SELECT MIN(s2.`order`)
       FROM document_signature s2
       WHERE s.document_id = s2.document_id
         AND s2.status = 'NOTSIGNED')
  AND s.status='NOTSIGNED'; /*add here*/

演示小提琴

在 MySQL v8 上實際上有不止一種方法可以做到這一點，這里有一種：

SELECT *
  FROM
(SELECT *,
       MIN(`order`) OVER (PARTITION BY document_id) AS min_ord
   FROM document_signature
  WHERE status='NOTSIGNED') s
WHERE s.`order`=s.min_ord;

在您當前的示例資料中，這可能看起來沒有什么不同，但假設我們在表中再添加兩行：

INSERT INTO `document_signature` VALUES
(7,1,3,NULL,2,'NOTSIGNED'),
(8,2,4,NULL,2,'NOTSIGNED');

它會變成這樣：

 ---- ------------- ------------- --------- ------- ----------- 
| id | document_id | employee_id | user_id | order | status    |
 ---- ------------- ------------- --------- ------- ----------- 
|  1 |           1 |           2 |    NULL |     0 | SIGNED    |
|  2 |           1 |           3 |    NULL |     1 | NOTSIGNED |
|  3 |           1 |           4 |    NULL |     1 | NOTSIGNED |
|  4 |           2 |           3 |    NULL |     0 | NOTSIGNED |
|  5 |           3 |        NULL |       1 |     0 | SIGNED    |
|  6 |           3 |           1 |    NULL |     0 | NOTSIGNED |
|  7 |           1 |           3 |    NULL |     2 | NOTSIGNED |
|  8 |           2 |           4 |    NULL |     2 | NOTSIGNED |
 ---- ------------- ------------- --------- ------- ----------- 

然后，當您運行以下子查詢時：

SELECT *,
       MIN(`order`) OVER (PARTITION BY document_id) AS min_ord
   FROM document_signature
  WHERE status='NOTSIGNED'

你會得到這個結果：

 ---- ------------- ------------- --------- ------- ----------- --------- 
| id | document_id | employee_id | user_id | order | status    | min_ord |
 ---- ------------- ------------- --------- ------- ----------- --------- 
|  2 |           1 |           3 |    NULL |     1 | NOTSIGNED |    1    |
|  3 |           1 |           4 |    NULL |     1 | NOTSIGNED |    1    |
|  4 |           2 |           3 |    NULL |     0 | NOTSIGNED |    0    |
|  6 |           3 |           1 |    NULL |     0 | NOTSIGNED |    0    |
|  7 |           1 |           3 |    NULL |     2 | NOTSIGNED |    1    |
|  8 |           2 |           4 |    NULL |     2 | NOTSIGNED |    0    |
 ---- ------------- ------------- --------- ------- ----------- --------- 

請注意，對于id = 7 & 8， in 的值min_ord與它們的order值不同。那時您將該查詢作為派生表進行WHERE s.order=s.min_ord比較。

在這個小提琴中還有另一個使用 cte 的例子

uj5u.com熱心網友回復：

即使您使用的是 MySQL v4，也可以使用以下查詢：

SELECT @M := MIN(`order`) FROM document_signature WHERE `status` = 'NOTSIGNED';
SELECT * FROM document_signature WHERE `status` = 'NOTSIGNED' AND `order` = @M;

標籤：mysqlsql子查询相关子查询

上一篇：更新多列無效

下一篇：如何在mySQL的插入前觸發器中獲取自動遞增的id？