使MYSQL查询更短，但结果相同

voase2hg  于 2023-10-15  发布在  Mysql
关注(0)|答案(3)|浏览(87)

我有一个表，其中可能的值是null，0（不好），1（好）和3（不/适用）。我必须严格按照顺序只选择null和等于1的值。我的疑问太长了。你能帮我把它弄短一点吗？

create table cv.cv_interview_status
(
    id                    int auto_increment
        primary key,
    type                  varchar(255)                       not null,
    date                  varchar(255)                       not null,
    issue                 varchar(255)                       not null,
    recruiter             varchar(255)                       not null,
    department            varchar(255)                       not null,
    position              varchar(255)                       not null,
    feedback_department   tinyint                            null,
    contacted_hr          tinyint                            null,
    office_interview      tinyint                            null,
    department_testing    tinyint                            null,
    department_acceptance tinyint                            null,
    add_cv                tinyint                            null,
    psychological_test    tinyint                            null,
    department_interview  tinyint                            null,
    accepted_not_accepted tinyint                            null,
    salary_request_to_ceo tinyint                            null,
    ceo_feedback          tinyint                            null,
    salary_offer          tinyint                            null,
    accept_salary_offer   tinyint                            null,
    hr_documentation      tinyint                            null,
    hr_legal_requirements tinyint                            null,
    date_created          datetime default CURRENT_TIMESTAMP not null
);

    SELECT *
    FROM cv_interview_status
    WHERE 1
      AND (
            (`feedback_department` IS NULL)
            OR
            (
                    `feedback_department` = 1
                    AND `contacted_hr` IS NULL
                    AND `office_interview` IS NULL
                    AND `department_testing` IS NULL
                    AND `department_acceptance` IS NULL
                    AND `add_cv` IS NULL
                    AND `psychological_test` IS NULL
                    AND `department_interview` IS NULL
                    AND `accepted_not_accepted` IS NULL
                    AND `salary_request_to_ceo` IS NULL
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` IS NULL
                    AND `department_testing` IS NULL
                    AND `department_acceptance` IS NULL
                    AND `add_cv` IS NULL
                    AND `psychological_test` IS NULL
                    AND `department_interview` IS NULL
                    AND `accepted_not_accepted` IS NULL
                    AND `salary_request_to_ceo` IS NULL
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` = 1
                    AND `office_interview` = 1
                    AND `department_testing` IS NULL
                    AND `department_acceptance` IS NULL
                    AND `add_cv` IS NULL
                    AND `psychological_test` IS NULL
                    AND `department_interview` IS NULL
                    AND `accepted_not_accepted` IS NULL
                    AND `salary_request_to_ceo` IS NULL
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` IS NULL
                    AND `add_cv` IS NULL
                    AND `psychological_test` IS NULL
                    AND `department_interview` IS NULL
                    AND `accepted_not_accepted` IS NULL
                    AND `salary_request_to_ceo` IS NULL
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` = 1
                    AND `add_cv` IS NULL
                    AND `psychological_test` IS NULL
                    AND `department_interview` IS NULL
                    AND `accepted_not_accepted` IS NULL
                    AND `salary_request_to_ceo` IS NULL
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` = 1
                    AND `add_cv` = 1
                    AND `psychological_test` IS NULL
                    AND `department_interview` IS NULL
                    AND `accepted_not_accepted` IS NULL
                    AND `salary_request_to_ceo` IS NULL
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` = 1
                    AND `add_cv` = 1
                    AND `psychological_test` = 1
                    AND `department_interview` IS NULL
                    AND `accepted_not_accepted` IS NULL
                    AND `salary_request_to_ceo` IS NULL
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` = 1
                    AND `add_cv` = 1
                    AND `psychological_test` = 1
                    AND `department_interview` = 1
                    AND `accepted_not_accepted` IS NULL
                    AND `salary_request_to_ceo` IS NULL
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` = 1
                    AND `add_cv` = 1
                    AND `psychological_test` = 1
                    AND `department_interview` = 1
                    AND `accepted_not_accepted` = 1
                    AND `salary_request_to_ceo` IS NULL
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` = 1
                    AND `add_cv` = 1
                    AND `psychological_test` = 1
                    AND `department_interview` = 1
                    AND `accepted_not_accepted` = 1
                    AND `salary_request_to_ceo` = 1
                    AND `ceo_feedback` IS NULL
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` = 1
                    AND `add_cv` = 1
                    AND `psychological_test` = 1
                    AND `department_interview` = 1
                    AND `accepted_not_accepted` = 1
                    AND `salary_request_to_ceo` = 1
                    AND `ceo_feedback` = 1
                    AND `salary_offer`IS NULL
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` = 1
                    AND `add_cv` = 1
                    AND `psychological_test` = 1
                    AND `department_interview` = 1
                    AND `accepted_not_accepted` = 1
                    AND `salary_request_to_ceo` = 1
                    AND `ceo_feedback` = 1
                    AND `salary_offer` = 1
                    AND `accept_salary_offer` IS NULL
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
            OR
            (
                        `feedback_department` = 1
                    AND `contacted_hr` =1
                    AND `office_interview` = 1
                    AND `department_testing` = 1
                    AND `department_acceptance` = 1
                    AND `add_cv` = 1
                    AND `psychological_test` = 1
                    AND `department_interview` = 1
                    AND `accepted_not_accepted` = 1
                    AND `salary_request_to_ceo` = 1
                    AND `ceo_feedback` = 1
                    AND `salary_offer` = 1
                    AND `accept_salary_offer` = 1
                    AND `hr_documentation` IS NULL
                    AND `hr_legal_requirements` IS NULL
                )
        );

这个表的过滤器工作得很好，但是查询太长了，你能给予建议我如何重构它吗？
解决方案：

SELECT
    *
FROM
    cv_interview_status
WHERE
    ( `feedback_department` IS NULL )
   OR (
            `feedback_department` = 1
        AND (
            (      IFNULL( `contacted_hr`, 1 )
                  * IFNULL( `office_interview`, 1 )
                  * IFNULL( `department_testing`, 1 )
                  * IFNULL( `department_acceptance`, 1 )
                  * IFNULL( `add_cv`, 1 )
                  * IFNULL( `psychological_test`, 1 )
                  * IFNULL( `department_interview`, 1 )
                  * IFNULL( `accepted_not_accepted`, 1 )
                  * IFNULL( `salary_request_to_ceo`, 1 )
                  * IFNULL( `ceo_feedback`, 1 )
                  * IFNULL( `salary_offer`, 1 )
                  * IFNULL( `accept_salary_offer`, 1 )

                ) = 1 )
        AND `hr_documentation` IS NULL
        AND `hr_legal_requirements` IS NULL
    )

mysql

3条答案

按热度按时间
vohkndzv

vohkndzv1#

...
WHERE ...
  AND CONCAT( COALESCE(feedback_department, 0),
              COALESCE(contacted_hr, 0),
              ...
              COALESCE(hr_legal_requirements, 0)
              ) REGEXP '^1{1,}0{2,}$'
...

我需要的查询只选择值1（确定）和空，但你查询选择和值0（不确定）-奥尔加
只需使用另一个NULL替换即可。例如

...
WHERE ...
  AND CONCAT( COALESCE(feedback_department, 'N'),
              COALESCE(contacted_hr, 'N'),
              ...
              COALESCE(hr_legal_requirements, 'N')
              ) REGEXP '^1{1,}N{2,}$'
...
赞(0）分享  回复(0）举报  2023-10-15
wn9m85ua

wn9m85ua2#

不知道这是否行得通：

SELECT 
    *
FROM
    cv_interview_status
WHERE
    (`feedback_department` IS NULL
        OR (`feedback_department` = 1
        AND (`contacted_hr` IS NULL
        OR `office_interview` IS NULL
        OR `department_testing` IS NULL
        OR `department_acceptance` IS NULL
        OR `add_cv` IS NULL
        OR `psychological_test` IS NULL
        OR `department_interview` IS NULL
        OR `accepted_not_accepted` IS NULL
        OR `salary_request_to_ceo` IS NULL
        OR `ceo_feedback` IS NULL
        OR `salary_offer` IS NULL
        OR `accept_salary_offer` IS NULL
        OR `hr_documentation` IS NULL
        OR `hr_legal_requirements` IS NULL))
        
        AND NOT (`feedback_department` = 1
        AND (`contacted_hr` IS NULL
        OR `office_interview` IS NULL
        OR `department_testing` IS NULL
        OR `department_acceptance` IS NULL
        OR `add_cv` IS NULL
        OR `psychological_test` IS NULL
        OR `department_interview` IS NULL
        OR `accepted_not_accepted` IS NULL
        OR `salary_request_to_ceo` IS NULL
        OR `ceo_feedback` IS NULL
        OR `salary_offer` IS NULL
        OR `accept_salary_offer` IS NULL
        OR `hr_documentation` IS NULL
        OR `hr_legal_requirements` IS NULL)
        
        AND (`contacted_hr` IS NOT NULL
        OR `office_interview` IS NOT NULL
        OR `department_testing` IS NOT NULL
        OR `department_acceptance` IS NOT NULL
        OR `add_cv` IS NOT NULL
        OR `psychological_test` IS NOT NULL
        OR `department_interview` IS NOT NULL
        OR `accepted_not_accepted` IS NOT NULL
        OR `salary_request_to_ceo` IS NOT NULL
        OR `ceo_feedback` IS NOT NULL
        OR `salary_offer` IS NOT NULL
        OR `accept_salary_offer` IS NOT NULL
        OR `hr_documentation` IS NOT NULL
        OR `hr_legal_requirements` IS NOT NULL)));
赞(0）分享  回复(0）举报  2023-10-15
5vf7fwbs

5vf7fwbs3#

SELECT
    * 
FROM
    tt 
WHERE
    ( `a` IS NULL ) 
    OR (
        `a` = 1 
      AND ( ( IFNULL( `b`, 1 ) * IFNULL( `c`, 1 ) * IFNULL( `d`, 1 )) = 1 ) 
    )
赞(0）分享  回复(0）举报  2023-10-15
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com