SELECT b.book_id, a.author_name, date_format(b.published_date, "%Y-%m-%d") as published_date
from book as b join author as a on a.author_id = b.author_id
where b.category = '경제'
order by b.published_date;
select p.product_code as product_code, sum(o.sales_amount)*price as sales
from product as p join offline_sale as o on p.product_id = o.product_id
group by product_code
order by sales desc, product_code asc;
-- 코드를 입력하세요
SELECT i.ingredient_type as ingredient_type, sum(f.total_order) as total_order
from first_half as f, icecream_info as i
where i.flavor = f.flavor
group by i.ingredient_type
order by total_order;
-- 코드를 입력하세요
SELECT mcdp_cd as "진료과코드", count(distinct apnt_no) as "5월예약건수"
from appointment
where date_format(apnt_ymd, '%Y-%m') = "2022-05"
group by mcdp_cd
order by count(distinct apnt_no),"진료과코드";
이 문제에서 가장 골머리를 앓았던 부분은 order by 절 때문인 것 같다...
order by를 "5월예약건수", "진료과코드"라고 하면 오답이 나온다...
select mcdp_cd as 진료과코드, count(*) as 5월예약건수
from appointment
where date_format(apnt_ymd, '%y-%m') like '22-05'
group by mcdp_cd
order by 5월예약건수 asc, 진료과코드 asc;
SELECT car_type, count(*) as cars
from car_rental_company_car
where options like '%통풍시트%'
or options like '%열선시트%'
or options like '%가죽시트%'
group by car_type
order by car_type;
SELECT (price div 10000)*10000 as price_group, count(*) as products
from product
group by price_group
order by price_group;
group by에 대해 배울 수 있는 문제였다.
항상 전체를 어떻게 다뤄야하나... 코딩만 하다보니 for문 돌려야하나... 이러는 것 같다
연습해야지 어쩌겠어!!
set @a = 0;
select (@a := @a + 10000) as PRICE_GROUP,
(select count(*)
from PRODUCT
where @a <= price and price < @a+10000) as PRODUCTS
from PRODUCT
where @a < price
order by PRICE_GROUP;
그리고 다른 분의 코드를 보게 되었다!!
이렇게도 쓸 수 있구나.. 신기하다....
SELECT TRUNCATE(price,-4) AS price_group, COUNT(product_id) AS products
FROM product
GROUP BY price_group
ORDER BY price_group;
SELECT distinct a.user_id, a.product_id
from online_sale as a join online_sale as b
on a.user_id=b.user_id and a.product_id=b.product_id
where a.sales_date != b.sales_date
order by user_id, product_id desc;
self join으로 해보았다! self join은 처음 활용해봤는데 오묘하다...
SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) > 1
ORDER BY USER_ID, PRODUCT_ID DESC;
또한, group by를 사용해서 풀 수도 있더라..
SELECT USER_ID, PRODUCT_ID,count(*)
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID;
이 sql문을 작성해보면, 다음과 같은 결과가 나온다.
USER_ID
PRODUCT_ID
count(*)
2
21
1
2
26
1
2
30
1
4
27
1
5
12
1
7
1
1
7
5
1
12
7
1
13
27
1
14
18
1
15
12
2
19
26
1
20
24
1
22
8
1
23
7
1
23
26
1
24
2
1
27
3
1
29
15
1
29
26
1
34
6
1
35
8
1
37
1
1
41
1
1
42
9
1
42
19
1
42
30
1
43
6
1
45
15
1
46
1
1
50
24
1
52
15
1
53
12
1
54
11
1
55
30
1
56
28
1
57
11
1
57
25
1
58
11
1
58
26
1
59
14
1
60
15
1
61
20
1
62
29
1
65
19
1
66
4
1
66
21
1
68
1
1
68
6
1
68
15
1
70
27
1
72
12
1
73
8
1
73
21
1
73
25
1
74
9
1
78
4
1
78
6
1
78
19
1
81
20
1
84
8
1
87
24
1
88
28
1
90
24
1
91
21
1
92
26
1
93
3
1
98
3
1
99
27
1
101
9
1
102
18
1
103
29
1
108
27
1
109
5
1
109
13
1
110
9
1
111
30
1
113
15
1
114
1
1
114
12
1
117
7
1
117
12
1
117
22
1
118
1
1
119
12
2
120
4
1
120
29
1
121
14
1
123
14
1
123
25
1
126
9
1
126
21
1
127
29
1
131
19
1
133
17
1
134
18
1
134
22
1
134
26
1
138
4
1
139
9
1
140
4
1
143
15
1
145
8
1
149
8
1
151
26
1
152
7
1
154
7
1
154
29
1
157
30
1
160
23
1
162
30
1
163
6
1
170
24
1
170
25
1
177
9
1
180
30
1
183
14
1
184
24
1
189
25
1
190
25
1
처음에는 어떻게 이렇게 가능한거지.. 이해가 안 갔는데, 다음 블로그르 참고하면 이해하기가 좀 쉬울것이다!
SELECT member_id, member_name, gender, date_format(date_of_birth, "%Y-%m-%d") as date_of_birth
from member_profile
where gender = "W" and month(date_of_birth)="03" and tlno is not null
order by member_id;