Использование и меры предосторожности случая, когда в MySQL

предисловие

В MySQL есть два места, где используются ключевые слова.case:

• Flow Control Functions - CASE Operator
• Flow Control Statements - CASE Statement

существуетCASE Statementне может иметь вELSE NULLоговорка и сEND CASEконец вместоEND.

CASE StatementВ основном используется в составных операторах, таких как хранимые процедуры; иCASE OperatorОн используется как функция в одном операторе.

Эта статья в основном знакомит сCASE OperatorПрименение.

Основной синтаксис случая, когда

Первое использование:

CASE value
    WHEN compare_value THEN result
    [WHEN compare_value THEN result ...]
    [ELSE result]
END

Второе использование:

CASE
    WHEN condition THEN result
    [WHEN condition THEN result ...]
    [ELSE result]
END

Разница между двумя вариантами использования:

ПервыйCASEСинтаксис возвращает первоеvalue=compare_valueдляtrueрезультат ветки.

второйCASEСинтаксис возвращает первоеconditionдляtrueрезультат ветки.

если нет ни одногоvalue=compare_valueилиconditionдляtrue, то вернетсяELSEсоответствующий результат, если нетELSEветвь, затем возвратNULL.

Меры предосторожности на тот случай, когда

Не может быть пересечения между ветвями

Эта функция выполняется последовательно, и между каждым условием не может быть пересечения; дело не в том, что синтаксис MySQL не допускает пересечения, а в том, что после успешного совпадения другие ветви выполняться не будут. Если логическая связь не устранена, результаты запроса могут не соответствовать ожиданиям.

НУЛЕВОЕ решение

существуетCASEПри первом использовании , если вы хотите судить, является ли поле или выражениеNULLписьма.

Неправильное написание:

SELECT
    CASE (`字段`|`表达式`)
        WHEN NULL THEN '结果为假'
        ELSE '结果为真'
    END
FROM `table_name`

Правильное написание:

SELECT
    CASE (`字段`|`表达式`) IS NULL
        WHEN TRUE THEN '结果为真'
        ELSE '结果为假'
    END
FROM `table_name`

Причина, по которой это должно быть написано таким образом, заключается в следующем: MySQL неNULLСуждение не может прямо использовать знак равенства=, но использоватьIS NULLилиIS NOT NULL.

Проблема со значениями по умолчанию

существуетЯма случая mysql, когдаИнтересно увидеть это использование в этом блоге.

Заявление 1:

UPDATE categories
SET
    display_order = CASE id 
                        WHEN 1 THEN 3 
                        WHEN 2 THEN 4 
                        WHEN 3 THEN 5 
                    END;

Заявление 2:

UPDATE categories
SET
    display_order = CASE id 
                        WHEN 1 THEN 3 
                        WHEN 2 THEN 4 
                        WHEN 3 THEN 5 
                    END
WHERE
    id IN (1,2,3);

если неwhereпара операторовidограничение, то оператор 1 будетidне для1, 2, 3всех рекордовdisplay_orderполя настроены наNULL.

Тип значения, возвращаемый ветвью, может быть несогласованным

SELECT
    CASE
        WHEN 5 % 3 = 0 THEN "情况1"
        WHEN 5 % 3 = 1 THEN "情况2"
        ELSE 12
    END AS result;

существуетDataGripвыполнить это наSQLОператор не сообщает об ошибке, а запрос, за которым следует имя таблицы, не сообщает об ошибке. Увидев этот результат, я сломался.Тип возвращаемого значения ветки несовместим.Не должно ли быть сообщение об ошибке?

Затем я используюJdbcTemplateвыполнить этоSQLзаявление, обнаружил, что ошибки не было,ELSEЗначение ветки преобразуется в строку.

В самом делеMySQLНесерьезно!

В этот момент я обернулся и посмотрел поближе.Документация по MySQL, и обнаружил, что документация очень подробно описывает эту ситуацию.

Вот простой перевод:

CASEВозвращаемое значение функции представляет собой агрегированный тип всех результирующих типов значений:

• Если тип всех значений числовой, то тип агрегата тоже числовой:
  • Если хотя бы одно из значений двойное, то тип результата двойной.
  • В противном случае, если хотя бы одно значениеDECIMAL, то тип результатаDECIMAL.
  • ......
