SQL查询组。
|
粘贴了当前用于从unix shell脚本生成报告的sql查询。我想在下面的sql查询中添加一个附加列COUNT（cdw.file_id）writeoffcnt，以显示
匹配mg_disp_status = 0和mig_disp_code = 3的记录的计数。
现有字段COUNT（cdw.file_id）cnt应该具有与记录匹配的记录数
mg_disp_status = 1和mig_disp_code <>2。如何修改查询？
SELECT fs.file_id,
fs.file_id_serv,
fs.file_process_dt,
fs.file_name,
fs.total_records,
RTRIM (d.description_text) source,
SUM(amount),
COUNT (cdw.file_id) cnt
FROM file_status fs,
dr_data_work cdw,
descriptions d,
contacts ec
WHERE file_process_dt >= TO_DATE (\'${START_DATE}\', \'DD-MON-YYYY\')
AND file_process_dt < TO_DATE (\'${END_DATE}\', \'DD-MON-YYYY\')
AND fs.ext_contact_id = ec.ext_contact_id
--
AND ec.description_code = d.description_code
AND cdw.file_id = fs.file_id
AND mg_disp_status = 1
AND mig_disp_code <> 2
GROUP BY fs.file_id,
fs.file_id_serv,
fs.file_process_dt,
fs.file_name,
fs.total_records,
RTRIM (d.description_text);
没有找到相关结果
已邀请:
2 个回复
坝胺绣敝
乏摩纶誊伟
SELECT fs.file_id, fs.file_id_serv, fs.file_process_dt, fs.file_name, fs.total_records, RTRIM (d.description_text) source, SUM(amount), COUNT (cdw.file_id) cnt1, COUNT (cdw2.file_id) cnt2 FROM file_status fs, dr_data_work cdw, dr_data_work cdw2, descriptions d, contacts ec WHERE file_process_dt >= TO_DATE (\'${START_DATE}\', \'DD-MON-YYYY\') AND file_process_dt < TO_DATE (\'${END_DATE}\', \'DD-MON-YYYY\') AND fs.ext_contact_id = ec.ext_contact_id -- AND ec.description_code = d.description_code AND cdw.file_id = fs.file_id AND cdw2.file_id = fs.file_id AND cdw.mg_disp_status = 1 AND cdw2.mg_disp_status = 0 AND cdw.mig_disp_code <> 2 AND cdw2.mg_disp_code = 3 GROUP BY fs.file_id, fs.file_id_serv, fs.file_process_dt, fs.file_name, fs.total_records, RTRIM (d.description_text);