use kiinDB;
select name as ๊ณ ๊ฐ๋ช ,
passwd as ๋น๋ฐ๋ฒํธ,
email as ์ด๋ฉ์ผ,
number as ์ฐ๋ฝ์ฒ,
address as ์ฃผ์,
profile_img as ํ๋กํ์ฌ์ง,
concat(membership, 'ํ์') as ๋ฉค๋ฒ์ญ
from user u
inner join (select membership, user_id from membership) m on u.id = m.user_id
inner join (select address, user_id, min(id) from address) a on u.id = a.user_id
where u.id = 1
- ์ฃผ๋ฌธ๋ชฉ๋ก
use kiinDB;
select o.created_at as ์ฃผ๋ฌธ์ผ์,
o.updated_at,
shipment as ๋ฐฐ์ก์ข ๋ฅ,
shipment_img_url as ๋ฐฐ์ก์ด๋ฏธ์ง,
concat(name, space(1), o2.option) as ์ํ์ด๋ฆ,
price as ๊ฐ๊ฒฉ,
stock as ์ฃผ๋ฌธ๊ฐ์,
prod_img as ์ํ์ด๋ฏธ์ง,
(case
when
o.status = 'Y'
then '๋ฐฐ์ก์๋ฃ'
else '๋ฐฐ์ก ์ค'
end) as ๋ฐฐ์ก์ํ,
(case
when
o.status = 'Y' and datediff(o.updated_at,current_timestamp) < 1
then concat('์ด์ ',space(1),date_format(o.updated_at,'%m/%d/%W'),space(1),'๋์ฐฉ')
when
o.status = 'Y' and datediff(o.updated_at,current_timestamp) > 1
then concat(date_format(o.updated_at,'%m/%d/%W'),'๋์ฐฉ')
when o.status= 'N' and date_format(o.created_at,'%h') < 12
then '์ค๋ ์คํ ๋์ฐฉ ๋ณด์ฅ'
else concat(date_format(DATE_ADD(o.created_at, INTERVAL 1 DAY),'%m/%d/%W'),'๋์ฐฉ๋ณด์ฅ')
end) as ๋ฐฐ์ก์ํ
from order_ o
inner join shipments_mapping sm on o.prod_id = sm.prod_id
inner join shipments s on sm.shipment_id = s.id
inner join product p on o.prod_id = p.id
inner join option_mapping om on p.id = om.prod_id
inner join option_ o2 on om.option_id = o2.id
where o.buyer_id = 1
and o2.id = 1;
-์ฃผ๋ฌธ์์ธ
use kiinDB;
select user.name as ์ด๋ฆ,
payment as ๊ฒฐ์ ์๋จ,
number as ํธ๋ํฐ๋ฒํธ,
address as ์ฃผ์,
o.created_at as ๊ตฌ์ ์ผ์,
o.id as ์ฃผ๋ฌธ๋ฒํธ,
shipment as ๋ฐฐ์ก์ข ๋ฅ,
shipment_img_url as ๋ฐฐ์ก์ด๋ฏธ์ง,
(case
when s.id = '1' then '0์'
when s.id = '2' then '0์'
else '3000์' end) as ๋ฐฐ์ก๋น,
concat(p.name, space(1), o2.option) as ์ํ์ด๋ฆ,
price as ๊ฐ๊ฒฉ,
stock as ์ฃผ๋ฌธ๊ฐ์,
prod_img as ์ํ์ด๋ฏธ์ง,
(case
when
o.status = 'Y'
then '๋ฐฐ์ก์๋ฃ'
else '๋ฐฐ์ก ์ค'
end) as ๋ฐฐ์ก์ํ,
(case
when
o.status = 'Y' and datediff(o.updated_at, current_timestamp) < 1
then concat('์ด์ ', space(1), date_format(o.updated_at, '%m/%d/%W'), space(1), '๋์ฐฉ')
when
o.status = 'Y' and datediff(o.updated_at, current_timestamp) > 1
then concat(date_format(o.updated_at, '%m/%d/%W'), '๋์ฐฉ')
when o.status = 'N' and date_format(o.created_at, '%h') < 12
then '์ค๋ ์คํ ๋์ฐฉ ๋ณด์ฅ'
else concat(date_format(DATE_ADD(o.created_at, INTERVAL 1 DAY), '%m/%d/%W'), '๋์ฐฉ๋ณด์ฅ')
end) as ๋ฐฐ์ก์ํ
from order_ o
inner join shipments_mapping sm on o.prod_id = sm.prod_id
inner join shipments s on sm.shipment_id = s.id
inner join product p on o.prod_id = p.id
inner join option_mapping om on p.id = om.prod_id
inner join (select min(id),'option',id from option_) o2 on om.option_id = o2.id
inner join user on o.buyer_id = user.id
left join (select min(id),address,user_id from address) a on user.id = a.user_id
inner join order_payment op on op.order_id = o.id
inner join payments_list pl on pl.id = op.payment_id
where o.id = 5;
-์ต๊ทผ ๋ณธ ์ํ
use kiinDB;
select u.id,
p.prod_img,
p.name,
p.price AS ์๋๊ฐ๊ฒฉ,
round((p.price - p.price*(discount/100)),0) AS ํ ์ธ๊ฐ,
(case when p.status='Y' then '๊ตฌ๋งค๊ฐ๋ฅ'
when p.status='N' then 'ํ์ ' end) AS ํ์ ์ ๋ฌด,
shipment AS ๋ฐฐ์ก,
avgscore AS ํ๊ท ๋ณ์ ,
reviewCount AS ๋ฆฌ๋ทฐ๊ฐ์
from user u
inner join recently_viewed rv on u.id = rv.user_id
inner join product p on rv.prod_id = p.id
inner join shipments_mapping sm on p.id = sm.prod_id
inner join shipments s on sm.shipment_id = s.id
inner join (select prod_id, count(r.id) AS reviewCount, avg(score) AS avgscore
from review r
inner join score s2 on r.id = s2.review_id
group by prod_id) r on p.id = r.prod_id
left join (select discount,prod_id from discount_prod) dp on p.id = dp.prod_id
where u.id = 1
group by p.id
limit 20
-์ํ ํ์ด์ง
use kiinDB;
select prod_img,price AS ์๋๊ฐ, round((p.price - p.price*(discount/100)),0) AS ํ ์ธ๊ฐ,name,brand,o.`option` AS ์ต์ ,avgScore AS ํ๊ท ๋ณ์ , ๋ฆฌ๋ทฐ์, shipment
from product p
inner join (select count(r.id) AS ๋ฆฌ๋ทฐ์, round(avg(score),0) AS avgScore,prod_id from review r inner join score s on r.id = s.review_id group by prod_id) r on r.prod_id= p.id
inner join shipments_mapping sm on p.id = sm.prod_id
inner join shipments s2 on sm.shipment_id = s2.id
inner join brand b on p.brand_id = b.id
inner join option_mapping om on p.id = om.prod_id
inner join (select min(id) AS id,`option` from option_) o on om.option_id = o.id
inner join discount_prod dp on p.id = dp.prod_id
where p.id = 1
