mysql - SQL检查列值是否变化

Question

假设我们有两个具有 1 对 n 关系的表

group
--+-------+
id|name   |
--+-------+
1 | GROUP1|
2 | GROUP2|
3 | GROUP3|
--+-------+

unit
--+-----+-----+------+
id|group|value|status|
--+-----+-----+------+
 1| 1   |  0  |  0   |
 2| 1   |  20 |  0   |
 3| 1   |  5  |  0   |
 4| 2   |  1  |  1   |
 5| 2   |  3  |  1   |
 6| 2   |  0  |  1   |
 7| 3   |  5  |  1   |
 8| 3   |  8  |  0   |
 9| 3   |  6  |  0   |

我怎么知道某行在组中是否是唯一的？如果不是唯一的，它有什么值(value)？例如:

SELECT 
    DISTINCT `group`.id,
    `group`.name,
    MIN(unit.value) AS 'min',
    MAX(unit.value) AS 'max',
    isUnique(unit.status) AS 'status'
FROM unit JOIN `group` ON unit.`group` = `group`.id
GROUP BY `group`.id
ORDER BY `group`.id

应该返回:

--+-------+---+---+------+
id|name   |min|max|status|
--+-------+---+---+------+
1 | GROUP1|0  |20 | all 0|
2 | GROUP2|0  |3  | all 1|
3 | GROUP3|5  |8  | both |
                  (status is in string just to ilustarate returned values)

Answer 1

您可以使用 GROUP_CONCAT 每个组具有不同的状态

SELECT 
    DISTINCT `group`.id,
    `group`.name,
    MIN(unit.value) AS 'min',
    MAX(unit.value) AS 'max',
    GROUP_CONCAT(DISTINCT unit.status) AS 'status'
FROM unit JOIN `group` ON unit.`group` = `group`.id
GROUP BY `group`.id
ORDER BY `group`.id

结果会是

--+-------+---+---+------+
id|name   |min|max|status|
--+-------+---+---+------+
1 | GROUP1|0  |20 |  0   |
2 | GROUP2|0  |3  |  1   |
3 | GROUP3|5  |8  | 1,0  |

Fiddle