MySQL 带有if语句的sql查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/742508/
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
sql query with if statment
提问by Brian Griffin
I am trying to come up with a query to report revenue. It will require 2 tables: clicks and offers. Revenue is calculated by the number of conversions * commission for the offer. Conversions are stored in the clicks table in a field called "conversionDate", and the commission for each offer is stored in the offers table.
我想提出一个查询来报告收入。它将需要 2 个表：点击和优惠。收入的计算方法是转化次数 * 优惠佣金。转换存储在点击表中名为“conversionDate”的字段中，每个报价的佣金存储在报价表中。
There needs to be a condition in the query to ignore any clicks that did not convert (meaning conversionDate is NULL) when adding up revenue for the offer.
查询中需要有一个条件来忽略所有未转换的点击（意味着conversionDate 为NULL），在为优惠添加收入时。
What I've got needs a bit of tweaking as it is not giving the correct value for revenue:
我所拥有的需要一些调整，因为它没有给出正确的收入价值：
SELECT o.name offer, count(c.id) clicks, if(not isnull(c.conversionDate), revenue=revenue+o.commission, revenue) revenue FROM clicks c, offers o where c.offerID=o.ID GROUP BY o.ID;
SELECT o.name offer, count(c.id) clicks, if(not isnull(c.conversionDate), Revenue=revenue+o.commission, Revenue) 从点击c,offers o where c.offerID=o.ID GROUP通过o.ID；
I have 3 dummy records in clicks right now, 2 of which are conversions. With the commission set to 1, revenue should be 2. The result I am getting is 1. Am I on the right track or should the revenue calculation be some kind of subquery or what?
我现在有 3 条虚拟记录点击次数，其中 2 条是转化。佣金设置为 1，收入应该是 2。我得到的结果是 1。我是在正确的轨道上还是收入计算应该是某种子查询或什么？
回答by Bill Karwin
I'd write the query this way:
我会这样写查询：
SELECT o.name AS offer, COUNT(c.id) AS clicks,
SUM( IF(c.conversionDate IS NOT NULL, o.commission, NULL) ) AS revenue
FROM offers o JOIN clicks c ON (c.offerID=o.ID)
GROUP BY o.ID;
Here's another solution, but offers that don't have anyconverted clicks are not shown in the query result:
这是另一种解决方案，但查询结果中不会显示没有任何转化点击次数的优惠：
SELECT o.name AS offer, COUNT(c.id) AS clicks,
SUM(o.commission) AS revenue
FROM offers o JOIN clicks c
ON (c.offerID=o.ID AND c.conversionDate IS NOT NULL)
GROUP BY o.ID;
回答by Steven A. Lowe
move the null check to the WHERE clause
将空检查移至 WHERE 子句
回答by Gumbo
Try this query:
试试这个查询：
SELECT o.name offer, COUNT(c.id) clicks, IF(c.conversionDate IS NULL, revenue + o.commission, revenue) revenue
FROM clicks c, offers o
WHERE c.offerID=o.ID
GROUP BY o.ID;
回答by Steve Willcock
SELECT o.name offer, count(*) clicks, (COUNT(c.ID) * o.commission) revenue
FROM clicks c, offers o
WHERE c.ConversionDate is not null and c.offerID=o.ID
GROUP BY o.ID, o.name, o.commission;
