MySQL SELECT优先级(Mysql SELECT priority)

我的数据库缺少一些值：

Name Surname Country Salary John Walker USA 800 Walker Canada 1000 Peter Walker Canada 800 John Walker 900 Peter Farmer USA 1200 ... ... ...

我想选择名称和姓氏和国家，如果没有值，我想按名称和国家选择，如果没有值，我想按姓氏和国家选择。

我现在在使用：

SELECT Salary FROM table_name WHERE (Name='InputFromPHP' AND Surname='InputFromPHP' AND Country='InputFromPHP') OR (Name='InputFromPHP' AND Surname='InputFromPHP') OR (Name='InputFromPHP' AND Country='InputFromPHP')

我想优先考虑，所以它会精确地选择输入PHP，但是当数据库中存在多个值时，它会给出SQL错误。

多谢提前一百万。

My database is missing some values:

Name Surname Country Salary John Walker USA 800 Walker Canada 1000 Peter Walker Canada 800 John Walker 900 Peter Farmer USA 1200 ... ... ...

I want to SELECT Name and Surname and Country, if there are no values, I want to SELECT by Name and Country, if there are no values, I want to SELECT by Surname and Country.

I am using now:

I want to give priority, so it will select exactly what was Input With PHP, but when there are several values in database it gives SQL error.

Thanks a million in advance.

最满意答案

您可以使用order by和limit ：

SELECT Salary FROM table_name WHERE Name='InputFromPHP' AND (Surname='InputFromPHP' OR Country='InputFromPHP') ORDER BY CASE Surname WHEN 'InputFromPHP' THEN 0 ELSE 1 END, CASE Country WHEN 'InputFromPHP' THEN 0 ELSE 1 END LIMIT 1

请注意，您的where子句始终需要Name='InputFromPHP' ，以便可以从or子句中取出。

You could use order by and limit:

Note that your where clause always required Name='InputFromPHP', so that can be taken out of the or clause.

更多推荐