mysql

最新推荐文章于 2022-02-15 13:18:04 发布

Incimo

最新推荐文章于 2022-02-15 13:18:04 发布

阅读量194

点赞数

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_41912398/article/details/114109843

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

CREATE VIEW `ths_personbasicInfo_codevalue` AS 
SELECT
*,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'Sex' AND CodeID = ths_nt_personbasiclnfo.PB_XB LIMIT 1) AS  sex,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'CodeTable_MZ' AND CodeID = ths_nt_personbasiclnfo.PB_MZ LIMIT 1) AS  mz,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'xl' AND CodeID = ths_nt_personbasiclnfo.PB_QRZXL LIMIT 1) AS  qrzxl,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'xl' AND CodeID = ths_nt_personbasiclnfo.PB_ZZXL LIMIT 1) AS  zzjyxl,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'ZT' AND CodeID = ths_nt_personbasiclnfo.PB_ZT LIMIT 1) AS  zt
FROM ths_nt_personbasiclnfo

-- CREATE VIEW `ths_codevalue` AS 
SELECT *,(select GROUP_CONCAT() from codes c where FIND_IN_SET(ths_nt_personbasiclnfo.PB_XB,u.`code`)),
FROM ths_nt_personbasiclnfo
-- WHERE NT_PersonEducationOID = '00007def-0000-0002-0000-0000f99d54c0'

CREATE VIEW `ths_personbasicInfo_codevalue` AS 
SELECT
*,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'Sex' AND CodeID = ths_nt_personbasiclnfo.PB_XB LIMIT 1) AS  sex,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'CodeTable_MZ' AND CodeID = ths_nt_personbasiclnfo.PB_MZ LIMIT 1) AS  mz,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'xl' AND CodeID = ths_nt_personbasiclnfo.PB_QRZXL LIMIT 1) AS qrzxl,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'xl' AND CodeID = ths_nt_personbasiclnfo.PB_ZZXL LIMIT 1) AS zzjyxl,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'ZT' AND CodeID = ths_nt_personbasiclnfo.PB_ZT LIMIT 1) AS zt,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'ZZMM' AND CodeID = ths_nt_personbasiclnfo.PB_ZZMM LIMIT 1) AS zzmm,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'body' AND CodeID = ths_nt_personbasiclnfo.PB_STZK LIMIT 1) AS stzk
FROM ths_nt_personbasiclnfo

-- user gxnt.ths_nt_personbasiclnfo

SELECT CodeName FROM gxnt.ths_codevalue WHERE CodeTableID = 'Sex' AND CodeID = 1 LIMIT 1

SELECT CodeName FROM gxnt.ths_codevalue WHERE CodeTableID = 'CodeTable_MZ' AND CodeID = 15 LIMIT 1

-- ,NT_PersonEducation,NT_PersonSocialPractice,NT_PersonFamilies
-- WHERE NT_PersonBasiclnfo.NT_PersonBasiclnfoOID = NT_PersonEducation.NT_PersonBasiclnfo_FK
-- AND NT_PersonSocialPractice.NT_PersonBasiclnfo_FK = NT_PersonBasiclnfo.NT_PersonBasiclnfoOID
-- AND NT_PersonFamilies.NT_PersonBasiclnfo_FK = NT_PersonBasiclnfo.NT_PersonBasiclnfoOID
-- GROUP BY NT_PersonBasiclnfo.NT_PersonBasiclnfoOID

SELECT *,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'xxfs' AND CodeID = ths_nt_personeducation.PE_StudyType LIMIT 1) AS  xxfs ,
(SELECT CodeName FROM ths_codevalue WHERE CodeTableID = 'EducationStatus' AND CodeID = ths_nt_personeducation.PE_Qualifications LIMIT 1) AS  xl 
FROM ths_nt_personeducation

SELECT 场景名称,食堂名称,
CASE WHEN 优先级=3 THEN '红色' WHEN 优先级=2 THEN '黄色' ELSE '蓝色' END AS 显示颜色
FROM
(
  SELECT 场景名称,食堂名称,MAX(优先级) AS 优先级
    FROM
    (
        SELECT 场景名称,食堂名称,显示颜色,
        CASE WHEN 显示颜色='红色' THEN 3 WHEN 显示颜色='黄色' THEN 2 ELSE 1 END AS 优先级
        FROM
        (
            SELECT 场景名称,食堂名称,
            CASE WHEN 显示颜色='蓝色' AND 颜色数量>1 THEN '黄色' ELSE 显示颜色 END AS 显示颜色
            FROM
            (
                SELECT 场景名称,食堂名称,显示颜色,COUNT(1) AS 颜色数量
                FROM 食堂
                GROUP BY 场景名称,食堂名称,显示颜色
            ) AS T1
        ) AS T2 
    ) AS T3
    GROUP BY 场景名称,食堂名称
) AS T4
ORDER BY 场景名称,食堂名称,显示颜色

行转列

SELECT
	student_number,
	semester_year,
	semester_type,
	SUM( CASE `course_type` WHEN '必修课' THEN fail_exams ELSE 0 END ) AS 'bx_fail',
	SUM( CASE `course_type` WHEN '选修课' THEN fail_exams ELSE 0 END ) AS 'xx_fail' 
FROM
	stu_fail_exams_statistics 
GROUP BY
	student_number,
	semester_year,
	semester_type