时间戳

首页 > 解决方案 > 使用 sql 中的 case 语句填充的三列的总和

mysql - 使用 sql 中的 case 语句填充的三列的总和

问题描述

我需要得到 devicePoint、osPoint、browserPoint 的总和，这些总和是在 sql 中的 select 中使用 case 语句填充的。这是我的代码

SELECT *,
    CASE
        WHEN device='Default' THEN 1
        ELSE 5
    END AS devicePoint,
    CASE
        WHEN operating_system='Default' THEN 1
        ELSE 3
    END AS osPoint,
    CASE
        WHEN browser='Default' THEN 1
        ELSE 2
    END AS browserPoint,
    (devicePoint + osPoint + browserPoint) as 'Total'
FROM mytable
WHERE 
 (
    (device = 'Desktop' AND operating_system='Ios' AND browser='Firefox')
    OR
    (device = 'Desktop' AND operating_system='Ios' AND browser='Default')
    OR 
    (device = 'Desktop' AND operating_system='Default' AND browser='Firefox')
    OR 
    (device = 'Desktop' AND operating_system='Default' AND browser='Default')
    OR 
    (device = 'Default' AND operating_system='Ios' AND browser='Default')
    OR 
    (device = 'Default' AND operating_system='Default' AND browser='Firefox')
    OR 
    (device = 'Default' AND operating_system='Default' AND browser='Default')
)

标签： mysqlsql

解决方案

使用子查询：

SELECT
    devicePoint,
    osPoint,
    browserPoint,
    (devicePoint + osPoint + browserPoint) AS Total
FROM
(
    SELECT *,
        CASE WHEN device = 'Default' THEN 1 ELSE 5 END AS devicePoint,
        CASE WHEN operating_system = 'Default' THEN 1 ELSE 3 END AS osPoint,
        CASE WHEN browser = 'Default' THEN 1 ELSE 2 END AS browserPoint
    FROM mytable
    WHERE 
        (device = 'Desktop' AND operating_system='Ios' AND browser='Firefox') OR
        (device = 'Desktop' AND operating_system='Ios' AND browser='Default') OR
        (device = 'Desktop' AND operating_system='Default' AND browser='Firefox') OR
        (device = 'Desktop' AND operating_system='Default' AND browser='Default') OR
        (device = 'Default' AND operating_system='Ios' AND browser='Default') OR
        (device = 'Default' AND operating_system='Default' AND browser='Firefox') OR
        (device = 'Default' AND operating_system='Default' AND browser='Default')
) t;

不可能在SELECT定义它们的地方引用您的三个别名。但是，如果您使用上面的子查询方法，它们是可用的。

推荐阅读