1.有如下線索表tbl_clue

clue_id	city_id	price	created_at
1	10	100	2016/6/7 15:33
2	12	123.35	2016/6/7 16:32
3	10	100	2016/6/8 8:20
4	14	30.25	2016/6/7 13:00
....	...	...	...

?

請用一條SQL語句查詢出6月7日當天分城市的線索量(clue_id)、平均價格，并按照線索量降序排列。

SELECT city_id, COUNT(clue_id) AS count_clue_id, AVG(price) ASavg_priceFROM tbl_clueWHERE DATE(created_at) = '2016/6/7'GROUP BY city_idORDER BY count_clue_id DESC;

?

2.有如下的城市表 tbl_city

?

city_id	city_name
10	北京
11	上海
12	天津
...	?

?

請用一條SQL語句在實現題1的功能前提下，用城市名稱(city_name)替換城市id(city_id)。

SELECT ci.city_name, COUNT(cl.clue_id) AS count_clue_id,AVG(cl.price) AS avg_priceFROM tbl_clue cl JOIN tbl_city ci ON cl.city_id = ci.city_idWHERE DATE(cl.created_at) = '2016/6/7'GROUP BY ci.city_nameORDER BY count_clue_id DESC;

?

?

?

3. 有如下的合同表tbl_contract

id	clue_id	deal_price	created_at
1	3	100.08	2016/7/1 0:00
2	2	80.32	2016/7/1 8:23
3	4	70.11	2016/7/2 13:22
...	...	...	...

?

此表的clue_id關聯tbl_clue表的clue_id請用一條語句查詢出6月7日創建的線索在7月1日的成交合同總量。

SELECT COUNT(id) AS count_idFROM tbl_contractWHERE DATE(creat_at) = '2016/7/1'AND clue_id IN (SELECT clue_idFROM tbl_clueWHERE DATE(created_at) = '2016/6/7');

?

4.請說明hive中 sort by ，order by ，clusterby ，distribute by各代表什么意思。

?

答：SORT BY：在每個reducer中對數據進行排序，即執行一個局部排序過程；

ORDER BY：對查詢結果集執行全局排序，即所有數據都通過一個reducer進行處理；

CLUSTER BY：是DISTRIBUTE BY …SORT BY的簡化版；

DISTRIBUTE BY: 控制map的輸出在reducer中是怎么劃分的。

?

5.怎么把這樣一個表兒
?

year	month	amount
1991	1	1.1
1991	2	1.2
1991	3	1.3
1991	4	1.4
1992	1	2.1
1992	2	2.2
1992	3	2.3
1992	4	2.4

查成這樣一個結果

year	m1	m2	m3	m4
1991	1.1	1.2	1.3	1.4
1992	2.1	2.2	2.3	2.4

?

SELECT year,SUM(CASE WHEN month = 1 THENamount ELSE 0 END) AS m1,SUM(CASE WHEN month = 2 THENamount ELSE 0 END) AS m2,SUM(CASE WHEN month = 3 THENamount ELSE 0 END) AS m3,SUM(CASE WHEN month = 4 THENamount ELSE 0 END) AS m4FROM tb1GROUP BY year;

?

?

?

6.表中有A B C三列,用SQL語句實現：當A列大于B列時選擇A列否則選擇B列，當B列大于C列時選擇B列否則選擇C列。

SELECT (CASEWHEN A>B THEN AWHEN B>C THEN BELSE CEND) AS lineFROM tb2;

?

?

?

總結

以上是生活随笔為你收集整理的瓜子二手车实习笔试SQL的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。