問題描述
我正在對 wordpress 表 (postmeta) 進(jìn)行查詢.該表有鍵和值，我需要一個查詢來獲取所有匹配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' 意味著我需要這些特色行的相應(yīng)命中鍵的值
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
推薦答案
根據(jù)您提供的有限詳細(xì)信息，很難確切說明如何執(zhí)行此操作.但是當(dāng)您想返回鍵/值對時，您可以使用以下方法.
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 表達(dá)式的聚合函數(shù)(使用 sum() 假設(shè)一個數(shù)值，你可以使用 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
這篇關(guān)于mySQL 查詢鍵值對的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！