在mysql中合并行

wwtsj6pe 于 2023-06-21 发布在 Mysql

关注(0)|答案(1)|浏览(72)

我有一个查询，知道有多少用户每月有6个或更多的活动，但我只有关系，而不是每月计数
这是我的查询

SELECT 
MONTH(12_main.timestamp)  AS 'MONTH' ,

  (SELECT 12_main.user )AS 'USER'

 FROM 12_main
 
 INNER JOIN 12_pack on 12_main.pack =12_pack.pack_id 
 INNER JOIN 12_ubication_pack on 12_pack.pack_id= 12_ubication_pack.pack 
 INNER JOIN 10_working_ubication on 12_ubication_pack.ubication =10_working_ubication.id 
 INNER JOIN 12_productive_areas on 10_working_ubication.working_line=12_productive_areas.working_line 

 WHERE 12_main.status_cert ='4' 
 AND YEAR(12_main.timestamp)='2023' 

GROUP BY 12_main.user
HAVING count( * )>=6
;

给予了我这个
| 月|用户|
| - -----|- -----|
| 1| JUAN12|
| 1| MARY23|
| 2|公司简介|
| 2| DAN454|
| 3| ROB345|
我需要月数和用户数量
| 月|用户|
| - -----|- -----|
| 1| 2|
| 2| 2|
| 3| 1|

mysql

来源：https://stackoverflow.com/questions/76409731/merge-rows-in-mysql

1条答案

按热度按时间

uplii1fm1#

尝试使用group by与月份，所以代码看起来像下面这将解决这个问题

SELECT 
MONTH(12_main.timestamp) AS 'MONTH' , 
COUNT(DISTINCT 12_main.user) AS 'USER_COUNT' 
FROM 12_main 

INNER JOIN 12_pack ON 12_main.pack = 12_pack.pack_id 
INNER JOIN 12_ubication_pack ON 12_pack.pack_id = 12_ubication_pack.pack 
INNER JOIN 10_working_ubication ON 12_ubication_pack.ubication = 
10_working_ubication.id 
INNER JOIN 12_productive_areas ON 10_working_ubication.working_line = 
12_productive_areas.working_line 

WHERE 12_main.status_cert = '4' 
AND YEAR(12_main.timestamp) = '2023' 

GROUP BY MONTH(12_main.timestamp) HAVING COUNT(DISTINCT 12_main.user) >= 6;

赞(0）回复(0）举报 2023-06-21

我来回答

在mysql中合并行

1条答案

相关问题

热门标签

最新问答