主页 / dba / 问题 / 3259
Accepted
tostinni
Asked: 2011-06-11 09:35:17 +0800 CST

如何获得 A 列，其中 B 列按 C 列分组？

  • 772

我想获取具有最小“顺序”列的每个类别（我的分组列）的数据集的 ID。
这里用一组数据来说明我的想法：

CREATE TABLE DATAS (
    ID INT(2) ,
    CATEGORY INT(2) ,
    ORD INT(1) 
);

INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 2);

预期结果：

ID   CATEGORY
-------------
3    1
4    2

奖金问题，即使“订单”列（ORD）有一些重复，如何只检索一个 ID？

INSERT INTO DATAS (ID, CATEGORY, ORD)
VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 1), (6, 3, NULL), (7, 3, NULL);

预期结果（例如使用 MIN 或其他建议）：

ID   CATEGORY
-------------
3    1
4    2
6    3

欢迎使用 Oracle 或 MySQL 查询，非常感谢。

mysql oracle

4 个回答

  1. Best Answer

    Oracle解决这两个问题：

    SELECT ID, Category FROM (
   SELECT FIRST_VALUE(ID) OVER 
      (PARTITION BY Category ORDER BY Ord) IDOfSmallestOrdForCategory
      , Category, ID FROM DATAS
   )
   WHERE ID = IDOfSmallestOrdForCategory;

    这两个问题的通用解决方案：

    SELECT MIN(a.ID), a.Category FROM DATAS a
JOIN (SELECT CATEGORY, COALESCE(MIN(ORD),0) MINORD FROM DATAS GROUP BY CATEGORY) b
ON COALESCE(a.ORD,0) = b.MINORD AND a.Category = b.Category
GROUP BY a.Category;

    甲骨文 DDL/DML：

    CREATE TABLE DATAS (
    ID       Integer,
    CATEGORY Integer,
    ORD      Integer 
);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 2);

INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (2, 1, 2); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (3, 1, 1); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (4, 2, 1);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (5, 2, 1); 
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (6, 3, NULL);
INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (7, 3, NULL);
    • 6

  2. MySQL解决方案

    我想我明白了！！！！

    查询第一个答案：

    SELECT A.ID,A.CATEGORY FROM
(SELECT ID,CATEGORY,ORD FROM DATAS GROUP BY ID,CATEGORY) A
INNER JOIN
(SELECT MIN(ORD) ORD FROM DATAS) B
USING (ORD);

    查询第二个答案：

    SELECT AA.ID,AA.CATEGORY FROM
(SELECT ID,CATEGORY,IFNULL(AAA.ORD,BBB.ORD) ORD FROM DATAS AAA,
(SELECT MIN(ORD) ORD FROM DATAS) BBB) AA
INNER JOIN
(SELECT MIN(ORD) ORD FROM DATAS) BB
USING (ORD)
GROUP BY AA.CATEGORY,AA.ORD;

    第二个答案有点困难，因为我必须找到一种方法将 NULL 替换为最小 ORD 值。我希望这就是你想用 NULL 做的事情。如果您查看子查询 BBB，它包含最小 ORD 值。我在带有子查询 AAA 的笛卡尔积中使用它作为替换 NULL ORD 值的方法。在那之后剩下的就变得容易了。

    这是第一个查询运行的示例：

    mysql> use tostinni
