使用同一列上的多个 WHERE 条件进行选择

好吧，我想我可能忽略了一些显而易见/简单的东西... 但我需要编写一个查询，只返回匹配同一列上的多个条件的记录..。

我的表是一个非常简单的链接设置，用于向用户应用标志..。

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

等等，在这种情况下，你会看到联系人99和100都被标记为“志愿者”和“上传”..。

我需要能够做的是返回那些 contactid 的只匹配通过搜索表单输入的多个标准... contactid 的必须匹配所有选择的标志... 在我的头脑中的 SQL 应该是这样的:

SELECT contactid
WHERE flag = 'Volunteer'
AND flag = 'Uploaded'...

但是... 没有任何回报... 我做错了什么？

500036 次浏览
小开

can't really see your table, but flag cannot be both 'Volunteer' and 'Uploaded'. If you have multiple values in a column, you can use

WHERE flag LIKE "%Volunteer%" AND flag LIKE "%UPLOADED%"

not really applicable seeing the formatted table.

小开
最佳答案

You can either use GROUP BY and 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

(assuming contact_id, flag is unique).

Or use joins:

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'

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.

小开

Use:

  SELECT t.contactid
FROM YOUR_TABLE t
WHERE flag IN ('Volunteer', 'Uploaded')
GROUP BY t.contactid
HAVING COUNT(DISTINCT t.flag) = 2

The key thing is that the counting of t.flag needs to equal the number of arguments in the IN clause.

The use of COUNT(DISTINCT t.flag) is in case there isn't a unique constraint on the combination of contactid and flag -- if there's no chance of duplicates you can omit the DISTINCT from the query:

  SELECT t.contactid
FROM YOUR_TABLE t
WHERE flag IN ('Volunteer', 'Uploaded')
GROUP BY t.contactid
HAVING COUNT(t.flag) = 2
小开

Sometimes you can't see the wood for the trees :)

Your original SQL ..

SELECT contactid
WHERE flag = 'Volunteer'
AND flag = 'Uploaded'...

Should be:

SELECT contactid
WHERE flag = 'Volunteer'
OR flag = 'Uploaded'...
小开

AND will return you an answer only when both volunteer and uploaded are present in your column. Otherwise it will return null value...

try using OR in your statement ...

SELECT contactid  WHERE flag = 'Volunteer' OR flag = 'Uploaded'
小开

Try to use this alternate query:

SELECT A.CONTACTID
FROM (SELECT CONTACTID FROM TESTTBL WHERE FLAG = 'VOLUNTEER')A ,
(SELECT CONTACTID FROM TESTTBL WHERE FLAG = 'UPLOADED') B WHERE A.CONTACTID = B.CONTACTID;
小开 
SELECT contactid, Count(*)
FROM <YOUR_TABLE> WHERE flag in ('Volunteer','Uploaded')
GROUP BY contactid
HAVING count(*)>1;
小开 
select purpose.pname,company.cname
from purpose
Inner Join company
on purpose.id=company.id
where pname='Fever' and cname='ABC' in (
select mname
from medication
where mname like 'A%'
order by mname
);
小开

Change AND to OR. Simple mistake. Think of it like plain English, I want to select anything with that equals this or that.

小开

Consider using INTERSECT like this:

SELECT contactid WHERE flag = 'Volunteer'
INTERSECT
SELECT contactid WHERE flag = 'Uploaded'

I think it it the most logistic solution.

小开

Use this: For example:

select * from ACCOUNTS_DETAILS
where ACCOUNT_ID=1001
union
select * from ACCOUNTS_DETAILS
where ACCOUNT_ID=1002
小开

something like this should work for you

SELECT * FROM `product_options` GROUP BY product_id
HAVING COUNT(option_id IN (1,2,3) OR NULL) > 0 AND COUNT(option_id IN (7) OR NULL) > 0

提交答案