带有if语句的sql查询 [英] sql query with if statment

问题描述

我正在尝试提出查询以报告收入.它将需要2个表格:点击次数和报价.收入是根据转化次数*要约的佣金来计算的.转化次数存储在clicks表中名为"conversionDate"的字段中，每个报价的佣金都存储在offers表中.

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.

查询中必须有一个条件，当为要约增加收入时，忽略所有未转化的点击(意味着conversionDate为NULL).

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.

我得到的东西需要一些调整，因为它没有提供正确的收入价值:

What I've got needs a bit of tweaking as it is not giving the correct value for revenue:

选择o.name报价，计数(c.id)点击，如果(非非空(c.conversionDate)，收益=收入+ o.commission，收益)收益来自点击c，则报价o，其中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) revenue FROM clicks c, offers o where c.offerID=o.ID GROUP BY o.ID;

我现在在点击中有3条虚拟记录，其中2条是转化.在佣金设置为1的情况下，收入应为2.我得到的结果是1.我是在正确的轨道上吗?或者收入计算应该是某种子查询还是什么?

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?

推荐答案

我将以这种方式编写查询:

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 any converted 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;

这篇关于带有if语句的sql查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！