php - 对表进行分组后将表内部连接到自身

Question

我生成了以下查询来搜索表并仅返回多次出现的情况(最大出现次数是两次，不再多):

SELECT * FROM spin
            WHERE `serial` in (
            SELECT `serial` from spin group by `serial`
            HAVING count(*) > 1
            ) ORDER BY test_started DESC

在此表中，每个记录都有一个 test_started 列，然后我将像这样循环访问结果集:

<?php foreach ($tests as $test) { ?>
<tr>
<td><?php if (isset($test->model)) echo $test->model; ?></td>
<td><?php if (isset($test->serial)) echo $test->serial; ?></td>
<td><?php if (isset($test->error_count)) echo $test->error_count; ?></td>
<td><?php if (isset($test->max_temp)) $test_temp = substr($test->max_temp, -2); echo $test_temp; ?></td>
<td><?php if (isset($test->test_started)) echo $test->test_started; ?></td>
<td><?php if (isset($test->test_ended)) echo $test->test_ended; ?></td>
</tr>
<?php } ?>

我想做的是将匹配的序列分组到一列中，我猜为此需要某种不同的查询，然后显示第一个和第二个 test_started 日期

Answer 1

据我所知，您最多需要 2 个相同的连续剧，并且您需要第一个日期和开始日期，因此请尝试一下:

SELECT A.model, A.`serial`, A.error_count, A.max_temp, A.test_started, B.test_ended
 FROM ( 
  SELECT model, `serial`, error_count, max_temp, test_started 
  FROM spin 
  WHERE `serial` in (
    SELECT `serial` from spin group by `serial`
    HAVING count(*) = 2
    ) 
    GROUP BY `serial`, test_started ASC
 ) A
 INNER JOIN
 (
  SELECT model, `serial`, error_count, max_temp, test_started as test_ended
  FROM spin 
  WHERE `serial` in (
    SELECT `serial` from spin group by `serial`
    HAVING count(*) = 2
    ) 
  GROUP BY `serial`, test_started DESC
  ) B
  ON A.`serial` = B.`serial`
  GROUP BY A.`serial

参见 Demo