php mysql asc / desc顺序 [英] php mysql asc/desc order

问题描述

表：

**timeslot**:
----------
id_timeslot   times
1             09:00
2             09:30
3             10:00
4             10:30
5             11:00

**bookslot**
id   id_timeslot     date        b_ref
-------------------------------------------
1    2               2010-02-22  001 
2    3               2010-02-22  001
3    4               2010-02-22  001
4    5               2010-02-22  001
5    2               2010-02-25  002
6    3               2010-02-27  003
7    4               2010-02-27  003
8    5               2010-02-27  003

PHP

$q = $mysqli->query("SELECT * FROM bookslot  
LEFT JOIN timeslot ON bookslot.id_timeslot = timeslot.id_timeslot   
WHERE bookslot.status = 1 
GROUP BY bookslot.b_ref  
ORDER BY bookslot.date ASC, bookslot.id_timeslot ASC LIMIT 20");

HTML结果：

DATE         TIMES  
2010-02-22   10:30
2010-02-25   09:30
2010-02-27   11:00

任何人都注意到了表的结果。时间是不正确的顺序？

i更改了ASC / DESC的方式，仍然显示最后一个id_timeslot的时间？

anyone notice that on the table result. the times is incorrect order?
i changed another way round with ASC / DESC, and still the times showing the last id_timeslot?

预期结果： / p>

EXPECTED RESULT:

DATE         TIMES  
2010-02-22   09:30
2010-02-25   09:30
2010-02-27   10:00

推荐答案

您的 GROUP BY bookslot.b_ref 正在对记录进行分组，因此您只能看到每种情况的最后一次。

Your GROUP BY bookslot.b_ref is grouping the records, so you're only seeing the last time in each case.

尝试使用

SELECT date, time, MIN(bookslot.id_timeslot)
FROM bookslot  
LEFT JOIN timeslot ON bookslot.id_timeslot = timeslot.id_timeslot   
WHERE bookslot.status = 1 
GROUP BY bookslot.b_ref  
ORDER BY bookslot.date ASC, bookslot.id_timeslot ASC LIMIT 20

这篇关于php mysql asc / desc顺序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！