如何创建此查询？

nwwlzxa7 于 2021-07-24 发布在 Java

关注(0)|答案(3)|浏览(192)

我这里有一个mysql查询：

SELECT
   COUNT(*) ReleasePerMonth,
   d.name as DevGroup_REGION 
FROM
   release_summary r 
   inner join
      gti_server_info g 
      on r.gti_server_id = g.gti_server_id 
   inner join
      dev_group d 
      on d.dev_group_id = g.dev_group_id 
WHERE
   r.testingFinishedOn_timestamp >= '2020-05-01 00:00:00' 
   AND r.testingFinishedOn_timestamp <= '2020-05-31 00:00:00' 
   AND r.test_type != 14 
GROUP BY
   d.name ;

现在，我想每个月都这样。也就是说，

r.testingFinishedOn_timestamp >= '2019-01-01 00:00:00' 
   AND r.testingFinishedOn_timestamp <= '2019-01-31 00:00:00'

和

r.testingFinishedOn_timestamp >= '2019-05-02 00:00:00' 
   AND r.testingFinishedOn_timestamp <= '2019-02-31 00:00:00'

直到年底。目前，我正在手动执行此操作。有什么办法我可以用自动化的方式来做吗？
澄清：
我想要12张12个月的table。

sql mysql

来源：https://stackoverflow.com/questions/62568309/how-can-i-create-this-query

3条答案

按热度按时间

avwztpqn1#

请尝试以下操作：

SELECT
   YEAR(r.testingFinishedOn_timestamp) year,
MONTH(r.testingFinishedOn_timestamp) month,
   COUNT(*) ReleasePerMonth,
   d.name as DevGroup_REGION 
FROM
   release_summary r 
   inner join
      gti_server_info g 
      on r.gti_server_id = g.gti_server_id 
   inner join
      dev_group d 
      on d.dev_group_id = g.dev_group_id 
WHERE
   r.test_type != 14 
GROUP BY
   YEAR(r.testingFinishedOn_timestamp),
   MONTH(r.testingFinishedOn_timestamp),
   d.name
ORDER BY year, month;

赞(0）回复(0）举报 2021-07-24

vatpfxk52#

一种可能的解决方案是查询全年的数据并按月份分组（testingfinishedon\u timestamp）。
我在下面添加了查询，但未进行测试：

SELECT
   MONTH(r.testingFinishedOn_timestamp) ReleaseMonth,
   COUNT(*) ReleasePerMonth,
   d.name as DevGroup_REGION 
FROM
   release_summary r 
   inner join
      gti_server_info g 
      on r.gti_server_id = g.gti_server_id 
   inner join
      dev_group d 
      on d.dev_group_id = g.dev_group_id 
WHERE
   r.testingFinishedOn_timestamp >= '2020-01-01 00:00:00' 
   AND r.testingFinishedOn_timestamp < '2021-01-01 00:00:00' 
   AND r.test_type != 14 
GROUP BY
   d.name, MONTH(r.testingFinishedOn_timestamp);
ORDER BY
  MONTH(r.testingFinishedOn_timestamp), d.name

根据可用的文档，month（）函数返回月份的数字，例如，for january返回1。如果您想获得您的月份名称，请使用monthname（）函数而不是month（）。

赞(0）回复(0）举报 2021-07-24

g6ll5ycj3#

您可以尝试下面的查询-使用 last_day() ```
SELECT year(r.testingFinishedOn_timestamp),month(r.testingFinishedOn_timestamp),
COUNT(*) ReleasePerMonth

FROM
release_summary r
inner join
gti_server_info g
on r.gti_server_id = g.gti_server_id
inner join
dev_group d
on d.dev_group_id = g.dev_group_id
WHERE
r.testingFinishedOn_timestamp >= date_add(date_add(LAST_DAY(now()),interval 1 DAY),interval -12 MONTH)
AND r.testingFinishedOn_timestamp <= LAST_DAY(now())
AND r.test_type != 14
GROUP BY
year(r.testingFinishedOn_timestamp),month(r.testingFinishedOn_timestamp) ;

赞(0）回复(0）举报 2021-07-24

我来回答

如何创建此查询？

3条答案

相关问题

热门标签

最新问答