SQL --select.sql

##ORDER BY
DESC => 增
ASC  => 减


##基本查詢語法 + order by
SELECT * FROM coffees WHERE price >100 ORDER by sup_id

##where + and + or 
SELECT * FROM coffees WHERE price >= 100 and price <=150

##where in 
SELECT * FROM coffees WHERE cof_code in (1005, 1014, 1006)

##where + between -->指定range
SELECT * FROM coffees WHERE cof_code BETWEEN 1001 and 1010

##模糊比對
SELECT * FROM coffees WHERE cof_name LIKE '_咖啡'  //_代表1個字元
SELECT * FROM coffees WHERE cof_name LIKE '%咖啡' //%代表0或多個字元

##單一欄位重複資料排除 select distinct
SELECT DISTINCT cof_name from coffees

##按指定欄位排序order by
SELECT DISTINCT sup_id, price FROM coffees ORDER BY sup_id, price

##進階查詢語法-1 + LEFT JOIN(14人格心座)
select  c.caseNumber, c.caseAnimal, c.caseContent, c.caseQuestion, t.name, t.answer, t.score
from CaseQuestion as c
LEFT JOIN (SELECT * from TestDetail WHERE name = 'x-man') as t
on c.caseNumber = t.questionInfo
where caseContent = '你的他，平日的穿著打扮?' 

##進階查詢語法-2 + LEFT JOIN(14人格心座)
SELECT * from KnowMeQuestion as k
LEFT JOIN (select * from  MyDetail as mm where mm.historyNumber = 35 ) as m
on k.number = m.questionNumber



##進階查詢語法 + Count
SELECT count(a.name), 
(SELECT c.caseContent from CaseQuestion as c WHERE c.caseNumber = a.questionInfo) as content,
(SELECT b.caseQuestion from CaseQuestion as b where b.caseNumber = a.questionInfo) as question,
a.answer, a.score
from TestDetail as a
WHERE a.name = '黃aa' and content = '你的他，平日的穿著打扮?' and a.answer = 5


##查詢不重複的資料
SELECT DISTINCT SecondType from SituationQue 
WHERE mainType = "生活"