exam item

#encoding=utf-8

import re
import pdb
q = []
a = []
item = dict()

#P = "(?<=q\d{1}\)).*?(?=a\d{1}\))"
P = "(?<=q\d{1}\)).*?\?"
f = open("cobol.txt", "r").read()
m = re.split("[QA]\d{1,4}\)", f)

for i, j in enumerate(m):
    if i % 2:
        q.append(j.strip().strip('\n'))
    else:
        a.append(j.strip().strip('\n'))
a = a[1:]
for i in range(len(q)):
    item[q[i]] = a[i]

pdb.set_trace()


#if __name__ == '__main__':
#    run()

WITH required_exams AS ( -- 获取 lookup 中配置的有效考试信息,并按 type 拆分 SELECT li.item_code, li.item_name, CASE WHEN instr(li.item_attr4, '技术复核') > 0 THEN '技术复核' WHEN instr(li.item_attr4, '综合复核') > 0 THEN '综合复核' ELSE '其他' END AS exam_type FROM tpl_lookup_item_t li WHERE li.classify_code = 'INTERVIEW_EXAM_INFO' AND li.status = '1' AND (instr(li.item_attr4, '技术复核') > 0 OR instr(li.item_attr4, '综合复核') > 0) ), user_passed_exams AS ( -- 原始查询提取用户已通过的考试记录 select '技术复核' as type, t.user_id, t.emp_num, t.exam_code, t.exam_name from omp_exam_record_t t left join tpl_lookup_item_t li on li.classify_code = 'INTERVIEW_EXAM_INFO' and li.status = '1' and li.item_code = t.exam_code where t.is_pass = '1' and instr(li.item_attr4, '技术复核') > 0 group by t.user_id, emp_num, t.exam_code, t.exam_name union all select '综合复核' as type, t.user_id, t.emp_num, t.exam_code, t.exam_name from omp_exam_record_t t left join tpl_lookup_item_t li on li.classify_code = 'INTERVIEW_EXAM_INFO' and li.status = '1' and li.item_code = t.exam_code where t.is_pass = '1' and instr(li.item_attr4, '综合复核') > 0 group by t.user_id, emp_num, t.exam_code, t.exam_name ) SELECT upe.user_id, upe.emp_num, re.exam_type AS type, re.item_code AS missing_exam_code, re.item_name AS missing_exam_name FROM (SELECT DISTINCT user_id, emp_num, type exam_type FROM user_passed_exams) upe JOIN required_exams re ON upe.exam_type = re.exam_type LEFT JOIN user_passed_exams passed ON upe.user_id = passed.user_id AND re.item_code = passed.exam_code WHERE passed.exam_code IS NULL AND upe.user_id IN (641524425026919, 421000703596799, 661571615791631, 661572063916081, 421000515162862, 701667467488593) ORDER BY upe.user_id, re.exam_type; 帮我改写为 查询通过 综合复核 和 技术复核 类型的考试,及用户
07-25
-- 复核官考试表,每个类别都需要通过lookup配置的全部考试才算通过,目前【技术复核】和【筛选复核】各3个 select a.* from (select '技术复核' as type, t.user_id, t.emp_num, t.exam_code, t.exam_name, (select count(1) from tpl_lookup_item_t li where li.classify_code = 'INTERVIEW_EXAM_INFO' and li.status = '1' and li.item_attr2 = '有效' and instr(li.item_attr4, '技术复核') > 0) as exam_count from omp_exam_record_t t left join tpl_lookup_item_t li on li.classify_code = 'INTERVIEW_EXAM_INFO' and li.status = '1' and li.item_code = t.exam_code where t.is_pass = '1' and instr(li.item_attr4, '技术复核') > 0 group by t.user_id, emp_num, t.exam_code, t.exam_name union all select '综合复核' as type, t.user_id, t.emp_num, t.exam_code, t.exam_name, (select count(1) from tpl_lookup_item_t li where li.classify_code = 'INTERVIEW_EXAM_INFO' and li.status = '1' and li.item_attr2 = '有效' and instr(li.item_attr4, '综合复核') > 0) as exam_count from omp_exam_record_t t left join tpl_lookup_item_t li on li.classify_code = 'INTERVIEW_EXAM_INFO' and li.status = '1' and li.item_code = t.exam_code where t.is_pass = '1' and instr(li.item_attr4, '综合复核') > 0 group by t.user_id, emp_num, t.exam_code, t.exam_name) a where a.user_id in (641524425026919, 421000703596799, 661571615791631, 661572063916081, 421000515162862, 701667467488593) order by a.user_id, a.type, a.exam_name; -- lookup配置的有效考试 select * from tpl_lookup_item_t li where li.classify_code = 'INTERVIEW_EXAM_INFO' and li.status = '1'; 上面的两个SQL分别是查询人员考了哪些考试,以及每个类别必须考的考试,我想知道每个人漏考了哪些
07-18
评论
成就一亿技术人!
拼手气红包6.0元
还能输入1000个字符
 
红包 添加红包
表情包 插入表情
 条评论被折叠 查看
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值