데이터베이스 질문과 답변
안녕하세요.
중복관련 쿼리질문 남겨드립니다. 질문내용이 미흡하거나 추가정보가 더 필요한게 있으면 수정하겠습니다.
6월에 등록된 kno를 중복제거 후 카운트한 쿼리입니다. 첨부파일은 쿼리실행 결과화면입니다.
여기서 추가하고 싶은게 있는데 6월이전에 등록이 된 kno가 있으면 이것을 제외한후 6월에 새로 등록된것만
카운트를 해주고 싶습니다.
SELECT AGROUP,
KNO
FROM (SELECT AGROUP,
KNO
FROM (SELECT CO.ACODE AGROUP,
COUNT(DISTINCT (PM.KNO)) KNO
FROM (SELECT ACODE, ANAME, APRCODE
FROM se_as_code
WHERE USEYN = 'Y' AND CLCODE = 'AS' AND ALVL != '3')
CO
LEFT OUTER JOIN se_program_manage PM
ON CO.ACODE = PM.AGROUP
AND PM.SDATE <
date_add(
date_format('2018-06-01', '%Y-%m-%d'),
INTERVAL +1 MONTH)
AND PM.SDATE >= '2018-06-01'
WHERE 1 = 1 AND left(CO.ACODE, 2) = '03'
GROUP BY CO.ACODE
ORDER BY CO.ACODE ASC) A
GROUP BY AGROUP
ORDER BY AGROUP) B
by 마농
[2018.09.20 11:00:38]
SELECT agroup
, aname
, COUNT(*) kno
FROM (SELECT co.acode agroup
, co.aname
, pm.kno
FROM se_as_code co
LEFT OUTER JOIN se_program_manage pm
ON co.acode = pm.agroup
AND pm.sdate < DATE_ADD('2018-06-01', INTERVAL 1 MONTH)
WHERE co.useyn = 'Y'
AND co.clcode = 'AS'
AND co.alvl != '3'
AND co.acode LIKE '03%'
GROUP BY co.acode, co.aname, pm.kno
HAVING MIN(pm.sdate) >= '2018-06-01'
) a
GROUP BY agroup, aname
ORDER BY agroup
;
by 밍밍밍밍
[2018.09.20 13:43:14]
답변감사드립니다~
혹시 kno값이 0이라도 쿼리실행시 0으로 나오게할수있을까요?
by 마농
[2018.09.20 14:22:19]
SELECT agroup
, aname
, COUNT(kno) kno
FROM (SELECT co.acode agroup
, co.aname
, CASE WHEN MIN(pm.sdate) >= '2018-06-01' THEN pm.kno END kno
FROM se_as_code co
LEFT OUTER JOIN se_program_manage pm
ON co.acode = pm.agroup
AND pm.sdate < DATE_ADD('2018-06-01', INTERVAL 1 MONTH)
WHERE co.useyn = 'Y'
AND co.clcode = 'AS'
AND co.alvl != '3'
AND co.acode LIKE '03%'
GROUP BY co.acode, co.aname, pm.kno
) a
GROUP BY agroup, aname
ORDER BY agroup
;
by 밍밍밍밍
[2018.09.20 16:31:51]
답변감사드립니다!
해결했습니다!!
) 버튼을 클릭하여 작성 하시면 됩니다.