• ......
• Для всех других типов комбинаций результатVARCHARТипы.
• Когда типы объединяются, они игнорируютсяNULLТип, которому принадлежит значение.

В середине слишком много типов слияний, поэтому я не буду перечислять их по одному из-за нехватки места.Если вам интересно, перейдите по ссылке:operate_case.

Сценарий использования случая, когда

• Преобразование значений полей на основе условий
• строка в столбец

преобразование поля

SELECT
    name '姓名',
    age '年龄',
    CASE
        WHEN age < 18 THEN '少年'
        WHEN age < 30 THEN '青年'
        WHEN age >= 30 AND age < 50 THEN '中年'
        ELSE '老年'
    END '年龄段'
FROM
    user_info;

Один оператор для вывода нескольких индикаторов

Сколько одноклассников мужского пола и сколько одноклассников женского пола, и подсчитайте, сколько одноклассников мужского пола сдали и сколько одноклассников сдали

Структура таблицы следующая: в поле STU_SEX 0 означает мальчиков, 1 — девочек.

STU_CODE	STU_NAME	STU_SEX	STU_SCORE
XM	Сяо Мин	0	88
XL	Сяолэй	0	55
XF	Сяофэн	0	45
XH	немного красный	1	66
XN	Сяо Ни	1	77
XY	Сяойи	1	99

SELECT 
    SUM (CASE WHEN STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_COUNT,
    SUM (CASE WHEN STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_COUNT,
    SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 0 THEN 1 ELSE 0 END) AS MALE_PASS,
    SUM (CASE WHEN STU_SCORE >= 60 AND STU_SEX = 1 THEN 1 ELSE 0 END) AS FEMALE_PASS
FROM 
    THTF_STUDENTS

Структура таблицы в этом примере не очень разумна: имя, пол и оценка помещаются в одну таблицу; ноsumа такжеcaseЯ редко видел их вместе,sumне все общее иgroupиспользовать вместе?

строка в столбец

• Отображение продаж горизонтально по месяцам
• Горизонтальное отображение оценок по предметам

невиновныйCASE WHENНевозможно преобразовать строки в столбцы и необходимо сотрудничатьSUMа такжеGROUP BYИспользование оговорки etc.

Статистика по каждому предмету

SELECT
    st.stu_id '学号',
    st.stu_name '姓名',
    sum(CASE co.course_name WHEN '大学语文' THEN sc.scores ELSE 0 END ) '大学语文',
    sum(CASE co.course_name WHEN '新视野英语' THEN sc.scores ELSE 0 END ) '新视野英语',
    sum(CASE co.course_name WHEN '离散数学' THEN sc.scores ELSE 0 END ) '离散数学',
    sum(CASE co.course_name WHEN '概率论' THEN sc.scores ELSE 0 END ) '概率论',
    sum(CASE co.course_name WHEN '线性代数' THEN sc.scores ELSE 0 END ) '线性代数',
    sum(CASE co.course_name WHEN '高等数学' THEN sc.scores ELSE 0 END ) '高等数学'
FROM
    edu_student st
    LEFT JOIN edu_score sc ON st.stu_id = sc.stu_id
    LEFT JOIN edu_courses co ON co.course_no = sc.course_no
GROUP BY
    st.stu_id
ORDER BY
    NULL;

Сумма месячной производительности каждого отдела

SELECT
    t1.dep,
    t2.depname,
    SUM(CASE mon WHEN '一月' THEN yj ELSE 0 END) AS 一月,
    SUM(CASE mon WHEN '一月' THEN yj ELSE 0 END) AS 一月,
    SUM(CASE mon WHEN '一月' THEN yj ELSE 0 END) AS 一月,
FROM
    table_1 t1
    LEFT JOIN table_2 t2 ON t1.dep = t2.dep
GROUP BY
    t1.dep;

SQL-оптимизация

Примеры оптимального статистического анализа

использоватьsum case whenпредыдущийSQL:

SELECT 
    (
         SELECT SUM(total_fee) 
         FROM mall_order SS 
         WHERE SS.create_time = S.create_time AND SS.payment_method = 1
    ) AS 'zhifubaoTotalOrderAmount',
    (
        SELECT COUNT(*)
        FROM mall_order SS
        WHERE SS.create_time = S.create_time AND SS.payment_method = 1
    ) AS 'zhifubaoTotalOrderNum',
    (
        SELECT SUM(total_fee)
        FROM mall_order SS
        WHERE SS.create_time = S.create_time AND SS.payment_method = 2
    ) AS 'weixinTotalOrderAmount',
    (
        SELECT COUNT(*)
        FROM mall_order SS
        WHERE SS.create_time = S.create_time AND SS.payment_method = 2
    ) AS 'weixinTotalOrderNum'
 FROM mall_order S
 WHERE S.create_time > '2016-05-01' AND S.create_time < '2016-08-01' 
 GROUP BY
     S.create_time
 ORDER BY
     S.create_time ASC;

Выполнение: 50 Вт данных, около 10 с; полное сканирование таблицы, 4 подзапросаDEPENDENT SUBQUERY, зависит от внешнего запроса.

использоватьsum case whenПосле оптимизации:

SELECT
    S.create_time,
    sum(case when S.payment_method =1 then 1 else 0 end) as 'zhifubaoOrderNum',
    sum(case when S.payment_method =1 then total_fee else 0 end) as 'zhifubaoOrderAmount',
    sum(case when S.payment_method =2 then 1 else 0 end) as 'weixinOrderNum',
    sum(case when S.payment_method =2 then total_fee else 0 end) as 'weixinOrderAmount'
FROM
    mall_order S
WHERE
    S.create_time > '2015-05-01' and S.create_time < '2016-08-01' 
GROUP BY
    S.create_time
ORDER BY
    S.create_time asc;

Ситуация с выполнением: вся таблица сканирует 50 Вт данных, около 1 с; результат можно получить, пройдя всю таблицу один раз.

Еще один пример оптимизации

оригинальныйSQL:

SELECT
    uid,
    sum(power) powerup
FROM t1 
WHERE
    date>='2017-03-31' AND
    UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))>=1490965200 AND
    UNIX_TIMESTAMP(STR_TO_DATE(concat(date,' ',hour),'%Y-%m-%d %H'))<1492174801 AND
    aType in (1,6,9)
