sql案例二

— 需求: 导出问卷调查表中的题目和答案，注意其中题目有补充题，有表格题，例如：
题目：满意吗
选项满意不满意
补充：不满意理由xxxx

方案：

SELECT t1.answer_id,
               t1.submit_user,
               t1.submit_date,
               case
                   when t1.survey_mode = 1 then '电话'
                   when t1.survey_mode = 2 then '现场'
                   when t1.survey_mode = 3 then '链接访问'
                   else '其他' end    survey_mode,
               t1.statistics_score AS 'score',
               t1.subject_id,
               t1.field_type,
               t1.field_label,
               t1.field_name,
               t1.field_answer,
							  t1.field_sort,
               t1.table_row_index,
               t1.table_field_sort
        FROM (SELECT a.answer_id,
                     a.submit_user,
                     a.submit_date,
                     a.survey_mode,
                     a.statistics_score,
                     b.subject_id,
                     b.subject_type,
                     b.field_type,
                     b.field_label,
                     b.field_name,
                     b.field_answer_value field_answer,
                     b.field_sort,
                     0                    table_row_index,
                     0                    table_field_sort
              FROM t_park_survey_answer a
                       LEFT JOIN t_park_survey_answer_subject b ON b.answer_id = a.answer_id AND b.valid = 1
              WHERE a.valid = 1
                AND a.task_item_id = 1812747826334007299
                AND b.subject_type NOT IN ('1', '4')
                and b.field_type != 'table'

              UNION ALL
              SELECT a.answer_id,
                     a.submit_user,
                     a.submit_date,
                     a.survey_mode,
                     a.statistics_score,
                     b.subject_id,
                     b.subject_type,
                     b.field_type,
                     b.supplement_field_label  field_label,
                     b.field_name,
                     b.supplement_field_answer field_answer,
                     b.field_sort,
                     0                         table_row_index,
                     0                         table_field_sort
              FROM t_park_survey_answer a
                       LEFT JOIN t_park_survey_answer_subject b ON b.answer_id = a.answer_id
                  AND b.valid = 1
              WHERE a.valid = 1
                AND a.task_item_id = 1812747826334007299
                AND b.subject_type NOT IN ('1', '4')
                AND b.is_supplement = '1'

              union all
              SELECT a.answer_id,
                     a.submit_user,
                     a.submit_date,
                     a.survey_mode,
                     a.statistics_score,
                     b.subject_id,
                     b.subject_type,
                     b.field_type,
                     CONCAT(b.field_label, '-', c.field_label) field_label,
										 c.field_name,
                     c.field_answer_value                      field_answer,
                     
                     b.field_sort,
                     c.table_row_index,
                     c.field_sort                              table_field_sort
              FROM t_park_survey_answer a
                       LEFT JOIN t_park_survey_answer_subject b on b.answer_id = a.answer_id AND b.valid = 1
                       LEFT JOIN t_park_survey_answer_subject_table c ON c.subject_id = b.subject_id AND b.valid = 1
              WHERE a.valid = 1
                and b.field_type = 'table'
                AND a.task_item_id = 1812747826334007299) t1
        ORDER BY t1.submit_date desc,
                 t1.answer_id,
                 t1.subject_type,
                 t1.field_sort,t1.table_row_index,t1.table_field_sort

整理后的表格如下（已美化，内容结构与原数据一致，可直接复制到Excel或Markdown中使用）：

answer_id	submit_user	submit_date	survey_mode	score	subject_id	field_type	field_label	field_name	field_answer	field_sort
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256754135041	select	园区名称(系统)	sys_ibp_project_name	万纬沈阳浑南冷链物流园	1
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256754135042	text	客户名称(系统)	sys_customer_name		2
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256758329344	select	受访者姓名(系统)	sys_contacts_name		3
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256758329345	dict-multiply	业务范围	business_scope		4
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256758329346	text	文1	1812692604660219904	123	1
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256758329347	radio	单1	1812692604664414208	选项2	2
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256758329350	radio	单2	1812692604664414211	选项1	3
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256758329350	radio	补充	1812692604664414211		3
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256762523649	checkbox	多1	1812692604664414214	选项2	4
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256762523652	checkbox	多2	1812692604668608512		5
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256762523652	checkbox	补充	1812692604668608512		5
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256762523655	score	评2	1812692604668608517		6
1812765256305344512	张龙(C-ZHANGL103)	2024/7/15 16:24:51	电话	2	1812765256762523655	score	补评	1812692604668608517		6