mysql - SQL 和 CASE 语句

Question

由于语法/编写方法不正确，以下查询显然无法运行。

有人可以帮我纠正这个问题吗？

我正在尝试执行以下操作，

SELECT COALESCE(t1.O_UPDATE,0) AS O_UP, COALESCE(t2.O_READ,0) AS O_RE, COALESCE(t3.OR_READ,0) AS OR_RD, COALESCE(t4.OR_U_READ,0) AS OR_U_RD 
 from the inner SELECT statements & then pass them to a CASE statement to set values for various variables ( This I think is evident from the code logic) 

SELECT TV_DATE, 
        CASE
            WHEN t5.O_UP BETWEEN 1 AND 3 THEN O_WT=5
            WHEN t5.O_RE BETWEEN 3 AND 5 THEN O_RD_WT = 6
            WHEN t5.OR_RD BETWEEN 5 AND 10 THEN 5 OR_RD_WT = 4
            WHEN t5.OR_U_RD BETWEEN 20 AND 30 THEN  OR_U_RD_WT = 3
            ELSE O_WT=0, O_RD_WT=0,OR_RD_WT=0, OR_U_RD_WT=0
FROM
    (
        SELECT COALESCE(t1.O_UPDATE,0) AS O_UP, COALESCE(t2.O_READ,0) AS O_RE, COALESCE(t3.OR_READ,0) AS OR_RD, COALESCE(t4.OR_U_READ,0) AS OR_U_RD FROM
        (SELECT SUM(TV_R_U_COUNT) as O_UPDATE from TVS where TV_T_NAME='abcd' AND TV_DATE between '2015-01-01' and '2015-07-01' AND TV_R_U='U' AND TV_I_NAME LIKE 'w1%we%' AND TV_I_NAME like 'we%avd%') t1,
        (SELECT SUM(TV_R_U_COUNT) as O_READ from TVS where TV_T_NAME='abcd' AND TV_DATE between '2015-01-01' and '2015-07-01' AND TV_R_U='X' AND TV_I_NAME LIKE 'w1%we%' AND TV_I_NAME like 'er%dfr%') t2,
        (SELECT SUM(TV_R_U_COUNT) as OR_READ from TVS where TV_T_NAME='abcd' AND TV_DATE between '2015-01-01' and '2015-07-01' AND TV_R_U='X' AND TV_I_NAME LIKE 'w1%we%' AND TV_I_NAME like 'cv%dfr%' AND TV_I_NAME NOT LIKE 'dsf%er%') t3,
        (SELECT SUM(TV_R_U_COUNT) as OR_U_READ from TVS where TV_T_NAME='abcd' AND TV_DATE between '2015-01-01' and '2015-07-01' AND TV_R_U='X' AND TV_I_NAME LIKE 'w1%we%' AND TV_I_NAME like 'ad%asd%ere') t4
    ) t5 TVS 
GROUP BY TV_DATE

+-------------+------+---------+----------+------------+------------+
|    DATE     | O_WT | O_RD_WT | OR_RD_WT | OR_U_RD_WT | OR_U_RD_WT |
+-------------+------+---------+----------+------------+------------+
| 2015-01-01  |    5 |       6 |        4 |          3 |          0 |
| 2015-01-02  |    5 |       0 |        0 |          3 |          0 |
| 2015-01-03  |    5 |       0 |        0 |          0 |          0 |
| 2015-01-04  |    5 |       6 |        4 |          0 |          0 |
| 2015-01-05  |    5 |       0 |        0 |          3 |          0 |
+-------------+------+---------+----------+------------+------------+

如果有任何帮助，我将不胜感激。

Answer 1

使用单独的case语句语句。试试这个

SELECT TV_DATE, 
        CASE WHEN t5.O_UP BETWEEN 1 AND 3 THEN O_WT=5 ELSE O_WT=0 END as O_WT,
        CASE WHEN t5.O_RE BETWEEN 3 AND 5 THEN O_RD_WT = 6 ELSE O_RD_WT=0 END as O_RD_WT,
        CASE WHEN t5.OR_RD BETWEEN 5 AND 10 THEN 5 OR_RD_WT = 4 ELSE OR_RD_WT=0 END as OR_RD_WT,
        CASE WHEN t5.OR_U_RD BETWEEN 20 AND 30 THEN  OR_U_RD_WT = 3 ELSE OR_U_RD_WT=0 END as OR_U_RD_WT 

你的整个查询将像这样

SELECT TV_DATE, 
        CASE WHEN t5.O_UP BETWEEN 1 AND 3 THEN O_WT=5 ELSE O_WT=0 END as O_WT,
            CASE WHEN t5.O_RE BETWEEN 3 AND 5 THEN O_RD_WT = 6 ELSE O_RD_WT=0 END as O_RD_WT,
            CASE WHEN t5.OR_RD BETWEEN 5 AND 10 THEN 5 OR_RD_WT = 4 ELSE OR_RD_WT=0 END as OR_RD_WT,
            CASE WHEN t5.OR_U_RD BETWEEN 20 AND 30 THEN  OR_U_RD_WT = 3 ELSE OR_U_RD_WT=0 END as OR_U_RD_WT 
FROM
    (
        SELECT COALESCE(t1.O_UPDATE,0) AS O_UP, COALESCE(t2.O_READ,0) AS O_RE, COALESCE(t3.OR_READ,0) AS OR_RD, COALESCE(t4.OR_U_READ,0) AS OR_U_RD FROM
        (SELECT SUM(TV_R_U_COUNT) as O_UPDATE from TVS where TV_T_NAME='abcd' AND TV_DATE between '2015-01-01' and '2015-07-01' AND TV_R_U='U' AND TV_I_NAME LIKE 'w1%we%' AND TV_I_NAME like 'we%avd%') t1,
        (SELECT SUM(TV_R_U_COUNT) as O_READ from TVS where TV_T_NAME='abcd' AND TV_DATE between '2015-01-01' and '2015-07-01' AND TV_R_U='X' AND TV_I_NAME LIKE 'w1%we%' AND TV_I_NAME like 'er%dfr%') t2,
        (SELECT SUM(TV_R_U_COUNT) as OR_READ from TVS where TV_T_NAME='abcd' AND TV_DATE between '2015-01-01' and '2015-07-01' AND TV_R_U='X' AND TV_I_NAME LIKE 'w1%we%' AND TV_I_NAME like 'cv%dfr%' AND TV_I_NAME NOT LIKE 'dsf%er%') t3,
        (SELECT SUM(TV_R_U_COUNT) as OR_U_READ from TVS where TV_T_NAME='abcd' AND TV_DATE between '2015-01-01' and '2015-07-01' AND TV_R_U='X' AND TV_I_NAME LIKE 'w1%we%' AND TV_I_NAME like 'ad%asd%ere') t4
    ) t5 TVS