MYSQL 上的多个 IF 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9648481/
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
Multiple IF statements on MYSQL
提问by John Nu?ez
I'm trying to Display somes values in my database result, I am using this code but I can not succeed:
我正在尝试在我的数据库结果中显示一些值，我正在使用此代码但我无法成功：
SELECT item_code, IF(category_code = 'HERR1', 'NO', 1) OR (category_code = 'COLN5', 'NO', 2) AS category_code, item_name, item_quantity FROM qa_items
EDIT :I Want to display for example:
编辑：我想显示例如：
If category_code = 'HERR1'
Display = 1
else if category_code = 'COLN5'
Display = 2
End If
If anyone has any idea, would greatly appreciate it
如果有人有任何想法，将不胜感激
回答by a1ex07
I'd rather use CASE:
我宁愿使用CASE：
SELECT item_code,
CASE category_code
WHEN 'HERR1' THEN 1
WHEN 'COLN5' THEN 2
ELSE 'NO'
END as category_code, item_name, item_quantity
FROM qa_items
But IFwill also work : IF(category_code='HERR1',1, IF(category_code='COLN5',2,'NO'))
但IF也将工作：IF(category_code='HERR1',1, IF(category_code='COLN5',2,'NO'))
回答by Mark Willis
You need to nest the if statements
您需要嵌套 if 语句
SELECT item_code, IF(category_code = 'HERR1', 'NO', IF(category_code = 'COLN5', 1, 2)) AS category_code, item_name, item_quantity FROM qa_items
Then the first if will fail and the nested if will evaluate
然后第一个 if 将失败，嵌套的 if 将评估
回答by nnichols
Is this what you were after?
这就是你追求的吗？
SELECT
item_code,
CASE category_code
WHEN 'HERR1' THEN 1
WHEN 'COLN5' THEN 2
ELSE 'NO'
END AS category_code,
item_name,
item_quantity
FROM qa_items
回答by Chetter Hummin
Try the following
尝试以下
SELECT item_code, CASE category_code WHEN 'HERR1' THEN 1 WHEN 'COLN5' THEN 0 ELSE 'NONE' END AS category_code, item_name, item_quantity FROM qa_items
回答by Chetter Hummin
In my 3 columns table, one is package_price, employee_percentage, reference_customer_id.
在我的 3 列表中，一个是package_price, employee_percentage, reference_customer_id.
Now I want if reference_customer_id > 0then employee percentage as referenced_commissionand if reference_customer_id = 0then direct commission. I tried below way:
现在我想要如果reference_customer_id > 0那么员工百分比referenced_commission和如果reference_customer_id = 0那么直接佣金。我尝试了以下方式：
SELECT if(reference_customer_id = 0, sum(((package_price*employee_percentage)/100)) , 0) as direct_commission, if(reference_customer_id > 0, sum(((package_price*employee_percentage)/100)) , 0) as reference_commission
回答by Siva
Kindly Use It Simple It Works.
请使用它简单有效。
SELECT if(reference_customer_id = 0,sum(((package_priceemployee_percentage)/100)),sum(((package_priceemployee_percentage)/100)))
as direct_commission
回答by Kumar harsh
You can try this. Use IF in select query and update the table you want ;)
你可以试试这个。在选择查询中使用 IF 并更新您想要的表；)
create table student(marks int,grade char);
创建表学生（marks int,grade char）；
insert into student values(200,null),(120,null), (130,null);
插入学生值(200,null),(120,null),(130,null);
UPDATE student a
INNER JOIN (select s.marks, IF(s.marks>=200,'A',IF(s.marks>=130,'B','P')) AS Gradefrom student s) b on a.marks= b.marks
SET a.Grade = b.Grade;
UPDATE student a INNER JOIN (select s.marks, IF(s.marks>=200,'A',IF(s.marks>=130,'B','P')) AS Gradefrom student s) b on a .marks= b.marks SET a.Grade = b.Grade;
