端端舝

仇氏卅犯奈正互郗仄中媆﹜
SQL反仇丹𤩸仁
苤輿滂䀝

赻撩畿賡
靡ゴㄩ苤輿滂䀝(@ykyk1218)
Qiitaㄩhttps://qiita.com/ykyk1218

�允仇午
1. ゜葆午呾胍栳呾赽
2. 元扎卅中源弁巨伉
3. 堯硃措勳紳眶卞𠸎毛勾仃欠
及3掛匹允

ゴ枑
orders
users
items
item_ma
sters1�嗣嗣�1 嗣�1
嗣
�
1
order_de
tails
犯奈正矛奈旦反MySQL

妀ⅲ
phonecase: 旦穴石弗奈旦
walletphonecaseㄩ忒絝倰旦穴石弗奈旦
betteryㄩ乒田奶伙田永氾伉奈

�允仇午

゜葆午呾胍栳呾赽
云觳1
蛁恅゜互2018爛1堎8゜井日2018爛1堎23゜引匹及
犯奈正互郗仄中

2018爛1堎8゜井日2018爛1堎23゜引匹及犯奈正
select * from orders
where date(created_at)>='2018-01-08' AND
date(created_at) <= '2018-01-23';
�仁卅中

where created_at
between '2018-01-08' AND '2018-01-23';
母丟

謎中
where created_at >= '2018-01-08' AND
created_at < '2018-01-24';

゜葆午呾胍栳呾赽
云觳2
秏愐阭煋心及�跡匹﹜1000�眕奻及妀ⅲ毛龰腕允
月

秏愐阭煋心及�跡(price)匹1000�眕奻及妀ⅲ
select * from items i
where i.price*1.08 > 1000
�仁卅中

秏愐阭煋心及�跡(price)匹1000�眕奻及妀ⅲ
select * from items i
where i.price > 1000 / 1.08
謎中

�允仇午

元扎卅中源弁巨伉
云觳
引分�⻌今木化中卅中妀ⅲ毛龰腕允月

丐引曰方仁卅中...井手
select i.id, i.sti_type from items i
where i.id not in
(select item_id from order_details)

�蛁恅��犯奈正毛龰腕允月扔皮弁巨伉
where i.id not in
(select item_id from order_details)

where not exists
(select od.id from order_details od where
od.item_id=i.id)

LEFT JOIN order_details od ON i.id=od.item_id
where od.item_id is NULL

元扎卅中源弁巨伉𡛟蚚�
旦穴石弗奈旦毛惁勻凶互﹜田永氾伉奈毛惁勻化中卅中
�諦及伉旦玄

select u.id, u.email from users u where
( 1 =
(case when u.id not in
(select o.user_id from orders o INNER JOIN order_details od ON
o.id=od.order_id INNER JOIN items i ON od.item_id=i.id where
i.sti_type='phonecase')
then 0
when u.id in
i.sti_type='battery')
then 0 else 1 end)
);

( 1 =
then 0
when u.id in
then 0 else 1 end)
);
phonecase毛蛁恅仄化中卅中交奈扒奈毛龰腕

( 1 =
then 0
when u.id in
then 0 else 1 end)
);
battery毛蛁恅仄凶交奈扒奈毛龰腕

select u.id, u.email from users u where u.id in (
select o.user_id from orders o
INNER JOIN order_details od ON o.id=od.order_id
INNER JOIN items i ON od.item_id=i.id
INNER JOIN users u ON o.user_id=u.id
WHERE i.sti_type='phonecase'
) AND u.id not in (
select o.user_id from orders o
INNER JOIN order_details od ON o.id=od.order_id
WHERE i.sti_type='battery'
)

having 卞𠸎毛勾仃欠
云觳
忒絝倰旦穴石弗奈旦及�⻌隙杅互
5隙方曰屾卅中交奈扒奈毛龰腕

select u.id, count(o.id) from orders o
INNER JOIN order_details od ON
o.id=od.order_id
where i.sti_type='walletphonecase'
group by u.id
having count(o.id) < 5

睡互�觳井
0璃及犯奈正互龰腕匹五卅中

select u.email, count(order_filter.order_id) from
users u
LEFT OUTER JOIN
(select o.id oid, o.user_id uid from orders o
o.id=od.order_id
) order_filter ON order_filter.user_id = u.id
group by u.id
having count(order_filter.oid) < 5
order by count(order_filter.oid)

users u
LEFT OUTER JOIN
o.id=od.order_id
group by u.id
俋窒磐磁匹JOIN毛仄化蛁恅仄化中卅中交奈扒奈手龰
腕允月

users u
LEFT OUTER JOIN
o.id=od.order_id
group by u.id
count(*)分午0璃及猁匼互市它件玄
今木卅中

引午戶
where曆匹掀廌�砓及市仿丞卞憝杅支�呾宒毛妏丹午奶件犯永弁
旦互�井卅仁卅月
妀ⅲA毛�⻌仄化﹜妀ⅲB毛�⻌仄化中卅中交奈扒奈及伉旦玄反
where inㄗwhere not inㄘ及沭璃煦卞扔皮弁巨伉毛勾井勻化
龰腕匹五月
having < count(x)毛妏丹�磁反0璃及�磁卞甲永玄仄卅中褫夔
俶互丐月及匹𠸎毛勾仃月﹝�𡛟允月卞反俋窒磐磁毛妏丹

端端舝

仇氏卅犯奈正互郗仄中奀﹜釦紮郭反仇丹抎仁

More Related Content

仇氏卅犯奈正互郗仄中奀﹜釦紮郭反仇丹抎仁