[postgresql] Left join 예제
SELECT T1.category, T1.sumCnt AS sumCnt, T1.changedCnt, COALESCE(T2.deletedCnt, 0) FROM( SELECT COALESCE(category, 0) AS category, COUNT(id) AS sumCnt, SUM(CASE WHEN createdate != changedate AND SUBSTR(changedate,1,10) = '2015-01-26' THEN 1 ELSE 0 END) AS changedCnt FROM metadata GROUP BY category ) AS T1 LEFT OUTER JOIN (SELECT COALESCE(category, 0) AS category, SUM(CASE WHEN TO_CHAR(deletionda..
더보기
[Oracle SQL] 컬럼 추출 입력 예제
select nvl(sum(decode(mrkt,'N',1,0)),0), nvl(sum(decode(mrkt,'N',0,'H',0,1)),0), nvl(sum(decode(mrkt,'H',1,0)),0), nvl(sum(anum + cnum),0) into n_compcnt,n_occcnt,n_housecnt,n_guestcnt from fo_in_res where arrival to_date(v_date);
더보기
[MySQL SQL] IP매칭 SQL 예제
# IP 쪼개서 불려오기 SELECT COUNT(*) FROM OWSADMIPADDR WHERE (SUBSTRING_INDEX(IP_ADDR,'.',1) LIKE '***' OR SUBSTRING_INDEX(IP_ADDR,'.',1) LIKE SUBSTRING_INDEX('127.1.0.27','.',1)) AND (SUBSTRING_INDEX(SUBSTRING_INDEX(IP_ADDR,'.',2),'.',-1) LIKE '***' OR SUBSTRING_INDEX(SUBSTRING_INDEX(IP_ADDR,'.',2),'.',-1) LIKE SUBSTRING_INDEX(SUBSTRING_INDEX('127.1.0.27','.',2),'.',-1)) AND (SUBSTRING_INDEX(SUBSTRING..
더보기
