我希望使用group_concat mysql查询将2行合并为一行。
$query = $this->db->select('trt.day, group_concat(trt.open_time, trt.close_time)')
->from('tbl_restaurant_timing as trt')
->where('trt.restaurant_id', $restaurant_id, FALSE)
->group_by('trt.day')
->get();我试过上面的一个,但它提供了如下的一些其他结果
Array
(
[0] => Array
(
[day] => Fri
[group_concat(trt.open_time, trt.close_time)] => 18:0023:00,10:0015:00
)
[1] => Array
(
[day] => Mon
[group_concat(trt.open_time, trt.close_time)] => 10:0015:00,18:0023:00
)
[2] => Array
(
[day] => Sat
[group_concat(trt.open_time, trt.close_time)] => 18:0023:00,10:0015:00
)
[3] => Array
(
[day] => Sun
[group_concat(trt.open_time, trt.close_time)] => 10:0015:00,18:0023:00
)
[4] => Array
(
[day] => Thu
[group_concat(trt.open_time, trt.close_time)] => 10:0015:00,18:0023:00
)
[5] => Array
(
[day] => Tue
[group_concat(trt.open_time, trt.close_time)] => 18:0023:00,10:0015:00
)
[6] => Array
(
[day] => Wed
[group_concat(trt.open_time, trt.close_time)] => 18:0023:00,10:0015:00
)
)在这里,你可以看到第二排排在第一位,这是错误的。

这个表我想要的结果是php表,如下所示

所以请查查我哪里错了。谢谢
发布于 2017-08-01 12:50:38
请找到下面提到的解决方案,有点冗长的操作,但我认为这将对您有所帮助。
SELECT
trt.id,
trt.day,
(SELECT CONCAT_WS('-',open_time, close_time) from tbl_restaurant_timing trt1 WHERE trt1.day = trt.day ORDER BY trt1.id ASC LIMIT 1) as first_slot,
(SELECT CONCAT_WS('-',open_time, close_time) from tbl_restaurant_timing trt1 WHERE trt1.day = trt.day ORDER BY trt1.id DESC LIMIT 1) as second_slot
FROM tbl_restaurant_timing trt
WHERE trt.restaurant_id = 1
GROUP BY trt.day
ORDER BY id;表数据
+----+----------------+-----------+-----------+------------+
| id | restaurant_id | day | open_time | close_time |
+----+----------------+-----------+-----------+------------+
| 1 | 1 | Monday | 10:00 | 15:00 |
+----+----------------+-----------+-----------+------------+
| 2 | 1 | Monday | 18:00 | 23:50 |
+----+----------------+-----------+-----------+------------+
| 3 | 1 | Tuesday | 09:00 | 15:00 |
+----+----------------+-----------+-----------+------------+
| 4 | 1 | Tuesday | 18:00 | 23:00 |
+----+----------------+-----------+-----------+------------+
| 6 | 1 | Wednesday | 10:00 | 15:00 |
+----+----------------+-----------+-----------+------------+
| 7 | 1 | Wednesday | 18:00 | 23:00 |
+----+----------------+-----------+-----------+------------+
| 8 | 1 | Thursday | 10:00 | 15:00 |
+----+----------------+-----------+-----------+------------+
| 9 | 1 | Thursday | 18:00 | 23:00 |
+----+----------------+-----------+-----------+------------+
| 13 | 1 | Friday | 10:00 | 15:00 |
+----+----------------+-----------+-----------+------------+
| 14 | 1 | Friday | 18:00 | 23:00 |
+----+----------------+-----------+-----------+------------+
| 15 | 1 | Saturday | 10:00 | 15:00 |
+----+----------------+-----------+-----------+------------+
| 16 | 1 | Saturday | 18:00 | 23:00 |
+----+----------------+-----------+-----------+------------+
| 17 | 1 | Sunday | 10:00 | 15:00 |
+----+----------------+-----------+-----------+------------+
| 18 | 1 | Sunday | 18:00 | 23:00 |
+----+----------------+-----------+-----------+------------+结果
+----+-----------+-------------+-------------+
| id | day | first_slot | second_slot |
+----+-----------+-------------+-------------+
| 1 | Monday | 10:00-15:00 | 18:00-23:50 |
+----+-----------+-------------+-------------+
| 3 | Tuesday | 09:00-15:00 | 18:00-23:00 |
+----+-----------+-------------+-------------+
| 6 | Wednesday | 10:00-15:00 | 18:00-23:00 |
+----+-----------+-------------+-------------+
| 8 | Thursday | 10:00-15:00 | 18:00-23:00 |
+----+-----------+-------------+-------------+
| 13 | Friday | 10:00-15:00 | 18:00-23:00 |
+----+-----------+-------------+-------------+
| 15 | Saturday | 10:00-15:00 | 18:00-23:00 |
+----+-----------+-------------+-------------+
| 17 | Sunday | 10:00-15:00 | 18:00-23:00 |
+----+-----------+-------------+-------------+如果不管用请告诉我。
https://stackoverflow.com/questions/45415716
复制相似问题