[SQL]数据分析SQL笔试题

SQL笔试题

1、请简单写出left join和join的用法区别（可举例说明）；

join用于把来自两个或多个表的行结合起来，基于这些表之间的共同字段；

left join 关键字从左表（table1）返回所有的行，即使右表（table2）中没有匹配。如果右表中没有匹配，则结果为NULL。

如下使用例子来说明：

2、求出订单表（order_table表）中每个客户（custid）的最近一次购买日期（要求：按custid降序排列，transdate显示年月日格式）；

本题使用的方法是先使用窗口函数，筛选出最近的客户日期，再过滤出排名为1的即可

SELECT
    custid,
    transdate 
FROM
    (
SELECT
    *,
    date_format( order_date, '%Y-%m-%d' ) AS transdate,
    dense_rank ( ) over ( PARTITION BY custid ORDER BY date_format( order_date, '%Y-%m-%d' ) DESC ) AS 'rank'
FROM
    order_table 
ORDER BY
    custid,
    transdate DESC
    ) tmp 
WHERE
    tmp.rank = 1

3、用一条SQL语句查出订单表（product表）购买的每类产品付款都大于60元的客户姓名；

此题的考察点有where 和 group by：如下的SQL语句可以查询出每一类产品对应付款都大于60元的客户姓名：

SELECT product as 产品,name as 客户 FROM order_product 
WHERE payment > 60
GROUP BY product,name
ORDER BY product;　

4、请问‘SELECT 1/3’运行后能否得到结果0.33，如不能请给出正确的代码；

得到的结果是0.3333，正确的代码应该是

1	`SELECT` `ROUND(1/3,2)`

5、根据如下订单表，请使用sql语法，按购买时间统计出每天的新客人数和老客人数；

名词定义如下：

新客：当天有购买，且之前没有购买的人为新客，新客人数+1；

老客：当天有购买，且之前也有购买的人为老客，老客人数+1；

此题的考察点有窗口函数的使用以及case when的语法：

SELECT
    date,
    ifnull( CASE WHEN tmp.rank = 1 THEN count( tmp.rank ) END, 0 ) AS '新客人数',
    ifnull( CASE WHEN tmp.rank <> 1 THEN count( tmp.rank ) END, 0 ) AS '旧客人数'
FROM
    (
SELECT
    *,
    dense_rank ( ) over ( PARTITION BY NAME ORDER BY order_date ) AS 'rank'
FROM
    ( SELECT *, date_format( order_date, '%Y-%m-%d' ) AS date FROM ordersss ORDER BY NAME, order_date ) t 
    ) tmp 
GROUP BY
    tmp.date,
    tmp.NAME

6、根据如下左表具有3层关系的类目表，请使用sql语法，将数据展现成右表形式：

此题考查的是表的自连接：

SELECT
    c1.CId AS '三级类目',
    c1.PCId AS '二级类目',
    c2.PCId AS '一级类目',
    c3.PCId AS '根类目'
FROM
    category c1,
    category c2,
    category c3 
WHERE
    c1.PCId = c2.CId 
    AND c2.PCId = c3.CId