mySQL 查询键值对

2021-09-28 00:00:00 mysql wordpress

我正在对 wordpress 表 (postmeta) 进行查询.该表有键和值，我需要一个查询来获取所有匹配key1"等于value1"和key2"等于value2"的行，按value2排序

I am doing a query on a wordpress table (postmeta). The table has keys and values and I need a query that will get all rows that match "key1" equal to "value1" and "key2" equal to "value2" ordered by value2

该表基本上有 id、postid、key 和 value 列.

The table basically has an id, postid, key and value columns.

我什至不知道从哪里开始.我可以找到一个很好的值，即 ... where key='featured' &值=真.但我需要按行的值排序的前 25 个，其中 key='hits' 意味着我需要这些特色行的相应命中键的值

I am not sure even where to start. I can find one value fine ie ... where key='featured' & value=true. But I need the top 25 ordered by the value of the rows where key='hits' meaning I need the value of the corresponding hits key for those featured rows

我不知道该怎么做.

TIA

推荐答案

根据您提供的有限详细信息，很难确切说明如何执行此操作.但是当您想返回键/值对时，您可以使用以下方法.

It is difficult to say exactly how to do this with the limited details that you provided. But when you want to return key/value pairs you can use the following.

您可以多次加入您的桌子:

You can join on your table multiple times:

select p1.postid,
  p1.value Featured,
  p2.value Hits
from postmeta p1
left join postmeta p2
  on p1.postid = p2.postid
  and p2.key = 'hits'
where p1.key ='featured';

参见SQL Fiddle with Demo

或者你可以使用带有 CASE 表达式的聚合函数(使用 sum() 假设一个数值，你可以使用 max()/min() 用于字符串值:

Or you can use an aggregate function with a CASE expression (using sum() assumes a numeric value, you can use max()/min() for string values:

select postid,
  sum(case when `key` = 'featured' then value end) Featured,
  sum(case when `key` = 'hits' then value end) Hits
from postmeta
group by postid

参见SQL Fiddle with Demo

相关文章