查询两个表并根据值创建列以启用排序

oiopk7p5 于 2021-06-20 发布在 Mysql

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

我在wp\u post和wp\u post\u meta wp数据库中有数据：一个包含“workshops”的唯一条目，第二个包含与每个workshop相关的元数据。表格如下所示：

WP_POST TABLE

| post ID   | post title    | post type | post content etc.
|---------------------------------------------------------
|   1       | workshop 1    | workshop  | cooking with apples
|   2       | workshop 2    | workshop  | cooking with pears
|   3       | workshop 3    | workshop  | cooking with bananas

WP META TABLE

| post ID   | meta key      | meta value    | etc.
|---------------------------------------------------------
|    1      | summary       | apples        | ..
|    1      | date          | 20100518      | ..
|    1      | duration      | 1 hour        | ..
|    2      | summary       | pears         | ..
|    2      | date          | 20100623      | ..
|    2      | duration      | 1/2 day       | ..
|    3      | summary       | bananas       | ..
|    3      | date          | 20101111      | ..
|    3      | duration      | all day       | ..

我一直在胡闹。

SELECT post_title, post_date, post_type, meta_key, meta_value, post_content
FROM wp_posts as post INNER JOIN wp_postmeta as meta ON post.ID = meta.post_id 
WHERE post.post_status = "publish" AND post.post_type = "workshop"

这个查询给了我一个如下所示的表。

| post ID   | post title    | post type     | meta key      | meta value    | etc.
|--------------------------------------------------------------------------------
|    1      | workshop 1    | workshop      | summary       | apples        | ..
|    1      | workshop 1    | workshop      | date          | 20100518      | ..
|    1      | workshop 1    | workshop      | duration      | 1 hour        | ..
|    2      | workshop 2    | workshop      | summary       | pears         | ..
|    2      | workshop 2    | workshop      | date          | 20100623      | ..
|    2      | workshop 2    | workshop      | duration      | 1/2 day       | ..
|    3      | workshop 3    | workshop      | summary       | bananas       | ..
|    3      | workshop 3    | workshop      | date          | 20101111      | ..
|    3      | workshop 3    | workshop      | duration      | all day       | ..

我需要做的是对meta键值“date”排序，但我不确定最好的方法。我可以按“meta key”日期的“meta value”排序吗？
我想我需要创建一个这样的表，这样才有可能，但不知道如何做到这一点，或者即使这是必要的方法。

| post ID   | post title    | summary   | date      | duration | etc.
|--------------------------------------------------------------
|   1       | workshop 1    | apples    | 20100518  | 1 hour
|   2       | workshop 2    | pears     | 20100623  | 1/2 day
|   3       | workshop 3    | bananas   | 20101111  | all day

sql mysql wordpress

来源：https://stackoverflow.com/questions/51180345/query-two-tables-and-create-columns-from-values-to-enable-sort

2条答案

按热度按时间

3pvhb19x1#

您可以使用聚合在不同的列中为每个post-like显示元数据信息

SELECT p.post_title, 
  p.post_date,
  p.post_type,
  p.post_content,
  MAX(CASE WHEN m.meta_key = 'summary' THEN m.meta_value ELSE NULL END) summary,
  MAX(CASE WHEN m.meta_key = 'duration' THEN m.meta_value ELSE NULL END) duration,
  MAX(CASE WHEN m.meta_key = 'date' THEN m.meta_value ELSE NULL END) `date`
FROM wp_posts as p
INNER JOIN wp_postmeta as m ON p.ID = m.post_id 
WHERE p.post_type = "workshop" AND p.post_status = "publish"
GROUP BY p.post_title, 
  p.post_date,
  p.post_type,
  p.post_content
ORDER BY STR_TO_DATE(MAX(CASE WHEN m.meta_key = 'date' THEN m.meta_value ELSE NULL END), '%Y%m%d')

要使用元数据值对数据集进行排序，首先需要使用 STR_TO_DATE 演示

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

xzv2uavs2#

只需使用 join :

select p.*, d.meta_value as date
from wp_post p join
     wp_meta d
     on d.post_id = p.post_id and d.meta_key = 'date'
order by d.meta_value;

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

我来回答

查询两个表并根据值创建列以启用排序

2条答案

相关问题

热门标签

最新问答