如何将SQL查询分配给变量(num & den)，然后再对它们进行划分？

Question

我必须将SQL查询分配给分子和分母，然后再对结果进行除法。所需的逻辑如下：

数字：

    Study Count where reliability[flag]=1 
    AND reliability[status]="On Time" 
    OR reliability[status] = "Planned On Time"

Den：

    Study Count where reliability[flag]=1

(Num/Den)/100

Answer 1

您可以使用case语句查找满足每个条件的计数，然后在一个查询中应用该除法，如下所示

SELECT SUM(CASE WHEN (reliability_flag = 1 AND reliability_status = 'On Time') OR reliability_status = "Planned On Time"
                      THEN 1
                ELSE 0
           END) * 100.0
       /
       SUM(CASE WHEN reliability_flag = 1 THEN 1
                ELSE 0
           END))
FROM table_name

或者，您可以使用两个独立的查询分别查找计数，并将两个查询的结果除以找到最终结果。

SELECT
   (SELECT COUNT(*)
    FROM table_name
    WHERE (reliability_flag = 1 AND reliability_status = 'On Time')
        OR reliability_status = "Planned On Time") * 100.0
   /
   (SELECT COUNT(*)
    FROM table_name
    WHERE reliability_flag = 1)