subject 다양한 쿼리 예제들
author 미랑 date 2005-10-24 hit 202 HIT

월간 카테고리별 매출
select depth_3 as menu, sum(money) from log
where Category = 'normal' and hit_day like '2005-04%'
group by menu


월간 작가별 매출(높은금액순)  
select author as 작가, sum(money) as 금액 from log
where Category = 'normal' and hit_day like '2005-04%'
group by 작가
order by 금액 desc

하루 매출 상위10위
select title as 컨텐츠, sum(money) as 금액 from log
where Category = 'normal' and hit_day = '2005-05-05'
group by 컨텐츠
order by 금액 desc
limit 10

하루 매출 상위 11~15위
select title as 컨텐츠, sum(money) as 금액 from log
where Category = 'normal' and hit_day = '2005-05-05'
group by 컨텐츠
order by 금액 desc
limit 10, 5  

카테고리별 일일 매출
select hit_day as 날짜, depth_1 as 메뉴, sum(money) as 금액 from log
where Category = 'normal'  and hit_day < '2005-07-17' and hit_day > '2005-07-09'
group by 날짜, 메뉴
order by 날짜 desc, 메뉴 asc

컨텐츠별 상위 5/1~5/5 상위10위
select title as 제목, money as 금액, hit_day as 날짜 from log
where Category = 'normal' and hit_day < '2005-05-06' and hit_day > '2005-04-30'
order by money desc  
limit 10

성인작가별 컨텐츠 월매출
select author as author, count(*) as hit, sum(money) as money from log
where Category='adult' and Hit_Day like '2005-05%'
group by author
order by money desc

월간 카테고리별 매출체크
select depth_2 as menu, sum(money) from log
where Category = 'normal' and depth_1 = '3' and hit_day like '2005-04%'
group by menu

만화제목별 카테고리 체크
select title as menu, depth_3, sum(money) from log
where Category = 'normal' and depth_1 = '3' and depth_2 = '2' and hit_day like '2005-04%'
group by menu

카테고리별 4월 전체매출
select title as menu, depth_2, sum(money) from log
where Category = 'normal' and depth_3 and hit_day like '2005-04%'
group by menu

작가,컨텐츠,금액 월별통계
select author as 작가, title as 컨텐츠, sum(money) as 금액 from log
where Category = 'adult' and hit_day like '2005-04%'
group by 작가, 컨텐츠
order by 금액 desc

성인메뉴명
select * from service
where kind = 'd' and depth_2 = '0' and category = 'adult'
order by depth_1

서비스누적일/제목/총히트수
select count(*) as '서비스누적일', title as '제목', sum(hit) as '총히트수', round(avg(hit)) as '평균히트수', sum(money) as '총금액', round(avg(money)) as '평균금액' from log
where category = 'normal' and depth_1 = '4' and hit_day like '2005-06%'
group by '제목'
order by '평균금액' desc

성인/서비스누적일/제목/총히트수
select count(*) as '서비스누적일', title as '제목', sum(hit) as '총히트수', round(avg(hit)) as '평균히트수', sum(money) as '총금액', round(avg(money)) as '평균금액' from log
where category = 'adult' and depth_1 = '3' and hit_day < '2005-07-2' and hit_day > '2005-06-30'
group by '제목'
order by '평균금액' desc

select hit_day as 날짜,
case depth_1
when '1' then '몽정기1'
when '2' then '불량만화'
when '3' then 'BEST'
when '4' then '만화가게'
when '6' then '이토준지'
when '7' then '오싹오싹'
when '10' then '안녕형아'
when '11' then '화끈지기'
when '12' then '몽정기2'
else 'NULL' end as '메뉴',
sum(money) as 금액 from log
where Category = 'normal' and hit_day < '2005-07-07' and hit_day > '2005-07-16'
group by 날짜, 메뉴
order by 금액 desc, depth_1 asc

select case depth_1
when '1' then '핸폰 로보자!몽정기1'
when '2' then '21C불량만화'
when '3' then '초강추BEST'
when '4' then '[HIT]만화가게'
when '6' then '[이토준지공포망가]'
when '7' then '[NEW]오싹오싹파라 이스'
when '10' then '만화박스'
when '11' then '화끈지기추천만화'
when '12' then '먼저보자!몽정기2'
else 'NULL' end as '메뉴',
format(sum(money),0) as '금액' from log
where Category = 'normal' and hit_day = '2005-06-26'
group by '메뉴'
order by depth_1 asc

매출용쿼리
select hit_day as 날짜,
case depth_1
when '1' then '몽정기1'
when '2' then '불량만화'
when '3' then 'BEST'
when '4' then '만화가게'
when '6' then '이토준지'
when '7' then '오싹오싹'
when '10' then '안녕형아'
when '11' then '화끈지기'
when '12' then '몽정기2'
else 'NULL' end as '메뉴',
sum(money) as 금액 from log
where Category = 'normal' and hit_day > '2005-07-7' and hit_day > '2005-07-1'
group by 날짜, 메뉴
order by 날짜 asc,금액 desc

*한달 방문자 전체 히트수
select sum(hit) from log
where category = 'normal' and hit_day like '2005-06%'
목록보기
        X  
17  조인 쿼리 예제  2006-05-04 163
16  [쿼리문] 데이타 파일에서 자료 읽어오기 (load data)  2005-09-19 89
15  두근두근만화방 로그변환 DB 업데이트 소스  2005-09-16 144
14  데이타 덤프 와 복구  2006-10-21 238
13  원격 접속 열기  2006-05-26 238
12  MySql 통합 접속툴 - phpMyAdmin-2.8.0.3 설치  2006-04-24 259
 다양한 쿼리 예제들  2005-10-24 202
10  Group by 사용 예제  2004-10-04 346
9  중복행체크.. Distinct 사용..  2004-10-04 310
8  MySQL DB 가 깨졌을때 살리는법  2003-11-17 202
7  MySQL 복구하기  2003-11-17 249
6  MySQL 유저 등록하기 (원격접속포함)  2003-11-17 198
5  MySQL 데이타를 text 혹은 excel 파일로 받는 법  2003-11-17 106
4  필드 최적화 체크 방법  2003-11-17 88
3  초기 루트 사용자 설정  2003-11-17 82
1 2