优化SQL Server查询

Question

我有一个SQL Server查询：

(
    SELECT k12_dms_contacts_institution_jobtitles.job_title_id AS id
    FROM   k12_dms_contacts_institution_jobtitles
            INNER JOIN k12_dms_institution_master  ON  k12_dms_institution_master.id = k12_dms_contacts_institution_jobtitles.inst_id
    WHERE   (
                k12_dms_institution_master.state_id='63'
                AND k12_dms_contacts_institution_jobtitles.glevel_id='1'
            )
    GROUP BY
             k12_dms_contacts_institution_jobtitles.job_title_id
) 
INTERSECT 
(
    SELECT k12_dms_contacts_institution_jobtitles.job_title_id AS id
    FROM   k12_dms_contacts_institution_jobtitles
            INNER JOIN k12_dms_institution_master  ON  k12_dms_institution_master.id = k12_dms_contacts_institution_jobtitles.inst_id
    WHERE   (
                k12_dms_institution_master.state_id='63'
                AND k12_dms_contacts_institution_jobtitles.glevel_id='2'
            )
    GROUP BY
             k12_dms_contacts_institution_jobtitles.job_title_id
) 
INTERSECT 
(
    SELECT k12_dms_contacts_institution_jobtitles.job_title_id AS id
    FROM   k12_dms_contacts_institution_jobtitles
            INNER JOIN k12_dms_institution_master  ON  k12_dms_institution_master.id = k12_dms_contacts_institution_jobtitles.inst_id
    WHERE   (
                k12_dms_institution_master.state_id='63'
                AND k12_dms_contacts_institution_jobtitles.glevel_id='3'
            )
    GROUP BY
             k12_dms_contacts_institution_jobtitles.job_title_id
) 
INTERSECT 
(
    SELECT k12_dms_contacts_institution_jobtitles.job_title_id AS id
    FROM   k12_dms_contacts_institution_jobtitles
            INNER JOIN k12_dms_institution_master  ON  k12_dms_institution_master.id = k12_dms_contacts_institution_jobtitles.inst_id
    WHERE   (
                k12_dms_institution_master.state_id='63'
                AND k12_dms_contacts_institution_jobtitles.glevel_id='4'
            )
    GROUP BY
             k12_dms_contacts_institution_jobtitles.job_title_id
) 

有没有办法优化上面的query.Intersection只需要在Where子句的基础上。我想获取所有的职位，这是所有级别的共同。

Answer 1

您可以使查询更简洁，如下所示：

SELECT IJT.job_title_id AS id
FROM k12_dms_contacts_institution_jobtitles AS IJT
INNER JOIN k12_dms_institution_master AS IM
ON IM.id = IJT.inst_id
WHERE IM.state_id='63'
AND IJT.glevel_id IN ('1', '2', '3', '4')
GROUP BY IJT.job_title_id
HAVING COUNT(DISTINCT IJT.glevel_id) = 4

Answer 2

这就是你要找的东西吗？(未测试)

 SELECT k12_dms_contacts_institution_jobtitles.job_title_id AS id
    FROM   k12_dms_contacts_institution_jobtitles
            INNER JOIN k12_dms_institution_master  ON  k12_dms_institution_master.id = k12_dms_contacts_institution_jobtitles.inst_id
    WHERE   (
                k12_dms_institution_master.state_id='63'
                AND (select count( disctinct k12_dms_contacts_institution_jobtitles.glevel_id) from k12_dms_contacts_institution_jobtitles where k12_dms_contacts_institution_jobtitles.glevel_id in ('1','2','3','4')) = 4
            )
    GROUP BY
             k12_dms_contacts_institution_jobtitles.job_title_id