Mysql(v.5.1(数据库的域和Facebook共享的数量。
tblFacebook
+------------+--------+----------+
| day | shares | domainid |
+------------+--------+----------+
| 1571011200 | 441 | 1 |
| 1571097600 | 443 | 1 |
| 1571184000 | 474 | 1 |
| 1571270400 | 518 | 1 |
| 1571184000 | 849 | 3 |
| 1571270400 | 849 | 3 |
+------------+--------+----------+
我希望看到以下输出:
+------------+------------------+------------------+
| day | domainid1-shares | domainid3-shares |
+------------+------------------+------------------+
| 1571011200 | 441 | |
| 1571097600 | 443 | |
| 1571184000 | 474 | 849 |
| 1571270400 | 518 | 849 |
+------------+-----------------+------------------+
如何?
我确实尝试过,但它没有给我两个单独的列(domainid1共享和domainid3共享(?
SELECT day, GROUP_CONCAT(shares SEPARATOR ', ') FROM tblFacebook GROUP BY day;
您可以使用条件聚合进行透视:
select
day,
max(case when domainid = 1 then shares end) domainid1_shares,
max(case when domainid = 3 then shares end) domainid3_shares
from mytable t
group by day
DB Fiddle上的演示:
天|domainid1_shares|domainid3_shares---------:|----------------:|---------------:1571011200 | 441 |null1571097600 | 443 |null1571184000 | 474 | 8491571270400 | 518 | 849