โจทย์ปัญหาครับ
■ Make a query to collect all of values below :
． model_hyouji have no same result
． Model_Code
． The number of motorcyle of Model
． The avatar of Model
． Motorcycle highest price of Model (model kakaku_max) is counted based on the unit of 10,000 (end to 2 decimals). Ex : DB → 15000 show result is 1.50 . If price is null or empty, the result will be showed 0
． Motorcycle lowest price of Model (model kakaku_min) is counted based on the unit of 10,000 (end to 2 decimals). Ex : DB → 1200 show result is 0.12 . If price is null or empty, the result will be showed 0
■ Table need to query:
．mst_series.
．mst_model_series.
．mst_model_v2.
■ Query condition :
．Motorcycle brands (Maker) are: Harley Davidson (maker_code : 9) , Ducati (maker_code : 13), BMW (maker_code : 37).
．These motorcycle brands above are taken by motorcycle's Series of motorcycle brand which has code of 7
■ Sort ascending and follow:
．Order by : model.model_displacement,
model_hyouji
คิวรีที่ผมทำครับ Code (SQL)
SELECT distinct(model_hyouji) as t ,
mms.model_code,
v2.model_count,
v2.model_image_url,
round(if(model_kakaku_min is null or model_kakaku_min = '0','0', (model_kakaku_min / 10000)),2) as motocycle_kakaku_min,
round(if(model_kakaku_max is null or model_kakaku_max = '0','0', (model_kakaku_max / 10000)),2) as motocycle_kakaku_max,
model_displacement
FROM
mst_model_series mms,
mst_series ms,
mst_model_v2 v2
WHERE
v2.model_code = mms.model_code and
mms.series_code = ms.series_code and
mms.maker_code in (9,13,37) and mms.series_code = 7
order by model_displacement,t asc
select * from(
SELECT distinct(model_hyouji) as t ,
mms.model_code,
v2.model_count,
v2.model_image_url,
round(if(model_kakaku_min is null or model_kakaku_min = '0','0', (model_kakaku_min / 10000)),2) as motocycle_kakaku_min,
round(if(model_kakaku_max is null or model_kakaku_max = '0','0', (model_kakaku_max / 10000)),2) as motocycle_kakaku_max,
model_displacement
FROM
mst_model_series mms,
mst_series ms,
mst_model_v2 v2
WHERE
v2.model_code = mms.model_code and
mms.series_code = ms.series_code and
mms.maker_code in (9,13,37) and mms.series_code = 7
) as tmp
order by model_displacement,t
Load balance : Server 04