GROUP BY
    uid;

Описание ситуации: Таблица разработана с датой и временем вdateа такжеhourОн разделяется на два столбца, а затем при запросе объединяется в новое условие, что приводит к очень низкой эффективности SQL, полному просмотру таблицы, отсутствию индекса, временной таблицы и дополнительной сортировке.

оптимизированныйSQL:

SELECT
    uid,
    sum(powerup+powerup1)
FROM
    (
        SELECT uid,
        CASE
            WHEN concat(date,' ',hour) >='2017-03-24 13:00' THEN power ELSE '0' 
        END AS powerup,

        CASE
            WHEN concat(date,' ',hour) < '2017-03-25 13:00' THEN power ELSE '0'
        END AS powerup1
        
        FROM t1
        WHERE date >= '2017-03-24' AND date AND aType in (1,6,9)
    ) a 
GROUP BY
    uid;

использоватьcase whenПосле оптимизации оригиналdateМожно использовать приведенный выше индекс.

Суммировать

Лично мне не нравится работать в бизнес-кодеSQLиспользуется в предложенияхcase when, по двум причинам:

• не очень читабельно
• Плохая ремонтопригодность

Однако при статистическом анализе использование этого типа функций будет вздыхать: действительно ароматно!

использованная литература

• Dev.MySQL.com/doc/Furious/…
• Dev.MySQL.com/doc/Furious/…
• Блог woohoo.cn на.com/echo JSON/afraid/…
• blog.CSDN.net/QQ_16142851…
• blog.CSDN.net/U013514928/…
• Woo Woo.cn blog on.com/ Воспользуйтесь животом...
• Блог woohoo.cn на.com/echo JSON/afraid/…
• blog.CSDN.net/QQ_16142851…
• no.OSCHINA.net/U/1187675/no…
• blog.CSDN.net/WeChat_3246…
• blog.CSDN.net/velvet U disk/ah…