1번
SELECT a.NAME , a.DATETIME
from ANIMAL_INS a
LEFT join ANIMAL_OUTS b
on a.ANIMAL_ID=b.ANIMAL_ID
WHERE b.ANIMAL_TYPE IS NULL
ORDER BY a.DATETIME
limit 3
SQL
복사
2번
SELECT b.USER_ID , b.NICKNAME , a.TOTAL_SALES
from (select WRITER_ID , SUM(PRICE) AS TOTAL_SALES
from USED_GOODS_BOARD
where STATUS='DONE'
group by WRITER_ID) a
inner join USED_GOODS_USER b
ON a.WRITER_ID=b.USER_ID
WHERE a.TOTAL_SALES>=700000
order by a.TOTAL_SALES ASC
SQL
복사
3번
SELECT a.ANIMAL_ID,a.ANIMAL_TYPE,a.NAME
from ANIMAL_INS as a
join ANIMAL_OUTS as b
on a.ANIMAL_ID=b.ANIMAL_ID
where a.SEX_UPON_INTAKE like 'Intact%' and (b.SEX_UPON_OUTCOME like 'Spayed%' or b.SEX_UPON_OUTCOME like 'Neutered%')
SQL
복사
4번
select
case when b.game_account_id is null then '결제안함'
else '결제함' end as gb , count(distinct a.game_account_id ) as usercnt
from users as a
left join payment as b
on a.game_account_id =b.game_account_id
group by gb
SQL
복사
5번
select a.game_account_id , count(distinct a.game_actor_id ) as actor_cnt , avg(b.total_amt ) as avg_amount
from users as a
inner join(
select game_account_id,sum(pay_amount ) as total_amt
from payment
where pay_type ='CARD'
group by game_account_id ) as b
on a.game_account_id =b.game_account_id
where a.serverno >=2
group by a.game_account_id
having actor_cnt>=2
order by avg_amount desc
SQL
복사
6번
select serverno,ROUND(AVG(diffdate)) AS avgdiffdate
from(
select u.serverno , DATEDIFF(p.date2, u.first_login_date) AS diffdate
from
(select game_account_id ,min(first_login_date) as first_login_date ,serverno
from users
where ip_addr like "70%"
GROUP BY game_account_id,serverno) u
inner join
(select b.game_account_id,max(b.approved_at) as date2
from payment b
group by b.game_account_id) p
on u.game_account_id = p.game_account_id
where p.date2 > u.first_login_date ) as kkk
where diffdate >=10
GROUP BY serverno
ORDER BY serverno DESC
SQL
복사