个人中心
gpt4 book ai didi

mysql - 臃肿的 UNION ALL 代码 (MYSQL)

转载 作者：行者123 更新时间：2023-11-29 19:34:10 25 4
gpt4 key购买 nike

我是一名自学成才的人，我的 SQL 代码很乱，而且速度慢得可怕。我在 Tableau 中可视化这些数据，但我创建的 View 变得非常慢，以至于我必须创建一个包含我需要的所有信息的表(每天刷新)。

我相信速度缓慢是由于我在这里执行了大量的 UNION ALL 语句。我这样做是为了“透视”数据，因此每个问题都不是数据库中自己的列，而是每行的属性。

任何人都可以提供一些关于如何重组代码以加快速度的提示吗？任何帮助或批评将不胜感激，谢谢! (下面只是我的代码的一个片段，UNION ALL block 的数量继续......

CREATE ALGORITHM=UNDEFINED 
DEFINER=root@localhost 
SQL SECURITY DEFINER 
VIEW view_data435386_xxxxx_MASTER AS


SELECT _data435386_xxxxx_1_job_profile.ID AS ID,
_data435386_xxxxx_1_job_profile.ID AS Comment_Photos_ID,
'Job Profile' AS Impact_Area,
_data435386_xxxxx_1_job_profile.PARENT_RECORD_ID AS PARENT_RECORD_ID,
_data435386_xxxxx_1_job_profile.pjq1 AS PJQ1,
_data435386_xxxxx_1_job_profile.pjq_2 AS PJQ2,
_data435386_xxxxx_1_job_profile.pjq_3 AS PJQ3,
_data435386_xxxxx_1_job_profile.pjq_4 AS PJQ4,
_data435386_xxxxx_1_job_profile.pjq_5 AS PJQ5,
_data435386_xxxxx_1_job_profile.pjq_6 AS PJQ6,
_data435386_xxxxx_1_job_profile.pjq_7 AS PJQ7,
_data435386_xxxxx_1_job_profile.pjq_8 AS PJQ8,
_data435386_xxxxx_1_job_profile.pjq_9 AS PJQ9,
_data435386_xxxxx_1_job_profile.pjq_10 AS PJQ10,
_data435386_xxxxx_main_form.additional_comments AS Additional_Comments,
_data435386_xxxxx_main_form.wr_,
NULL AS Completion_Due_Date,
NULL AS Completion_Due_Date_1443,
NULL AS Training_Needed,
NULL AS Follow_Up_Description,
REPLACE(_data435386_xxxxx_main_form.inspector, '_', ' ') AS Inspector,
REPLACE(_data435386_xxxxx_main_form.crew_leader, '_', ' ') AS Crew_Leader,
_data435386_xxxxx_main_form.date1 AS date1,
REPLACE(_data435386_xxxxx_1_job_profile.district, '_', ' ') AS District,
REPLACE(_data435386_xxxxx_1_job_profile.division, '_', ' ') AS Division,
REPLACE(_data435386_xxxxx_1_job_profile.contractor_inspected, '_', ' ') AS Contractor_Inspected,
301 Sample_Size_Req,
        (CASE
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                38197
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                3734
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                5044
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                55214
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                37742
            ELSE NULL   
        END) AS Population_size,
'1_Job Profile' AS Results,
3438545 AS Parent_Page_ID,
'1.0 Job Profile' AS Task,
'1.0 Job Profile' AS Construction_Activity,
NULL AS Overall_Rating,
CONCAT('https://xxxxx.xxxxx.com/exzact/dataPDF.php?TABLE_NAME=_data435386_xxxxx_main_form&ID=',
                `_data435386_xxxxx_main_form`.`ID`,
                '&PAGE_ID=3438731&USERNAME=xxxxx1&PASSWORD=xxxxx') AS `report_url`
FROM
(_data435386_xxxxx_main_form 
LEFT JOIN _data435386_xxxxx_1_job_profile ON((_data435386_xxxxx_main_form.ID = _data435386_xxxxx_1_job_profile.PARENT_RECORD_ID)))
UNION ALL 


SELECT _data435386_xxxxx_1_job_profile.ID AS ID,
_data435386_xxxxx_1_job_profile.ID AS Comment_Photos_ID,
'Safety' AS Impact_Area,
_data435386_xxxxx_1_job_profile.PARENT_RECORD_ID AS PARENT_RECORD_ID,
_data435386_xxxxx_1_job_profile.pjq1 AS PJQ1,
_data435386_xxxxx_1_job_profile.pjq_2 AS PJQ2,
_data435386_xxxxx_1_job_profile.pjq_3 AS PJQ3,
_data435386_xxxxx_1_job_profile.pjq_4 AS PJQ4,
_data435386_xxxxx_1_job_profile.pjq_5 AS PJQ5,
_data435386_xxxxx_1_job_profile.pjq_6 AS PJQ6,
_data435386_xxxxx_1_job_profile.pjq_7 AS PJQ7,
_data435386_xxxxx_1_job_profile.pjq_8 AS PJQ8,
_data435386_xxxxx_1_job_profile.pjq_9 AS PJQ9,
_data435386_xxxxx_1_job_profile.pjq_10 AS PJQ10,
_data435386_xxxxx_main_form.additional_comments AS Additional_Comments,
_data435386_xxxxx_main_form.wr_,
NULL AS Completion_Due_Date,
NULL AS Completion_Due_Date_1443,
NULL AS Training_Needed,
NULL AS Follow_Up_Description,
REPLACE(_data435386_xxxxx_main_form.inspector, '_', ' ') AS Inspector,
REPLACE(_data435386_xxxxx_main_form.crew_leader, '_', ' ') AS Crew_Leader,
_data435386_xxxxx_main_form.date1 AS date1,
REPLACE(_data435386_xxxxx_1_job_profile.district, '_', ' ') AS District,
REPLACE(_data435386_xxxxx_1_job_profile.division, '_', ' ') AS Division,
REPLACE(_data435386_xxxxx_1_job_profile.contractor_inspected, '_', ' ') AS Contractor_Inspected,
301 Sample_Size_Req,
        (CASE
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                38197
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                3734
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                5044
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                55214
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                37742
            ELSE NULL   
        END) AS Population_size,
21_Uses_proper_ppe AS Results,
3438623 AS Parent_Page_ID,
'2.1 Uses Proper PPE' AS Task,
'2.0 Safety' AS Construction_Activity,
NULL AS Overall_Rating,
CONCAT('https://xxxxx.xxxxx.com/exzact/dataPDF.php?TABLE_NAME=_data435386_xxxxx_main_form&ID=',
                `_data435386_xxxxx_main_form`.`ID`,
                '&PAGE_ID=3438731&USERNAME=xxxxx1&PASSWORD=xxxxx') AS `report_url`
FROM
((_data435386_xxxxx_main_form 
LEFT JOIN view_data435386_xxxxx_2_task_successful ON((_data435386_xxxxx_main_form.ID = view_data435386_xxxxx_2_task_successful.PARENT_RECORD_ID))) 
LEFT JOIN _data435386_xxxxx_1_job_profile ON((_data435386_xxxxx_main_form.ID = _data435386_xxxxx_1_job_profile.PARENT_RECORD_ID)))
WHERE 21_Uses_proper_ppe IS NOT NULL
UNION ALL 





SELECT _data435386_xxxxx_1_job_profile.ID AS ID,
view_data435386_xxxxx_2_task_successful.ID AS Comment_Photos_ID,
'Safety' AS Impact_Area,
_data435386_xxxxx_1_job_profile.PARENT_RECORD_ID AS PARENT_RECORD_ID,
_data435386_xxxxx_1_job_profile.pjq1 AS PJQ1,
_data435386_xxxxx_1_job_profile.pjq_2 AS PJQ2,
_data435386_xxxxx_1_job_profile.pjq_3 AS PJQ3,
_data435386_xxxxx_1_job_profile.pjq_4 AS PJQ4,
_data435386_xxxxx_1_job_profile.pjq_5 AS PJQ5,
_data435386_xxxxx_1_job_profile.pjq_6 AS PJQ6,
_data435386_xxxxx_1_job_profile.pjq_7 AS PJQ7,
_data435386_xxxxx_1_job_profile.pjq_8 AS PJQ8,
_data435386_xxxxx_1_job_profile.pjq_9 AS PJQ9,
_data435386_xxxxx_1_job_profile.pjq_10 AS PJQ10,
_data435386_xxxxx_main_form.additional_comments AS Additional_Comments,
_data435386_xxxxx_main_form.wr_,
NULL AS Completion_Due_Date,
NULL AS Completion_Due_Date_1443,
NULL AS Training_Needed,
NULL AS Follow_Up_Description,
REPLACE(_data435386_xxxxx_main_form.inspector, '_', ' ') AS Inspector,
REPLACE(_data435386_xxxxx_main_form.crew_leader, '_', ' ') AS Crew_Leader,
_data435386_xxxxx_main_form.date1 AS date1,
REPLACE(_data435386_xxxxx_1_job_profile.district, '_', ' ') AS District,
REPLACE(_data435386_xxxxx_1_job_profile.division, '_', ' ') AS Division,
REPLACE(_data435386_xxxxx_1_job_profile.contractor_inspected, '_', ' ') AS Contractor_Inspected,
301 Sample_Size_Req,
        (CASE
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                38197
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                3734
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                5044
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                55214
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                37742
            ELSE NULL   
        END) AS Population_size,
22_Damage_Prevention AS Results,
3438623 AS Parent_Page_ID,
'2.2 Damage Prevention' AS Task,
'2.0 Safety' AS Construction_Activity,
NULL AS Overall_Rating,
CONCAT('https://xxxxx.xxxxx.com/exzact/dataPDF.php?TABLE_NAME=_data435386_xxxxx_main_form&ID=',
                `_data435386_xxxxx_main_form`.`ID`,
                '&PAGE_ID=3438731&USERNAME=xxxxx1&PASSWORD=xxxxx') AS `report_url`
FROM
((_data435386_xxxxx_main_form 
LEFT JOIN view_data435386_xxxxx_2_task_successful ON((_data435386_xxxxx_main_form.ID = view_data435386_xxxxx_2_task_successful.PARENT_RECORD_ID))) 
LEFT JOIN _data435386_xxxxx_1_job_profile ON((_data435386_xxxxx_main_form.ID = _data435386_xxxxx_1_job_profile.PARENT_RECORD_ID)))
WHERE 22_Damage_Prevention IS NOT NULL
UNION ALL 





SELECT _data435386_xxxxx_1_job_profile.ID AS ID,
view_data435386_xxxxx_2_task_successful.ID AS Comment_Photos_ID,
'Safety' AS Impact_Area,
_data435386_xxxxx_1_job_profile.PARENT_RECORD_ID AS PARENT_RECORD_ID,
_data435386_xxxxx_1_job_profile.pjq1 AS PJQ1,
_data435386_xxxxx_1_job_profile.pjq_2 AS PJQ2,
_data435386_xxxxx_1_job_profile.pjq_3 AS PJQ3,
_data435386_xxxxx_1_job_profile.pjq_4 AS PJQ4,
_data435386_xxxxx_1_job_profile.pjq_5 AS PJQ5,
_data435386_xxxxx_1_job_profile.pjq_6 AS PJQ6,
_data435386_xxxxx_1_job_profile.pjq_7 AS PJQ7,
_data435386_xxxxx_1_job_profile.pjq_8 AS PJQ8,
_data435386_xxxxx_1_job_profile.pjq_9 AS PJQ9,
_data435386_xxxxx_1_job_profile.pjq_10 AS PJQ10,
_data435386_xxxxx_main_form.additional_comments AS Additional_Comments,
_data435386_xxxxx_main_form.wr_,
NULL AS Completion_Due_Date,
NULL AS Completion_Due_Date_1443,
NULL AS Training_Needed,
NULL AS Follow_Up_Description,
REPLACE(_data435386_xxxxx_main_form.inspector, '_', ' ') AS Inspector,
REPLACE(_data435386_xxxxx_main_form.crew_leader, '_', ' ') AS Crew_Leader,
_data435386_xxxxx_main_form.date1 AS date1,
REPLACE(_data435386_xxxxx_1_job_profile.district, '_', ' ') AS District,
REPLACE(_data435386_xxxxx_1_job_profile.division, '_', ' ') AS Division,
REPLACE(_data435386_xxxxx_1_job_profile.contractor_inspected, '_', ' ') AS Contractor_Inspected,
301 Sample_Size_Req,
        (CASE
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                38197
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                3734
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                5044
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                55214
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                37742
            ELSE NULL   
        END) AS Population_size,
23_fire_bottle_and_no_smoking_sign AS Results,
3438623 AS Parent_Page_ID,
'2.3 Fire Bottle and No Smoking Sign' AS Task,
'2.0 Safety' AS Construction_Activity,
NULL AS Overall_Rating,
CONCAT('https://xxxxx.xxxxx.com/exzact/dataPDF.php?TABLE_NAME=_data435386_xxxxx_main_form&ID=',
                `_data435386_xxxxx_main_form`.`ID`,
                '&PAGE_ID=3438731&USERNAME=xxxxx1&PASSWORD=xxxxx') AS `report_url`
FROM
((_data435386_xxxxx_main_form 
LEFT JOIN view_data435386_xxxxx_2_task_successful ON((_data435386_xxxxx_main_form.ID = view_data435386_xxxxx_2_task_successful.PARENT_RECORD_ID))) 
LEFT JOIN _data435386_xxxxx_1_job_profile ON((_data435386_xxxxx_main_form.ID = _data435386_xxxxx_1_job_profile.PARENT_RECORD_ID)))
WHERE 23_fire_bottle_and_no_smoking_sign IS NOT NULL
UNION ALL 





SELECT _data435386_xxxxx_1_job_profile.ID AS ID,
view_data435386_xxxxx_2_task_successful.ID AS Comment_Photos_ID,
'Safety' AS Impact_Area,
_data435386_xxxxx_1_job_profile.PARENT_RECORD_ID AS PARENT_RECORD_ID,
_data435386_xxxxx_1_job_profile.pjq1 AS PJQ1,
_data435386_xxxxx_1_job_profile.pjq_2 AS PJQ2,
_data435386_xxxxx_1_job_profile.pjq_3 AS PJQ3,
_data435386_xxxxx_1_job_profile.pjq_4 AS PJQ4,
_data435386_xxxxx_1_job_profile.pjq_5 AS PJQ5,
_data435386_xxxxx_1_job_profile.pjq_6 AS PJQ6,
_data435386_xxxxx_1_job_profile.pjq_7 AS PJQ7,
_data435386_xxxxx_1_job_profile.pjq_8 AS PJQ8,
_data435386_xxxxx_1_job_profile.pjq_9 AS PJQ9,
_data435386_xxxxx_1_job_profile.pjq_10 AS PJQ10,
_data435386_xxxxx_main_form.additional_comments AS Additional_Comments,
_data435386_xxxxx_main_form.wr_,
NULL AS Completion_Due_Date,
NULL AS Completion_Due_Date_1443,
NULL AS Training_Needed,
NULL AS Follow_Up_Description,
REPLACE(_data435386_xxxxx_main_form.inspector, '_', ' ') AS Inspector,
REPLACE(_data435386_xxxxx_main_form.crew_leader, '_', ' ') AS Crew_Leader,
_data435386_xxxxx_main_form.date1 AS date1,
REPLACE(_data435386_xxxxx_1_job_profile.district, '_', ' ') AS District,
REPLACE(_data435386_xxxxx_1_job_profile.division, '_', ' ') AS Division,
REPLACE(_data435386_xxxxx_1_job_profile.contractor_inspected, '_', ' ') AS Contractor_Inspected,
NULL Sample_Size_Req,
        (CASE
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                38197
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                3734
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                5044
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                55214
            WHEN
                (_data435386_xxxxx_1_job_profile.division = 'xxxxx')
            THEN
                37742
            ELSE NULL   
        END) AS Population_size,
24_traffic_control_plan AS Results,
3438623 AS Parent_Page_ID,
'2.4 Traffic Control Plan' AS Task,
'2.0 Safety' AS Construction_Activity,
NULL AS Overall_Rating,
CONCAT('https://xxxxx.xxxxx.com/exzact/dataPDF.php?TABLE_NAME=_data435386_xxxxx_main_form&ID=',
                `_data435386_xxxxx_main_form`.`ID`,
                '&PAGE_ID=3438731&USERNAME=xxxxx1&PASSWORD=xxxxx') AS `report_url`
FROM
((_data435386_xxxxx_main_form 
LEFT JOIN view_data435386_xxxxx_2_task_successful ON((_data435386_xxxxx_main_form.ID = view_data435386_xxxxx_2_task_successful.PARENT_RECORD_ID))) 
LEFT JOIN _data435386_xxxxx_1_job_profile ON((_data435386_xxxxx_main_form.ID = _data435386_xxxxx_1_job_profile.PARENT_RECORD_ID)))
WHERE 24_traffic_control_plan IS NOT NULL
UNION ALL

最佳答案

看起来几个被 UNIONed 的查询几乎是相同的，并且仅用于生成具有不同 Results 和 Task 字段的多个结果行；您可能想要考虑合并这些查询，或者将查询的统一版本(Results 字段的所有值作为单独的字段)插入到中间(伪临时)表中，然后进行更简单的 UNIONing对此提出质疑。

“中间(伪临时)”是指您不打算保留的非临时表；我建议使用 TEMPORARY 表，但 MySQL 不允许在同一查询中多次引用(例如 UNION 或 self-JOIN)。

<小时/>

编辑:也不清楚 Result 值来自哪个表；如果它们不是来自 _data435386_xxxxx_main_form 那么您有一些可以是 INNER JOIN 的 LEFT JOIN。

关于mysql - 臃肿的 UNION ALL 代码 (MYSQL)，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/41621656/

25 4 0
文章推荐： android - 无法运行 Hello World React Native App - 缺少领域构造函数
文章推荐： mysql - Order By 两个表 SQL 中的列
文章推荐： javascript - 使用 Jquery 上下文菜单
文章推荐： php - 选择具有多个条件(MAX)的MySQL
行者123
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com