一个查询中的多个mysql计数

tkclm6bt 于 2021-06-20 发布在 Mysql

关注(0)|答案(2)|浏览(312)

我目前有多个查询，我想收集到一个，他们是相同的，只是日期范围正在改变：
查询1：

SELECT COUNT(simp_posts.ID)
FROM simp_posts
INNER JOIN simp_postmeta
ON ( simp_posts.ID = simp_postmeta.post_id )
WHERE ( ( simp_postmeta.meta_key = '_schedule_end' AND simp_postmeta.meta_value = '0' AND simp_posts.post_status = 'wc-active'))
OR ( ( simp_postmeta.meta_key = '_schedule_end' AND simp_postmeta.meta_value > '2018-08-01 23:33:51' AND simp_posts.post_status = 'wc-active'))
OR ( ( simp_postmeta.meta_key = '_schedule_end' AND simp_postmeta.meta_value > '2018-08-01 23:33:51' AND simp_posts.post_status = 'wc-pending-cancel'))

问题2：

SELECT COUNT(simp_posts.ID)
FROM simp_posts
INNER JOIN simp_postmeta
ON ( simp_posts.ID = simp_postmeta.post_id )
WHERE ( ( simp_postmeta.meta_key = '_schedule_end' AND simp_postmeta.meta_value = '0' AND simp_posts.post_status = 'wc-active'))
OR ( ( simp_postmeta.meta_key = '_schedule_end' AND simp_postmeta.meta_value > '2018-09-01 23:33:51' AND simp_posts.post_status = 'wc-active'))
OR ( ( simp_postmeta.meta_key = '_schedule_end' AND simp_postmeta.meta_value > '2018-09-01 23:33:51' AND simp_posts.post_status = 'wc-pending-cancel'))

问题3：

SELECT COUNT(simp_posts.ID)
FROM simp_posts
INNER JOIN simp_postmeta
ON ( simp_posts.ID = simp_postmeta.post_id )
WHERE ( ( simp_postmeta.meta_key = '_schedule_end' AND simp_postmeta.meta_value = '0' AND simp_posts.post_status = 'wc-active'))
OR ( ( simp_postmeta.meta_key = '_schedule_end' AND simp_postmeta.meta_value > '2018-10-01 23:33:51' AND simp_posts.post_status = 'wc-active'))
OR ( ( simp_postmeta.meta_key = '_schedule_end' AND simp_postmeta.meta_value > '2018-10-01 23:33:51' AND simp_posts.post_status = 'wc-pending-cancel'))

我想这些查询被合并成一个，并有一个结果是3行与每个计数的结果在一个。例如，我会得到：182 183 194
谢谢你的帮助

sql mysql Database mariadb relational-database

来源：https://stackoverflow.com/questions/51685981/multiple-mysql-count-in-a-single-query

2条答案

按热度按时间

qlckcl4x1#

在mysql中，可以使用 SUM() 带布尔值。
你的逻辑很复杂。我认为可以归结为：

SELECT SUM((pm.meta_value = '0' AND p.post_status = 'wc-active') or pm.meta_value > '2018-09-01 23:33:51'),
       SUM((pm.meta_value = '0' AND p.post_status = 'wc-active') or pm.meta_value > '2018-10-01 23:33:51'),
SUM((pm.meta_value = '0' AND p.post_status = 'wc-active') or pm.meta_value > '2018-10-01 23:33:51')
FROM simp_posts p INNER JOIN
     simp_postmeta pm
     ON p.ID = pm.post_id
WHERE pm.meta_key = '_schedule_end' AND
      p.post_status IN ('wc-active', 'wc-pending-cancel')

赞(0）回复(0）举报 2021-06-20

wvt8vs2t2#

有共同的条件 where 并在日期范围内有条件地进行聚合。注意@tim在评论中提到的内容。如果计数必须相互排斥，则包括 meta_value 在 case 表情。

SELECT COUNT(CASE WHEN simp_postmeta.meta_value > '2018-08-01 23:33:51' 
                  THEN simp_posts.ID END)
,COUNT(CASE WHEN simp_postmeta.meta_value > '2018-09-01 23:33:51' 
            THEN simp_posts.ID END)
,COUNT(CASE WHEN simp_postmeta.meta_value > '2018-10-01 23:33:51' 
            THEN simp_posts.ID END)
FROM simp_posts
INNER JOIN simp_postmeta ON simp_posts.ID = simp_postmeta.post_id
WHERE simp_postmeta.meta_key = '_schedule_end' 
AND simp_posts.post_status IN ('wc-active','wc-pending-cancel')

赞(0）回复(0）举报 2021-06-20

我来回答

一个查询中的多个mysql计数

2条答案

相关问题

热门标签

最新问答