首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >希望使用MySQL group_concat将两行合并为一行

问希望使用MySQL group_concat将两行合并为一行
EN

Stack Overflow用户
提问于 2017-07-31 12:16:01
回答 1查看 150关注 0票数 0

我希望使用group_concat mysql查询将2行合并为一行。

代码语言:javascript
复制
$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();

我试过上面的一个,但它提供了如下的一些其他结果

代码语言:javascript
复制
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表,如下所示

​

​

所以请查查我哪里错了。谢谢

EN

回答 1

Stack Overflow用户

回答已采纳

发布于 2017-08-01 12:50:38

请找到下面提到的解决方案,有点冗长的操作,但我认为这将对您有所帮助。

代码语言:javascript
复制
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;

表数据

代码语言:javascript
复制
+----+----------------+-----------+-----------+------------+
| 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      |
+----+----------------+-----------+-----------+------------+

结果

代码语言:javascript
复制
+----+-----------+-------------+-------------+
| 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 |
+----+-----------+-------------+-------------+

如果不管用请告诉我。

票数 1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/45415716

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档