查询调查问卷人数汇总

lmy_loveF

已于 2024-06-04 14:27:59 修改

阅读量166

点赞数 2

文章标签： sqlserver

于 2024-06-04 10:48:33 首次发布

本文链接：https://blog.csdn.net/lmy_loveF/article/details/139436275

版权

SELECT  b.di_departmentid ,
        b.di_name ,
        COUNT(1) countnum
INTO    #tempTable
FROM    QUESTIONNAIRE_ANSWER
        LEFT JOIN DEPARTMENT_INFO b ON qa_customanswer = di_departmentid
WHERE   qa_qi_id = 1772499400
        AND qa_qd_id = 1367799500
GROUP BY b.di_name ,
        b.di_departmentid
ORDER BY COUNT(1) DESC


SELECT  COUNT(1) countdeptnum ,
        ui_department_id
INTO    #tempDept
FROM    USER_INFO
WHERE   ISNULL(ui_jop_status, 0) = 0
GROUP BY ui_department_id


SELECT  ( SELECT    di_name
          FROM      DEPARTMENT_INFO s
          WHERE     s.di_departmentid = ui_department_id
        ) 部门名称 ,
        countdeptnum 部门总人数 ,
        ISNULL(countnum, 0) 总提交人数
FROM    #tempDept
        LEFT JOIN #tempTable ON ui_department_id = di_departmentid
ORDER BY countdeptnum DESC


DROP TABLE   #tempTable

DROP TABLE   #tempDept

查询部门，未填写调查问卷的用户的名称

SELECT  ui_user_name ,
        qa_qi_id
FROM    USER_INFO
        LEFT JOIN QUESTIONNAIRE_ANSWER ON qa_customanswer = ui_department_id
                                          AND ui_userid = qa_userid
                                          AND qa_qi_id = 1772499400
                                          AND qa_qd_id = 1367799500
WHERE   ISNULL(ui_jop_status, 0) = 0
        AND ui_department_id = 10
        AND qa_qi_id IS NULL