mysql - 如何查询多行到单行mysql(how to query multi row to a single row mysql)

Question

I am trying to combine several rows in a table into a single row based on a condition.

(我试图根据条件将表中的几行合并为一行。)

For example, my table like this:

(例如，我的表是这样的：)

id    date        name    count1    count2    count3


1     2019-01-01  name1    8         10        5
2     2019-01-01  name2    12        5         20
3     2019-01-01  name3    15        5         10
4     2019-01-02  name4    12        5         40
5     2019-01-02  name5    3         5         10
6     2019-01-03  name6    10        2         7

And now, I want to the result of query to look like this:

(现在，我希望查询结果看起来像这样：)

{
 date:"2019-01-01",
 name1:{
      count1:8,
      count2:10,
      count3:15
    },
 name2:{
      count1:12,
      count2:5,
      count3:20
    },
 name3:{
      count1:15,
      count2:5,
      count3:10
    }
},
{
 date:"2019-01-02"
 name4:{
     count1:12,
     count2:5,
     count3:10
    },
 name5:{
     count1:3,
     count2:5,
     count3:10
   }
},
{
 date:"2019-01-03",
 name6:{
     count1:10,
     count2:2,
     count3:7
   }
}

How do I query a row that converts to json like above

(我如何查询像上面这样转换为json的行)

Can someone help me in achieving this?

(有人可以帮助我实现这一目标吗？)

  ask by Nhu Pham translate from so

Answer 1

等待大神答复