php - 将表分组后将表内部联接

我已经产生了以下查询来搜索表，并仅返回多次出现的查询（最大出现是两次，永远不会再返回）：

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 } ?>

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

最佳答案

据我了解，您最多希望2个相同的serials计数，并且您想要第一个日期和开始日期，所以尝试一下：

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

关于php - 将表分组后将表内部联接，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/27523144/