MySQL中的group by和order by

Question

我有一张包含4个字段和3条记录的表，以下查询可以正常工作，但我希望在分组之前按照a.start_date排序。

Tabel Name : testdata
Table fields : data type
id: autoincrement,primary key
pattern : varchar
start_date : datetime
cost : decimal(10,5)

记录：

ID | Pattern | Start_date | Cost
1  | 1       | 2013-09-15 | 10.00
2  | 1       | 2013-09-04 | 15.00
3  | 1       | 2013-09-21 | 28.00

查询：

select a.*, b.cost AS future_cost, b.start_date AS future_date
FROM testdata a
LEFT JOIN testdata b ON a.pattern = b.pattern AND a.id <> b.id
GROUP BY a.pattern

当前输出：

id  | pattern | start_date | cost | future_date | future_cost
1   |  1      | 2013-09-15 | 10.00| 2013-09-04  | 15.00

所需输出：

id  | pattern | start_date | cost | future_date | future_cost
2   |  1      | 2013-09-04 | 15.00| 2013-09-15  | 10.00

我需要的是，在上面的例子中，最早的日期是2013-09-04，所以未来的日期将是15。如果我删除2013-09-04的记录，则开始日期应为2013-09-15，未来日期应为2013-09-21。

我需要查询以获得所需的输出结果？

任何帮助和想法都将不胜感激。

- Ankit Doshi

---

为什么要使用group by？你没有任何聚合函数。只需按start_date排序即可。 - FJT

@FionaT 如果我运行这个语句 select a.*, b.cost AS future_cost, b.start_date AS future_date FROM example a LEFT JOIN example b ON a.pattern = b.pattern AND a.id <> b.id ORDER BY a.start_date，那么它会给我这个结果 http://sqlfiddle.com/#!2/e33a1/4 - Ankit Doshi

Answer 1

你只得到1行而不是6行的原因是你滥用了mysql的独特松散的group by语法（我不会详细解释，但你没有按所有非聚合列进行分组 - 通常是语法错误）。

基本上，你想要连接最早的2行，并且你可以将那个独特的功能弯曲成你想要的形式。

select a.*, b.cost AS future_cost, b.start_date AS future_date
from (select * from (select * from testdata order by start_date) x group by pattern) a
left join (select * from testdata order by start_date) b
    on a.pattern = b.pattern and a.id != b.id
group by a.pattern

请参阅SQLFiddle

这里的情况是，当使用group by时没有对所有非聚合列进行分组，mysql会返回遇到的（第一行），通过从有序行集中选择，可以控制是哪一行。

- Bohemian

---

不用担心。请注意，这只涉及MySQL技巧。祝你好运 :) - Bohemian

我有一个小疑问，对于单个记录它没有给我任何输出。请检查这个链接：http://sqlfiddle.com/#!2/ecf75/1 有什么解决办法吗？ - Ankit Doshi

已解决 - 我转换为 left join（没有意识到只有一行）。Fiddle 也已更新。 - Bohemian

如果我想添加搜索功能，那么在哪里添加搜索条件，比如 a.pattern = '1'？ - Ankit Doshi

如果您只想搜索一个“模式”，修改此查询的最佳性能方式是将select * from testdata order by start_date更改为select * from testdata where pattern '1' order by start_date。 - Bohemian

Answer 2

我不太确定这是否是你想要的，但我认为你可以使用distinct而不是group by。

所以类似这样：

select a.id,distinct(a.pattern),a.start_date,a.cost, b.cost AS future_cost, b.start_date AS future_date
FROM testdata a
LEFT JOIN testdata b ON a.pattern = b.pattern AND a.id <> b.id
order by a.start_date asc

- Wikunia