时间戳

首页 > 解决方案 > MySQL 查询以根据自定义列名和值获取值

mysql - 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

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

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

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

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

但我无法获得注册类型计数的数据，即（在线/离线注册）。那么有人可以告诉我如何实现这一目标吗？

任何建议或意见都会非常可观。谢谢。

标签： mysqlsqlwordpress

解决方案

您可以通过加入wp_postmeta两次来做到这一点，一次用于国家，一次用于注册：

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;

推荐阅读