将小型查询合并为大型mysql查询

Question

我遇到了一个问题，在同一查询中向不同的表获取4个SQL请求。查询为：

SELECT COUNT(*) AS posts FROM `wp_posts` WHERE post_type='updates' AND post_status='publish'

和

SELECT COUNT(*) AS posts FROM `wp_posts` WHERE post_type='post' AND post_status='publish'

wp_posts表结构为：

|  ID  |  title  |  post_type  |  post_status  |
------------------------------------------------
|   1  |   Name  |   updates   |    publish    |
|   2  |  Name 2 |     post    |    publish    |

另一个请求：

SELECT COUNT(*) AS users FROM wp_usermeta WHERE wp_usermeta.meta_value LIKE '%delegate%'

wp_usermeta表结构为：

|  ID  | user_id |    meta_key     |         meta_value         |
-----------------------------------------------------------------
|  29  |    4    | ei_capabilities | a:1:{s:13:"delegate";b:1;} |

最后一个：

SELECT wp_postmeta.meta_value AS version FROM wp_postmeta WHERE wp_postmeta.meta_key='content_version' AND post_id=1

wp_postmeta表结构为：

|  ID  |  post_id  |  title  |    meta_key     |  meta_value  |
---------------------------------------------------------------
|  21  |     1     |  Name   | content_version |       1      |

有没有办法把它们合并成一个请求？

Answer 1

前两个查询相似，所以我将wp_posts作为主表。其余两个查询作为子查询。

  SELECT
    (SUM(CASE WHEN post_type='updates'  AND  THEN 1 ELSE 0 END) ) as col1,
    (SUM(CASE WHEN post_type='post'  AND post_type='post' THEN 1 ELSE 0 END) ) as col2,
  (SELECT COUNT(*) AS users FROM wp_usermeta WHERE wp_usermeta.meta_value LIKE '%delegate%') AS col3,
  (SELECT wp_postmeta.meta_value AS version FROM wp_postmeta WHERE wp_postmeta.meta_key='content_version' AND post_id=1 LIMIT 1) as col4
  FROM
  wp_posts
  WHERE post_status='publish'

希望这能帮助

Answer 2

试试这个

   select(
    select 
    sum(case when post_type='updates' AND post_status='publish' then 1 else 0 end) from wp_posts) as '1Query',
    (select
sum(case when post_type='post' AND post_status='publish' then 1 else 0 end ) as '2Query'
    from wp_posts),
    (select 
    SUM(CASE WHEN wp_usermeta.meta_value LIKE '%delegate%' THEN 1 ELSE 0 END) AS users 
    FROM wp_usermeta) as Users,
    (
    SELECT wp_postmeta.meta_value AS version FROM wp_postmeta WHERE wp_postmeta.meta_key='content_version' AND post_id=1)as Version