問題描述
好吧，我想我可能在這里忽略了一些明顯/簡(jiǎn)單的東西...但我需要編寫一個(gè)查詢，該查詢僅返回與同一列上的多個(gè)條件匹配的記錄...
Ok, I think I might be overlooking something obvious/simple here... but I need to write a query that returns only records that match multiple criteria on the same column...
我的表是一個(gè)非常簡(jiǎn)單的鏈接設(shè)置，用于將標(biāo)志應(yīng)用到用戶......
My table is a very simple linking setup for applying flags to a user ...
ID contactid flag flag_type
-----------------------------------
118 99 Volunteer 1
119 99 Uploaded 2
120 100 Via Import 3
121 100 Volunteer 1
122 100 Uploaded 2
等...在這種情況下，您會(huì)看到聯(lián)系人 99 和 100 都被標(biāo)記為志愿者"和已上傳"...
etc... in this case you'll see both contact 99 and 100 are flagged as both "Volunteer" and "Uploaded"...
我需要能夠做的是僅返回那些與通過搜索表單輸入的多個(gè)條件匹配的 contactid...contactid 必須匹配所有選擇的標(biāo)志...在我的腦海中，SQL 應(yīng)該類似于:
What I need to be able to do is return those contactid's ONLY that match multiple criteria entered via a search form...the contactid's have to match ALL chosen flags... in my head the SQL should look something like:
SELECT contactid
WHERE flag = 'Volunteer'
AND flag = 'Uploaded'...
但是……什么也沒有返回……我在這里做錯(cuò)了什么?
but... that returns nothing... What am I doing wrong here?
推薦答案
你可以使用 GROUP BY 和 HAVING COUNT(*) = _:
SELECT contact_id
FROM your_table
WHERE flag IN ('Volunteer', 'Uploaded', ...)
GROUP BY contact_id
HAVING COUNT(*) = 2 -- // must match number in the WHERE flag IN (...) list
(假設(shè) contact_id, flag 是唯一的).
(assuming contact_id, flag is unique).
或者使用連接:
SELECT T1.contact_id
FROM your_table T1
JOIN your_table T2 ON T1.contact_id = T2.contact_id AND T2.flag = 'Uploaded'
-- // more joins if necessary
WHERE T1.flag = 'Volunteer'
如果標(biāo)志列表很長(zhǎng)并且有很多匹配項(xiàng)，第一個(gè)可能會(huì)更快.如果標(biāo)志列表很短并且匹配項(xiàng)很少，您可能會(huì)發(fā)現(xiàn)第二個(gè)更快.如果性能是一個(gè)問題，請(qǐng)嘗試對(duì)您的數(shù)據(jù)進(jìn)行測(cè)試，看看哪個(gè)效果最好.
If the list of flags is very long and there are lots of matches the first is probably faster. If the list of flags is short and there are few matches, you will probably find that the second is faster. If performance is a concern try testing both on your data to see which works best.
這篇關(guān)于在同一列上使用多個(gè) WHERE 條件進(jìn)行選擇的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！