Database changed
mysql> DROP TABLE IF EXISTS DATAS;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE DATAS (
    ->     ID INT(2) ,
    ->     CATEGORY INT(2) ,
    ->     ORD INT(1)
    -> );
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO DATAS (ID, CATEGORY, ORD) VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 2);
Query OK, 5 rows affected (0.03 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM DATAS;
+------+----------+------+
| ID   | CATEGORY | ORD  |
+------+----------+------+
|    1 |        1 |    3 |
|    2 |        1 |    2 |
|    3 |        1 |    1 |
|    4 |        2 |    1 |
|    5 |        2 |    2 |
+------+----------+------+
5 rows in set (0.00 sec)

mysql> SELECT A.ID,A.CATEGORY FROM
    -> (SELECT ID,CATEGORY,ORD FROM DATAS GROUP BY ID,CATEGORY) A
    -> INNER JOIN
    -> (SELECT MIN(ORD) ORD FROM DATAS) B
    -> USING (ORD);
+------+----------+
| ID   | CATEGORY |
+------+----------+
|    3 |        1 |
|    4 |        2 |
+------+----------+
2 rows in set (0.00 sec)

mysql>

    这是第二个查询的示例运行：

    mysql> DROP TABLE IF EXISTS DATAS;
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE DATAS (
    ->     ID INT(2) ,
    ->     CATEGORY INT(2) ,
    ->     ORD INT(1)
    -> );
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO DATAS (ID, CATEGORY, ORD)
    -> VALUES (1, 1, 3), (2, 1, 2), (3, 1, 1), (4, 2, 1), (5, 2, 1), (6, 3, NULL), (7, 3, NULL);
Query OK, 7 rows affected (0.03 sec)
Records: 7  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM DATAS;
+------+----------+------+
| ID   | CATEGORY | ORD  |
+------+----------+------+
|    1 |        1 |    3 |
|    2 |        1 |    2 |
|    3 |        1 |    1 |
|    4 |        2 |    1 |
|    5 |        2 |    1 |
|    6 |        3 | NULL |
|    7 |        3 | NULL |
+------+----------+------+
7 rows in set (0.00 sec)

mysql> SELECT AA.ID,AA.CATEGORY FROM
    -> (SELECT ID,CATEGORY,IFNULL(AAA.ORD,BBB.ORD) ORD FROM DATAS AAA,(SELECT MIN(ORD) ORD
 FROM DATAS) BBB) AA
    -> INNER JOIN
    -> (SELECT MIN(ORD) ORD FROM DATAS) BB
    -> USING (ORD)
    -> GROUP BY AA.CATEGORY,AA.ORD;
+------+----------+
| ID   | CATEGORY |
+------+----------+
|    3 |        1 |
|    4 |        2 |
|    6 |        3 |
+------+----------+
3 rows in set (0.00 sec)

mysql>

    试试看 ！！！

    • 1

  3. 这是我的解决方案不如@Leigh 的优雅，但兼容 Oracle 和 MySQL ;)

    SELECT MIN(ID) AS ID, D1.CATEGORY
FROM DATAS D1
WHERE NOT EXISTS (
    SELECT NULL 
    FROM DATAS D2
    WHERE D2.CATEGORY = D1.CATEGORY
    AND D2.ID <> D1.ID 
    AND COALESCE(D2.ORD, 0 ) < COALESCE(D1.ORD, 0 ) 
)
GROUP BY D1.CATEGORY

    我不喜欢我必须添加一个GROUP BY来过滤重复的事实......

    • 0

  4. 这将返回第一部分的答案：

    mysql> SELECT ID, CATEGORY, MIN(ORD) FROM DATAS GROUP BY CATEGORY;
+------+----------+----------+
| ID   | CATEGORY | MIN(ORD) |
+------+----------+----------+
|    1 |        1 |        1 |
|    4 |        2 |        1 |
|    6 |        3 |     NULL |
+------+----------+----------+
3 rows in set (0.00 sec)

    第二部分的答案有点复杂。你可能只想要一个答案，但你想要哪个答案？最强ID？最少类别？主观。否则这可能会这样做：

    mysql> SELECT * FROM (SELECT ID, CATEGORY, MIN(ORD) AS min FROM DATAS GROUP BY ID, CATEGORY) m GROUP BY min ORDER BY ID ASC;
+------+----------+------+
| ID   | CATEGORY | min  |
+------+----------+------+
|    1 |        1 |    3 |
|    2 |        1 |    2 |
|    3 |        1 |    1 |
|    6 |        3 | NULL |
+------+----------+------+
4 rows in set (0.01 sec)
    • -1

相关问题