我正在使用下面这样的示例表。数据集有多个组,每次对表进行写入时,数据集的RunNumber以及每个组的数据和总数都会增加。每个数据集/组组合通常有多行,例如:
RunNumber | 组 | 数据集 | 总计|
---|---|---|---|
1 | 组1 | 数据集A10 | |
1 | 第1组 | 数据集A20 | |
2 | 第1组 | 数据集A30 | |
2 | 第2组 | 数据集A15 | |
1 | 组1 | 数据集B5 | |
1 | 组2 | 数据集B10 | |
1 | 第3组 | 数据集A30 | |
2 | 第3组 | 数据集A30 | |
1 | 第1组 | 数据集C15 | |
1 | 第2组 | 数据集C50 | |
2 | 第2组 | 数据集C70 | |
2 | 组2 | 数据集C90 |
在MySQL 5.x中,您可以使用子查询。
SELECT *
FROM your_table
WHERE (`Group`, Dataset, RunNumber) IN (
SELECT `Group`, Dataset, MAX(RunNumber) AS MaxRunNumber
FROM your_table
GROUP BY `Group`, Dataset
);
测试db<gt;小提琴这里
替代方案
--
-- LEFT JOIN on bigger
--
SELECT t.*
FROM your_table t
LEFT JOIN your_table t2
ON t2.`Group` = t.`Group`
AND t2.Dataset = t.Dataset
AND t2.RunNumber > t.RunNumber
WHERE t2.RunNumber IS NULL
ORDER BY t.`Group`, t.Dataset;
--
-- where NOT EXISTS on bigger
--
SELECT *
FROM your_table t
WHERE NOT EXISTS (
SELECT 1
FROM your_table t2
WHERE t2.`Group` = t.`Group`
AND t2.Dataset = t.Dataset
AND t2.RunNumber > t.RunNumber
)
ORDER BY `Group`, Dataset;
--
-- Emulating DENSE_RANK = 1 with variables
-- Works also in 5.x
--
SELECT RunNumber, `Group`, Dataset, Total
FROM
(
SELECT
@rnk:=IF(@ds=Dataset AND @grp=`Group`, IF(@run=RunNumber, @rnk, @rnk+1), 1) AS Rnk
, @grp := `Group` as `Group`
, @ds := Dataset as Dataset
, @run := RunNumber as RunNumber
, Total
FROM your_table t
CROSS JOIN (SELECT @grp:=null, @ds:=null, @run:=null, @rnk := 0) var
ORDER BY `Group`, Dataset, RunNumber DESC
) q
WHERE Rnk = 1
ORDER BY `Group`, Dataset;
--
-- DENSE_RANK = 1
-- MySql 8 and beyond.
--
SELECT *
FROM
(
SELECT *
, DENSE_RANK() OVER (PARTITION BY `Group`, Dataset ORDER BY RunNumber DESC) AS rnk
FROM your_table
) q
WHERE rnk = 1
ORDER BY `Group`, Dataset;