时间戳

首页 > 解决方案 > 连接同一张表时，如何将连接的数据放在单独的行而不是单独的列中？

mysql - 连接同一张表时，如何将连接的数据放在单独的行而不是单独的列中？

问题描述

让我们说，我有一个类似以下的查询。

select * from `Students` s1
join `Students` s2
on s2.id = s1.id + 1 and s2.marks >= 80 and s1.marks >= 80

在这种情况下， 和 的数据s1将s2被放入两个单独的列中，但是我怎样才能将它们放入单独的行中呢？

这是一个示例数据：

+---+------+
| id| marks| 
+---+------|
| 1 | 88   |
| 2 | 77   |
| 3 | 81   |
| 4 | 82   |
| 5 | 75   |
| 6 | 80   |
| 7 | 90   |
| 8 | 93   |
+---+------+

我想要的是id显示两个连续 80+ 标记的行（按 排序）。例如，在这种情况下，将显示 id 为 3、4、6、7 和 8 的行。

标签： mysqlsqljoinwindow-functionsexists

解决方案

如果您的 MySql 版本是 8.0+，您可以使用LAG()和LEAD()窗口函数来检查marks每行的上一个和下一个值：

SELECT id, marks
FROM (
  SELECT *,
         LAG(marks) OVER (ORDER BY id) prev_marks,
         LEAD(marks) OVER (ORDER BY id) next_marks
  FROM students       
) t
WHERE marks >= 80 AND (prev_marks >= 80 OR next_marks >= 80)
ORDER BY id;

对于以前的版本，请使用EXISTS：

SELECT s1.*
FROM students s1
WHERE s1.marks >= 80
AND EXISTS (
  SELECT 1 
  FROM students s2
  WHERE s2.id IN (s1.id - 1, s1.id + 1) AND s2.marks >= 80
)
ORDER BY s1.id;

或者，使用自联接：

SELECT DISTINCT s1.*
FROM students s1 INNER JOIN students s2
ON s2.id IN (s1.id - 1, s1.id + 1)
WHERE s1.marks >= 80 AND s2.marks >= 80
ORDER BY s1.id;

请参阅演示。

推荐阅读