时间戳

首页 > 解决方案 > 如何在 SQL 查询的计算中使用 IF 语句

mysql - 如何在 SQL 查询的计算中使用 IF 语句

问题描述

我使用以下查询给我我们的销售代理赚取的佣金。

SELECT * FROM 
    (SELECT agent, 
    COUNT(*) as sales, 
        (4*(SELECT COUNT(*) 
        FROM Sales Sales2 
        WHERE Sales2.agent=Sales.agent AND finalized_at BETWEEN '2020-11-01 00:00:00' AND '2020-11-27 23:59:59' AND flow=117)) 
        / 
        (SELECT SUM(uren) FROM Uren WHERE datum BETWEEN '2020-11-01 00:00:00' AND '2020-11-27 23:59:59' AND agent=Sales.agent) as sph   
    FROM Sales 
        WHERE finalized_at BETWEEN '2020-11-27 00:00:00' AND '2020-11-27 23:59:59' AND flow=117 
        GROUP BY agent ) r 
    ORDER BY sales * sph desc;

现在我希望输出按(sales * sph * factor). 如果因子为 10 sph > 1.5，否则为 7.5。

是否可以在查询中实现这一点。如果是，我该如何实现？

如果计算将在SELECT语句中完成(sales * sph * factor) as commission，然后只使用SORT BY commission.

我希望我的问题有意义。

标签： mysqlsql

解决方案

您可以按顺序使用条件，例如使用 case when

    SELECT * 
    FROM  (
        SELECT agent, 
        COUNT(*) as sales, 
            (4*(SELECT COUNT(*) 
            FROM Sales Sales2 
            WHERE Sales2.agent=Sales.agent AND finalized_at BETWEEN '2020-11-01 00:00:00' AND '2020-11-27 23:59:59' AND flow=117)) 
            / 
            (SELECT SUM(uren) FROM Uren WHERE datum BETWEEN '2020-11-01 00:00:00' AND '2020-11-27 23:59:59' AND agent=Sales.agent) as sph   
        FROM Sales 
            WHERE finalized_at BETWEEN '2020-11-27 00:00:00' AND '2020-11-27 23:59:59' AND flow=117 
            GROUP BY agent ) r 
        ORDER BY sales * sph  * (case when  sph > 1.5 then 10 else 7.5 end ) desc;

推荐阅读