MySQL 多条件选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16249731/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
MySQL select query with multiple conditions
提问by Mangesh Narayankar
I have written a MySQL query but it doesn't seem to be working, because the result is empty. Is there any mistake in my code?
我写了一个 MySQL 查询，但它似乎不起作用，因为结果是空的。我的代码有什么错误吗？
$result = mysql_query(
"SELECT user_id
FROM wp_usermeta
WHERE
(meta_key = 'first_name' AND meta_value = '$us_name') AND
(meta_key = 'yearofpassing' AND meta_value = '$us_yearselect') AND
(meta_key = 'u_city' AND meta_value = '$us_reg') AND
(meta_key = 'us_course' AND meta_value = '$us_course')"
);
回答by fthiella
I would use this query:
我会使用这个查询：
SELECT
user_id
FROM
wp_usermeta
WHERE
(meta_key = 'first_name' AND meta_value = '$us_name') OR
(meta_key = 'yearofpassing' AND meta_value = '$us_yearselect') OR
(meta_key = 'u_city' AND meta_value = '$us_reg') OR
(meta_key = 'us_course' AND meta_value = '$us_course')
GROUP BY
user_id
HAVING
COUNT(DISTINCT meta_key)=4
this will select all user_idthat meets all four conditions.
这将选择user_id满足所有四个条件的所有内容。
回答by Kovge
@fthiella 's solution is very elegant.
@fthiella 的解决方案非常优雅。
If in future you want show more than user_idyou could use joins, and there in one line could be all data you need.
如果将来您想要显示的内容超过user_id使用连接的能力，那么一行中可能包含您需要的所有数据。
If you want to use ANDconditions, and the conditions are in multiple lines in your table, you can use JOINSexample:
如果要使用AND条件，并且条件在表中的多行中，则可以使用JOINS示例：
SELECT `w_name`.`user_id`
FROM `wp_usermeta` as `w_name`
JOIN `wp_usermeta` as `w_year` ON `w_name`.`user_id`=`w_year`.`user_id`
AND `w_name`.`meta_key` = 'first_name'
AND `w_year`.`meta_key` = 'yearofpassing'
JOIN `wp_usermeta` as `w_city` ON `w_name`.`user_id`=`w_city`.user_id
AND `w_city`.`meta_key` = 'u_city'
JOIN `wp_usermeta` as `w_course` ON `w_name`.`user_id`=`w_course`.`user_id`
AND `w_course`.`meta_key` = 'us_course'
WHERE
`w_name`.`meta_value` = '$us_name' AND
`w_year`.meta_value = '$us_yearselect' AND
`w_city`.`meta_value` = '$us_reg' AND
`w_course`.`meta_value` = '$us_course'
Other thing: Recommend to use prepared statements, because mysql_*functions is not SQL injection save, and will be deprecated.
If you want to change your code the less as possible, you can use mysqli_functions:
http://php.net/manual/en/book.mysqli.php
其他：推荐使用prepared statement，因为mysql_*函数不是SQL注入保存，会被弃用。如果你想尽可能少地改变你的代码，你可以使用mysqli_函数：http:
//php.net/manual/en/book.mysqli.php
Recommendation:
推荐：
Use indexes in this table. user_idhighly recommend to be and index, and recommend to be the meta_keyAND meta_valuetoo, for faster run of query.
使用此表中的索引。user_id强烈建议使用和索引，也建议使用meta_keyAND meta_value，以便更快地运行查询。
The explain:
解释：
If you use ANDyou 'connect' the conditions for one line. So if you want AND condition for multiple lines, first you must create one line from multiple lines, like this.
如果您使用AND“连接”一条线路的条件。所以如果你想要多行的 AND 条件，首先你必须从多行创建一行，像这样。
Tests: Table Data:
测试：表数据：
PRIMARY INDEX
int varchar(255) varchar(255)
/ \ |
+---------+---------------+-----------+
| user_id | meta_key | meta_value|
+---------+---------------+-----------+
| 1 | first_name | Kovge |
+---------+---------------+-----------+
| 1 | yearofpassing | 2012 |
+---------+---------------+-----------+
| 1 | u_city | GaPa |
+---------+---------------+-----------+
| 1 | us_course | PHP |
+---------+---------------+-----------+
The result of Query with $us_name='Kovge'$us_yearselect='2012'$us_reg='GaPa', $us_course='PHP':
与查询的结果$us_name='Kovge'$us_yearselect='2012'$us_reg='GaPa'，$us_course='PHP'：
+---------+
| user_id |
+---------+
| 1 |
+---------+
So it should works.
所以它应该有效。
回答by Adam Falchetta
also you can use "AND" instead of "OR" if you want both attributes to be applied.
如果您希望应用这两个属性，也可以使用“AND”而不是“OR”。
select * from tickets where (assigned_to='1') and (status='open') order by created_at desc;
回答by Gaurav Gupta
Lets suppose there is a table with following describe command for table (hello)- name char(100), id integer, count integer, city char(100).
假设有一个表，表（hello）的描述命令如下 - name char(100), id integer, count integer, city char(100)。
we have following basic commands for MySQL -
我们有以下 MySQL 的基本命令 -
select * from hello;
select name, city from hello;
etc
select name from hello where id = 8;
select id from hello where name = 'GAURAV';
now lets see multiple where condition -
现在让我们看看多个 where 条件 -
select name from hello where id = 3 or id = 4 or id = 8 or id = 22;
select name from hello where id =3 and count = 3 city = 'Delhi';
This is how we can use multiple where commands in MySQL.
这就是我们如何在 MySQL 中使用多个 where 命令。
回答by dave
You have conditions that are mutually exclusive - if meta_key is 'first_name', it can't also be 'yearofpassing'. Most likely you need your AND's to be OR's:
您有互斥的条件 - 如果 meta_key 是 'first_name'，它也不能是 'yearofpassing'。很可能你需要你的 AND 是 OR 的：
$result = mysql_query("SELECT user_id FROM wp_usermeta
WHERE (meta_key = 'first_name' AND meta_value = '$us_name')
OR (meta_key = 'yearofpassing' AND meta_value = '$us_yearselect')
OR (meta_key = 'u_city' AND meta_value = '$us_reg')
OR (meta_key = 'us_course' AND meta_value = '$us_course')")
