MySQL查询以根据自定义列名和值获取值

问题描述

我有两张这样的桌子

    wp_posts

  ID   post_date      post_title   post_type   post_status
  1    2020-12-10      ABC         institute   publish
  2    2020-12-16      DEG         institute   publish
  3    2020-12-11      XXY         institute   publish
  4    2020-12-12      ABX         institute   publish
  5    2020-12-24      ZYU         institute   publish
  6    2020-12-28      ANM         institute   publish
  7    2020-12-16      DDK         institute   publish
  8    2020-12-30      LKI         institute   publish
  9    2020-12-30      LKI         institute   publish
  10   2020-12-31      LKI         institute   publish
  11   2020-12-16      TGY         institute   publish
  12   2020-12-16      MUN         institute   publish

 wp_postmeta

meta_id  post_id   meta_key         meta_value
1          1       country_id           10
2          1       registartion_by      online
3          3       registartion_by      offline
4          4       country_id           100
5          3       country_id           100
6          4       registartion_by      online
7          5       registartion_by      online
8          7       registartion_by      offline
9          7       country_id           101
10         5       country_id           102
11         8       country_id           103
12         9       registartion_by      online
13         8       registartion_by      offline
14         9       country_id           10
15         10      country_id           104
16         10      registartion_by      offline
17         11      country_id           101
18         11      registartion_by      online

现在我想进行一些查询，以便最终输出应该像这样显示

Now I want to make some query so that the final output should be shown something like this

Date            Country   Offline_registration  Online_registartion
2020-12-10      10          0                      1
2020-12-11      100         1                      0
2020-12-12      100         0                      1
2020-12-16      101         1                      1
2020-12-24      102         0                      1
2020-12-30      103         0                      1
2020-12-31      104         1                      0

所以为了实现这一点，我做了一些这样的查询

So to achieve this I have made some query like this

SELECT date( posts.post_date ) as Date , postmeta.meta_value as Country
  FROM wp_posts as posts INNER JOIN wp_postmeta as postmeta on posts.ID = postmeta.post_id WHERE posts.post_type = 'institute' AND posts.post_status = 'publish' AND postmeta.meta_key = 'country_id' GROUP BY postmeta.meta_value

但是我无法获取注册类型计数的数据，即(在线/离线注册).那么有人可以告诉我如何实现这一目标吗?

But I can't get data of registration type count i.e(online/offline registration). So can someone tell me how to achieve that?

任何建议或建议都非常值得赞赏.谢谢.

Any suggestions or advice would be really appreciable.Thanks.

推荐答案

您可以通过加入 wp_postmeta 两次来做到这一点，一次针对国家/地区，一次针对注册:

You can do this by joining to wp_postmeta twice, once for the country and once for the registrations:

SELECT date(p.post_date) as Date, pmc.meta_value as Country,
       SUM( pmr.meta_value = 'online' ) as num_online,
       SUM( pmr.meta_value = 'offline' ) as num_offline
 FROM wp_posts p INNER JOIN
      wp_postmeta pmc
      ON pmc.post_id = p.id AND
         pmc.meta_key = 'country_id' LEFT JOIN
      wp_postmeta pmr
      ON pmc.post_id = p.id AND
         pmc.meta_key = 'registion_by'
WHERE p.post_type = 'institute' AND
      p.post_status = 'publish'
GROUP BY date, country;

这篇关于MySQL查询以根据自定义列名和值获取值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！