問題描述

我有兩張這樣的桌子

    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

現(xiàn)在我想進(jìn)行一些查詢，以便最終輸出應(yīng)該像這樣顯示

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

所以為了實現(xiàn)這一點，我做了一些這樣的查詢

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

但是我無法獲取注冊類型計數(shù)的數(shù)據(jù)，即(在線/離線注冊).那么有人可以告訴我如何實現(xiàn)這一目標(biāo)嗎?

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 兩次來做到這一點，一次針對國家/地區(qū)，一次針對注冊:

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;

這篇關(guān)于MySQL查詢以根據(jù)自定義列名和值獲取值的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！