mysql - 循环遍历表记录

Question

我有一个表格，其中包含姓名列表，如下所示

+----------+
| CLI_NAME |
+----------+
| A        |
| B        |
| C        |
| D        |
| E        |
+----------+

我想循环一下这个东西

for each value in CLI_NAME do
{
   bla bla
}

对于表 CLI_NAMES 中的每个值，我想循环并执行下面的代码。

从上表中选择的值需要作为 T_NAME 的参数，例如下面的查询中的 T_NAME = "abc"

INSERT INTO TABLE_2(DATE, O_WT)
SELECT 
    F_DATE,
    CASE 
        WHEN
            SUM(
                CASE
                    WHEN
                        F_NAME like 'ty%asd%'
                    THEN
                        F_PKEY
                END
            ) = 1 THEN 1
        WHEN
            SUM(
                CASE
                    WHEN
                        F_NAME like 'ty%asd%'
                    THEN
                        F_PKEY
                END

            ) = 2 THEN 2               
        WHEN
            SUM(
                CASE
                    WHEN
                        F_NAME like 'ty%asd%'
                    THEN
                        F_PKEY
                END
            ) BETWEEN 3 AND 5 THEN  3 
        WHEN
            SUM(
                CASE
                       WHEN
                        F_NAME like 'ty%asd%'
                    THEN
                        F_PKEY
                END

            ) > 5 THEN 5                          
            ELSE 0 
    END AS O_WT
FROM
    TABLE_1 
WHERE 
    F_NAME IN (
        SELECT I_NAME from I_WD WHERE I_I_ID IN (
            SELECT I_MAP_ID FROM T_T_MAP where T_MAP_ID = (
                SELECT T_ID FROM TWD WHERE T_NAME = 'abc'
            )
        )
    ) 
    AND F_DATE between '2015-05-01' and '2015-06-01' 
    AND F_NAME LIKE 'ty%pr%' 
        GROUP BY F_DATE

我该如何循环并执行此操作？

Answer 1

如果我理解正确，您可以使用CROSS JOIN做您想做的事情:

INSERT INTO TABLE_2(DATE, O_WT)
    SELECT t1.F_DATE,
           (CASE WHEN SUM(CASE WHEN t1.F_NAME like 'ty%asd%' THEN t1.F_PKEY END) = 1 THEN 1
                 WHEN SUM(CASE WHEN t1.F_NAME like 'ty%asd%' THEN t1.F_PKEY END) = 2 THEN 2
                 WHEN SUM(CASE WHEN t1.F_NAME like 'ty%asd%' THEN t1.F_PKEY END) BETWEEN 3 AND 5 THEN 3
                 WHEN SUM(CASE WHEN t1. F_NAME like 'ty%asd%' THEN t1.F_PKEY END) > 5 THEN 5
                 ELSE 0
            END)
        END AS O_WT
    FROM TABLE_1 t1 CROSS JOIN
         CLI_NAMES
    WHERE t1.F_NAME IN (SELECT I_NAME
                        FROM I_WD
                        WHERE I_I_ID IN (SELECT I_MAP_ID
                                         FROM T_T_MAP 
                                         WHERE T_MAP_ID = (SELECT T_ID
                                                           FROM TWD
                                                           WHERE T_NAME = 'abc'
                                                          )
                                        )
                       ) AND
          t1.F_DATE between '2015-05-01' and '2015-06-01' AND
          t1.F_NAME LIKE 'ty%pr%' 
    GROUP BY t1.F_DATE