[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，正确的代码应该是

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

　　结果如下：