10 手寫HQL 第10題

1.用一條SQL語句查詢出每門課都大于80分的學生姓名

name kecheng fenshu

張三語文81

張三數學75

李四語文76

李四數學90

王五語文81

王五數學100

王五英語90

A: select distinct name from table where name not in (select distinct name from table where fenshu<=80)

B：select name from table group by name having min(fenshu)>80

2.學生表 如下:
自動編號學號姓名 課程編號 課程名稱 分數
12005001張三0001數學 69
22005002李四0001數學 89
32005001張三 0001數學 69
刪除除了自動編號不同,其他都相同的學生冗余信息

A: delete tablename where自動編號not in(select min(自動編號) from tablename group by學號,姓名,課程編號,課程名稱,分數)

3.一個叫team的表，里面只有一個字段name,一共有4條紀錄，分別是a,b,c,d,對應四個球隊，現在四個球隊進行比賽，用一條sql語句顯示所有可能的比賽組合.

答：select a.name, b.name
from team a, team b
where a.name < b.name

4.面試題：怎么把這樣一個
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 m1m2m3m4
1991 1.1 1.2 1.3 1.4
1992 2.1 2.2 2.3 2.4

答案
select year,
(select amount fromaaa m where month=1and m.year=aaa.year) as m1,
(select amount fromaaa m where month=2and m.year=aaa.year) as m2,
(select amount fromaaa m where month=3and m.year=aaa.year) as m3,
(select amount from aaa m where month=4and m.year=aaa.year) as m4
from aaagroup by year

*********************************************************************
5.說明：復制表(只復制結構,源表名：a新表名：b)

SQL: select * into b from a where 1<>1 (where1=1，拷貝表結構和數據內容)
ORACLE:create table b

As

Select * from a where 1=2

[<>（不等于）(SQL Server Compact)

比較兩個表達式。 當使用此運算符比較非空表達式時，如果左操作數不等于右操作數，則結果為 TRUE。 否則，結果為 FALSE。]

6.

原表:
courseid coursename score
-------------------------------------
1 java 70
2 oracle 90
3 xml 40
4 jsp 30
5 servlet 80
-------------------------------------
為了便于閱讀,查詢此表后的結果顯式如下(及格分數為60):
courseid coursename score mark
---------------------------------------------------
1 java 70 pass
2 oracle 90 pass
3 xml 40 fail
4 jsp 30 fail
5 servlet 80 pass
---------------------------------------------------
寫出此查詢語句
select courseid, coursename ,score ,if(score>=60, "pass","fail") as markfrom course

7.表名：購物信息

購物人商品名稱 數量

A 甲 2

B 乙 4

C 丙 1

A 丁 2

B 丙 5

……

給出所有購入商品為兩種或兩種以上的購物人記錄

答：select * from 購物信息 where 購物人 in (select 購物人 from 購物信息 group by 購物人 having count(*) >= 2);

8.

info 表

date result

2005-05-09 win

2005-05-09 lose

2005-05-09 lose

2005-05-09 lose

2005-05-10 win

2005-05-10 lose

2005-05-10 lose

如果要生成下列結果, 該如何寫sql語句?

　　 win lose

2005-05-09 2 2

2005-05-10 1 2

答案：

(1) select date, sum(case when result = "win" then 1 else 0 end) as "win", sum(case when result = "lose" then 1 else 0 end) as "lose" from info group by date;

(2) select a.date, a.result as win, b.result as lose

　　from

　　(select date, count(result) as result from info where result = "win" group by date) as a

　　join

　　(select date, count(result) as result from info where result = "lose" group by date) as b

on a.date = b.date;

11 手寫HQL 第11題

有一個訂單表order。已知字段有：order_id(訂單ID), user_id(用戶ID),amount(金額), pay_datetime(付費時間),channel_id(渠道ID),dt(分區字段)。

1. 在Hive中創建這個表。

2. 查詢dt=‘2018-09-01‘里每個渠道的訂單數，下單人數（去重），總金額。

3. 查詢dt=‘2018-09-01‘里每個渠道的金額最大3筆訂單。

4. 有一天發現訂單數據重復，請分析原因

create external table order(
    order_id int,
    user_id int,
    amount double,
    pay_datatime timestamp,
    channel_id int
)partitioned by(dt string)
row format delimited fields terminated by '	';

select
    count(order_id),
    count(distinct(user_id))
    sum(amount)
from
    order
where dt="2019-09-01"

select
    order_id
    channel_id
    channel_id_amount
from(
    select
        order_id
        channel_id,
        amount,
        max(amount) over(partition by channel_id)
        min(amount) over(partition by channel_id)
        row_number()
        over(
            partition by channel_id
            order by amount desc
        )rank
    from
        order
    where dt="2019-09-01"
)t
where t.rank<4

訂單屬于業務數據，在關系型數據庫中不會存在數據重復

hive建表時也不會導致數據重復，

我推測是在數據遷移時，遷移失敗導致重復遷移數據冗余了

t_order訂單表

order_id,//訂單id

item_id, //商品id

create_time,//下單時間

amount//下單金額

t_item商品表

item_id,//商品id

item_name,//商品名稱

category//品類

t_item商品表

item_id,//商品id

item_name,//名稱

category_1,//一級品類

category_2,//二級品類

1. 最近一個月，銷售數量最多的10個商品

select
    item_id,
    count(order_id)a
from 
    t_order
where
    dataediff(create_time,current_date)<=30
group by 
    item_id
order by a desc;

2. 最近一個月，每個種類里銷售數量最多的10個商品

#一個訂單對應一個商品 一個商品對應一個品類

with(
    select
        order_id,
        item_id,
        item_name,
        category
    from
         t_order
    join
        t_item
    on
        t_order.item_id = t_item.item_id
) t
select
    order_id,
    item_id,
    item_name,
    category,
    count(item_id)over(
        partition by category
    )item_count
from
     t
group by category
order by item_count desc
limit 10;

計算平臺的每一個用戶發過多少日記、獲得多少點贊數

with t3 as(
    select * from 
    t1 left join t2 
    on t1.log_id = t2.log_id
)
select
    uid,//用戶Id
    count(log_id)over(partition by uid)log_cnt,//
    count(like_uid)over(partition by log_id)liked_cnt//獲得多少點贊數
from
    t3

處理產品版本號

1、需求A:找出T1表中最大的版本號

思路：列轉行 切割版本號 一列變三列

主版本號 子版本號 階段版本號

with t2 as(//轉換
    select
        v_id v1,//版本號
        v_id v2 //主
    from
        t1
    lateral view explode(v2) tmp as v2
)
select //第一層 找出第一個
    v1,
    max(v2)
from 
    t2

1、需求A:找出T1表中最大的版本號

select
    v_id,//版本號
    max(split(v_id,".")[0]) v1,//主版本不會為空
    max(if(split(v_id,".")[1]="",0,split(v_id,".")[1]))v2,//取出子版本并判斷是否為空，并給默認值
    max(if(split(v_id,".")[2]="",0,split(v_id,".")[2]))v3//取出階段版本并判斷是否為空，并給默認值
from
    t1

2、需求B：計算出如下格式的所有版本號排序，要求對于相同的版本號，順序號并列：

select
    v_id,
    rank() over(partition by v_id order by v_id)seq
from
    t1

總結

以上是生活随笔為你收集整理的MYSQL/HIVESQL笔试题（五）：HIVESQL（五）的全部內容，希望文章能夠幫你解決所遇到的問題。

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