쿼리수정중
페이지 정보
본문
from ( SELECT t1.company_id,
t1.comp_nm,
t1.hap_fee_free_yn,
t1.fee_area_rel_no,
t1.lb_no,
t1.kg_no,
t1.min_level_no,
t1.max_level_no,
t1.discount_rate_yn,
t1.unit_type,
t1.currency ,
CASE WHEN t1.discount_rate_yn = 1 THEN
(SELECT m1.fee - ceiling(m1.fee * (m2.discount_rate/100)*100)/100
FROM dev_zik_company_fee m1,
dev_zik_company_member_level m2
WHERE m1.company_id = t1.company_id AND m1.unit_no = if(t1.unit_type='lb',t1.lb_no,t1.kg_no) AND m1.fee_area_rel_no = t1.fee_area_rel_no AND m1.level_no = 1 AND m2.company_id = m1.company_id AND m2.level_no = t1.min_level_no
) ELSE
(SELECT fee
FROM dev_zik_company_fee
WHERE company_id = t1.company_id AND unit_no = if(t1.unit_type='lb',t1.lb_no,t1.kg_no) AND fee_area_rel_no = t1.fee_area_rel_no AND level_no = t1.min_level_no
) END as min_level_fee ,
CASE WHEN t1.discount_rate_yn = 1 THEN
(SELECT m1.fee - ceiling(m1.fee * (m2.discount_rate/100)*100)/100
FROM dev_zik_company_fee m1,
dev_zik_company_member_level m2
WHERE m1.company_id = t1.company_id AND m1.unit_no = if(t1.unit_type='lb',t1.lb_no,t1.kg_no) AND m1.fee_area_rel_no = t1.fee_area_rel_no AND m1.level_no = 1 AND m2.company_id = m1.company_id AND m2.level_no = t1.max_level_no
) ELSE
(SELECT fee
FROM dev_zik_company_fee m1
WHERE company_id = t1.company_id AND unit_no = if(t1.unit_type='lb',t1.lb_no,t1.kg_no) AND fee_area_rel_no = t1.fee_area_rel_no AND level_no = t1.max_level_no
) END as max_level_fee ,
CASE WHEN t1.currency = 'usd' THEN 1133.45 WHEN t1.currency = 'eur' THEN 1250.25 WHEN t1.currency = 'jpy' THEN 9.41 WHEN t1.currency = 'cny' THEN 178.27 WHEN t1.currency = 'won' THEN 1 ELSE 0 END as exchange_rate
FROM
(SELECT a.company_id,
a.fee_area_rel_no,
d.comp_nm,
d.hap_fee_free_yn,
c.lb_no,
c.kg_no,
e.min_level_no,
e.max_level_no,
b.unit_type,
b.currency ,
if('min'='min',e.min_level_no, e.max_level_no) as level_no ,
d.discount_rate_yn
FROM dev_zik_fee_area_relation a ,
dev_zik_company_addr b ,
(SELECT if(kg_no='0.3','0.5',kg_no) as kg_no,
kg2lb,
lb_no
FROM dev_zik_unit_convert
) c ,
dev_zik_company d ,
(SELECT company_id,
min(level_no) as min_level_no,
max(level_no) as max_level_no
FROM dev_zik_company_member_level
GROUP BY company_id
) e
WHERE 1=1
AND a.addr_area = 'OR'
AND a.use_yn = 1
AND b.company_id = a.company_id
AND b.addr_area = a.addr_area
AND if('lb'='lb',c.lb_no,c.kg_no) = 1
AND d.id = a.company_id
AND d.use_yn = 1
AND e.company_id = d.id
and a.company_id = 1005
) t1
) m0, dev_zik_company_member_level m1, dev_zik_company_member_level m2
where m1.company_id = m0.company_id
and m1.fee_area_rel_no = m0.fee_area_rel_no
and m1.level_no = m0.min_level_no
and m2.company_id = m0.company_id
and m2.fee_area_rel_no = m0.fee_area_rel_no
and m2.level_no = m0.max_level_no
- 이전글나눔행사 15.12.23
- 다음글이하넥스 회원권한 변경내용 15.10.28
댓글목록
최고관리자님의 댓글
최고관리자 작성일
SELECT t2.company_id,
t2.comp_nm,
t2.hap_fee_free_yn,
t2.fee_area_rel_no,
t2.lb_no,
t2.kg_no,
t2.min_level_no,
t2.max_level_no,
t2.discount_rate_yn,
t2.unit_type,
t2.currency ,
t2.min_level_fee,
t2.max_level_fee ,
(SELECT level_kor_nm
FROM dev_zik_company_member_level
WHERE company_id = t2.company_id
AND level_no = t2.min_level_no
) as min_level_nm ,
(SELECT level_kor_nm
FROM dev_zik_company_member_level
WHERE company_id = t2.company_id
AND level_no = t2.max_level_no
) as max_level_nm ,
if(t2.currency='won',ceiling(t2.min_level_fee/t2.exchange_rate *100)/100, t2.min_level_fee) as min_level_fee_usd ,
if(t2.currency='won',ceiling(t2.max_level_fee/t2.exchange_rate *100)/100, t2.max_level_fee) as max_level_fee_usd ,
if(t2.currency != 'won', ceiling(t2.min_level_fee * t2.exchange_rate), t2.min_level_fee) as min_level_fee_won ,
if(t2.currency != 'won', ceiling(t2.max_level_fee * t2.exchange_rate), t2.max_level_fee) as max_level_fee_won
FROM
(select m0.company_id,
m0.comp_nm,
m0.hap_fee_free_yn,
m0.fee_area_rel_no,
m0.lb_no,
m0.kg_no,
m0.min_level_no,
m0.max_level_no,
m1.level_base as min_level_base ,
m2.level_base as max_level_base,
m0.discount_rate_yn,
m0.unit_type,
m0.currency,
CASE WHEN m0.discount_rate_yn = 1 THEN
(SELECT if(m1.level_base='fix',n1.fee - n1.fee_fix_diff,n1.fee) - ceiling(n1.fee * (m2.discount_rate/100)*100)/100
FROM dev_zik_company_fee n1,
dev_zik_company_member_level n2
WHERE n1.company_id = m0.company_id AND n1.unit_no = if(m0.unit_type='lb',m0.lb_no,m0.kg_no) AND n1.fee_area_rel_no = m0.fee_area_rel_no AND n1.level_no = 1 AND n2.company_id = n1.company_id AND n2.level_no = m0.max_level_no
) ELSE
(SELECT fee
FROM dev_zik_company_fee n1
WHERE company_id = m0.company_id AND unit_no = if(m0.unit_type='lb',m0.lb_no,m0.kg_no) AND fee_area_rel_no = m0.fee_area_rel_no AND level_no = m0.max_level_no
) END as min_level_fee ,
CASE WHEN m0.discount_rate_yn = 1 THEN
(SELECT if(m2.level_base='fix',n1.fee - n1.fee_fix_diff,n1.fee) - ceiling(n1.fee * (m2.discount_rate/100)*100)/100
FROM dev_zik_company_fee n1,
dev_zik_company_member_level n2
WHERE n1.company_id = m0.company_id AND n1.unit_no = if(m0.unit_type='lb',m0.lb_no,m0.kg_no) AND n1.fee_area_rel_no = m0.fee_area_rel_no AND n1.level_no = 1 AND n2.company_id = n1.company_id AND n2.level_no = m0.max_level_no
) ELSE
(SELECT fee
FROM dev_zik_company_fee n1
WHERE company_id = m0.company_id AND unit_no = if(m0.unit_type='lb',m0.lb_no,m0.kg_no) AND fee_area_rel_no = m0.fee_area_rel_no AND level_no = m0.max_level_no
) END as max_level_fee ,
CASE WHEN m0.currency = 'usd' THEN 1133.45 WHEN m0.currency = 'eur' THEN 1250.25 WHEN m0.currency = 'jpy' THEN 9.41 WHEN m0.currency = 'cny' THEN 178.27 WHEN m0.currency = 'won' THEN 1 ELSE 0 END as exchange_rate
from ( SELECT t1.company_id,
t1.comp_nm,
t1.hap_fee_free_yn,
t1.fee_area_rel_no,
t1.lb_no,
t1.kg_no,
t1.min_level_no,
t1.max_level_no,
t1.discount_rate_yn,
t1.unit_type,
t1.currency
FROM
(SELECT a.company_id,
a.fee_area_rel_no,
d.comp_nm,
d.hap_fee_free_yn,
c.lb_no,
c.kg_no,
e.min_level_no,
e.max_level_no,
b.unit_type,
b.currency ,
if('min'='min',e.min_level_no, e.max_level_no) as level_no ,
d.discount_rate_yn
FROM dev_zik_fee_area_relation a ,
dev_zik_company_addr b ,
(SELECT if(kg_no='0.3','0.5',kg_no) as kg_no,
kg2lb,
lb_no
FROM dev_zik_unit_convert
) c ,
dev_zik_company d ,
(SELECT company_id,
min(level_no) as min_level_no,
max(level_no) as max_level_no
FROM dev_zik_company_member_level
where 1=1
and level_base != 'fix'
GROUP BY company_id
) e
WHERE 1=1
AND a.addr_area = 'NJ'
AND a.use_yn = 1
AND b.company_id = a.company_id
AND b.addr_area = a.addr_area
AND if('lb'='lb',c.lb_no,c.kg_no) = 1
AND d.id = a.company_id
AND d.use_yn = 1
AND e.company_id = d.id
and a.company_id in (1048, 1005, 1045)
) t1
) m0, dev_zik_company_member_level m1, dev_zik_company_member_level m2
where m1.company_id = m0.company_id
and m1.level_no = m0.min_level_no
and m2.company_id = m0.company_id
and m2.level_no = m0.max_level_no
) t2
최고관리자님의 댓글
최고관리자 작성일
SELECT DISTINCT t3.company_id ,
t3.comp_nm ,
t3.hap_fee_free_yn ,
t3.fee_area_rel_no ,
t3.lb_no ,
t3.min_level_no ,
t3.max_level_no ,
t3.discount_rate_yn ,
t3.unit_type ,
t3.currency ,
t3.min_level_fee,
t3.max_level_fee ,
t3.min_level_fee_won ,
t3.max_level_fee_won ,
t3.min_level_fee_usd ,
t3.max_level_fee_usd ,
t3.min_level_nm ,
t3.max_level_nm
FROM
(SELECT t2.company_id,
t2.comp_nm,
t2.hap_fee_free_yn,
t2.fee_area_rel_no,
t2.lb_no,
t2.kg_no,
t2.min_level_no,
t2.max_level_no,
t2.discount_rate_yn,
t2.unit_type,
t2.currency ,
t2.min_level_fee,
t2.max_level_fee ,
(SELECT level_kor_nm
FROM dev_zik_company_member_level
WHERE company_id = t2.company_id
AND level_no = t2.min_level_no
) as min_level_nm ,
(SELECT level_kor_nm
FROM dev_zik_company_member_level
WHERE company_id = t2.company_id
AND level_no = t2.max_level_no
) as max_level_nm ,
if(t2.currency='won',ceiling(t2.min_level_fee/t2.exchange_rate *100)/100, t2.min_level_fee) as min_level_fee_usd ,
if(t2.currency='won',ceiling(t2.max_level_fee/t2.exchange_rate *100)/100, t2.max_level_fee) as max_level_fee_usd ,
if(t2.currency != 'won', ceiling(t2.min_level_fee * t2.exchange_rate), t2.min_level_fee) as min_level_fee_won ,
if(t2.currency != 'won', ceiling(t2.max_level_fee * t2.exchange_rate), t2.max_level_fee) as max_level_fee_won
FROM
(select m0.company_id,
m0.comp_nm,
m0.hap_fee_free_yn,
m0.fee_area_rel_no,
m0.lb_no,
m0.kg_no,
m0.min_level_no,
m0.max_level_no,
m1.level_base as min_level_base ,
m2.level_base as max_level_base,
m0.discount_rate_yn,
m0.unit_type,
m0.currency,
CASE WHEN m0.discount_rate_yn = 1 THEN
(SELECT if(m1.level_base='fix',n1.fee - n1.fee_fix_diff,n1.fee) - ceiling(n1.fee * (m2.discount_rate/100)*100)/100
FROM dev_zik_company_fee n1,
dev_zik_company_member_level n2
WHERE n1.company_id = m0.company_id AND n1.unit_no = if(m0.unit_type='lb',m0.lb_no,m0.kg_no) AND n1.fee_area_rel_no = m0.fee_area_rel_no AND n1.level_no = 1 AND n2.company_id = n1.company_id AND n2.level_no = m0.max_level_no
) ELSE
(SELECT fee
FROM dev_zik_company_fee n1
WHERE company_id = m0.company_id AND unit_no = if(m0.unit_type='lb',m0.lb_no,m0.kg_no) AND fee_area_rel_no = m0.fee_area_rel_no AND level_no = m0.max_level_no
) END as min_level_fee ,
CASE WHEN m0.discount_rate_yn = 1 THEN
(SELECT if(m2.level_base='fix',n1.fee - n1.fee_fix_diff,n1.fee) - ceiling(n1.fee * (m2.discount_rate/100)*100)/100
FROM dev_zik_company_fee n1,
dev_zik_company_member_level n2
WHERE n1.company_id = m0.company_id AND n1.unit_no = if(m0.unit_type='lb',m0.lb_no,m0.kg_no) AND n1.fee_area_rel_no = m0.fee_area_rel_no AND n1.level_no = 1 AND n2.company_id = n1.company_id AND n2.level_no = m0.max_level_no
) ELSE
(SELECT fee
FROM dev_zik_company_fee n1
WHERE company_id = m0.company_id AND unit_no = if(m0.unit_type='lb',m0.lb_no,m0.kg_no) AND fee_area_rel_no = m0.fee_area_rel_no AND level_no = m0.max_level_no
) END as max_level_fee ,
CASE WHEN m0.currency = 'usd' THEN 1133.45 WHEN m0.currency = 'eur' THEN 1250.25 WHEN m0.currency = 'jpy' THEN 9.41 WHEN m0.currency = 'cny' THEN 178.27 WHEN m0.currency = 'won' THEN 1 ELSE 0 END as exchange_rate
from ( SELECT t1.company_id,
t1.comp_nm,
t1.hap_fee_free_yn,
t1.fee_area_rel_no,
t1.lb_no,
t1.kg_no,
t1.min_level_no,
t1.max_level_no,
t1.discount_rate_yn,
t1.unit_type,
t1.currency
FROM
(SELECT a.company_id,
a.fee_area_rel_no,
d.comp_nm,
d.hap_fee_free_yn,
c.lb_no,
c.kg_no,
e.min_level_no,
e.max_level_no,
b.unit_type,
b.currency ,
if('min'='min',e.min_level_no, e.max_level_no) as level_no ,
d.discount_rate_yn
FROM dev_zik_fee_area_relation a ,
dev_zik_company_addr b ,
(SELECT if(kg_no='0.3','0.5',kg_no) as kg_no,
kg2lb,
lb_no
FROM dev_zik_unit_convert
) c ,
dev_zik_company d ,
(SELECT company_id,
min(level_no) as min_level_no,
max(level_no) as max_level_no
FROM dev_zik_company_member_level
where 1=1
and level_base != 'fix'
GROUP BY company_id
) e
WHERE 1=1
AND a.addr_area = 'NJ'
AND a.use_yn = 1
AND b.company_id = a.company_id
AND b.addr_area = a.addr_area
AND if('lb'='lb',c.lb_no,c.kg_no) = 1
AND d.id = a.company_id
AND d.use_yn = 1
AND e.company_id = d.id
and a.company_id in (1048, 1005, 1045)
) t1
) m0, dev_zik_company_member_level m1, dev_zik_company_member_level m2
where m1.company_id = m0.company_id
and m1.level_no = m0.min_level_no
and m2.company_id = m0.company_id
and m2.level_no = m0.max_level_no
) t2
) t3