从表中选择数据作为组

p8h8hvxi 于 2021-08-09 发布在 Java

关注(0)|答案(2)|浏览(576)

我有一个表（主键作为 ID , COLUMN A 以及 COLUMN B )具有以下数据：

+---------+------------+------------+-----------+
 |   ID    |  COLUMN A  |  COLUMN B  |  COLUMN C |
 +---------+------------+------------+-----------+
 |   1     |    A1      |    B1      |    C1     |
 |   2     |    A2      |    B2      |    C2     |
 |   3     |    A1      |    B1      |    C3     |
 |   4     |    A3      |    B3      |    C1     |
 |   5     |    A1      |    B1      |    C3     |
 +-----------------------------------------------+

我希望获取的数据为：

+---------+------------+------------+-----------+
 |   ID    |  COLUMN A  |  COLUMN B  |  COLUMN C |
 +---------+------------+------------+-----------+
 |   2     |    A2      |    B2      |    C2     |
 |   4     |    A3      |    B3      |    C1     |
 |   5     |    A1      |    B1      |    C3     |
 +-----------------------------------------------+

我应该用这个吗：

SELECT * FROM TABLE_NAME
  GROUP BY 
  COLUMN A, COLUMN B
  ORDER BY ID;

sql mysql db2 Database

来源：https://stackoverflow.com/questions/62631099/selecting-data-as-groups-from-table

2条答案

按热度按时间

ryevplcw1#

假设您使用的是较旧版本的mysql，下面是一种不使用窗口函数的方法：

SELECT t1.ID, t1.A, t1.B, t1.C
FROM yourTable t1
INNER JOIN
(
    SELECT A, B, MAX(ID) AS MAX_ID
    FROM yourTable
    GROUP BY A, B
) t2
    ON t1.A = t2.A AND t1.B = t2.B AND t1.ID = t2.MAX_ID
ORDER BY
    t1.ID;

赞(0）回复(0）举报 2021-08-09

lpwwtiir2#

不使用窗口功能 max . 这是演示。

select
    max(ID) as ID,
    columnA,
    columnB,
    max(columnC) as columnC
from myTable
group by    
    columnA,
    columnB
order by    
    ID

你可以使用窗口功能 row_number ，这是演示。

select
  ID,
  columnA,
  columnB,
  columnC
from
(
  select
    *,
    row_number() over (partition by columnA, columnB order by ID desc) as rn
  from yourTable
) subq
where rn = 1

输出：

| ID  | columnA | columnB | columnC |
| --- | ------- | ------- | ------- |
| 2   | A2      | B2      | C2      |
| 4   | A3      | B3      | C1      |
| 5   | A1      | B1      | C3      |

赞(0）回复(0）举报 2021-08-09

我来回答

从表中选择数据作为组

2条答案

相关问题

热门标签

最新问答