sql随笔-面试题（二）

作者： 董存阔 （本博客均为原创文章，转载请注明出处）

 

create table `exam` (
	`s_date` datetime ,
	`order_id` varchar (765),
	`product_id` varchar (765),
	`amt` Decimal (11),
	`id` double 
); 
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-03-01 00:00:00','1','1','100','1');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-04-02 00:00:00','1','1','100','2');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-05-03 00:00:00','2','2','100','3');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-06-04 00:00:00','2','2','100','4');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-07-05 00:00:00','1','1','100','5');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-08-06 00:00:00','3','2','100','6');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-09-07 00:00:00','2','3','100','7');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-10-08 00:00:00','1','2','100','8');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-09-09 00:00:00','1','2','100','9');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-08-11 00:00:00','1','1','100','10');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2012-07-12 00:00:00','1','1','200','11');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2011-01-01 00:00:00','1','2','100','12');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2011-01-02 00:00:00','2','1','100','13');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2011-01-23 00:00:00','2','1','600','14');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2011-08-23 00:00:00','2','2','1090','15');
insert into `exam` (`s_date`, `order_id`, `product_id`, `amt`, `id`) values('2011-07-23 00:00:00','1','1','240','16');

 下面就是sql面试题  共四道 ，第四道最难   

 

-- 1 写一条Sql语句查询出2到8的记录  mysql:limit ,sql-server:top, Oracle:rownum
SELECT * FROM exam LIMIT 6 OFFSET 2

-- 2 写一条Sql语句删除重复[除时间外的所有字段相同]的记录,保留重复记录中时间最大的记录
DELETE FROM exam WHERE s_date NOT IN (
 SELECT * FROM  ( -- 这个外层的子查询看似多于，其实必须要有，不然在mysql中会有1093错误，
 -- 为什么需要你看文档
  SELECT MAX(s_date)   FROM exam GROUP BY order_id,product_id,amt -- HAVING COUNT(*)>1 
  -- ,体会为什么会去掉
 ) t )
 
-- 3 一条Sql语句查出年份,1月,2月,3月....12月的列表 
-- 正确写法1  
SELECT YEAR(s_date) year_ ,
SUM( (CASE WHEN MONTH(s_date)=1 THEN amt ELSE 0 END ) ) AS 1月,
SUM( (CASE WHEN MONTH(s_date)=2 THEN amt ELSE 0 END ) ) AS 2月,
SUM( (CASE WHEN MONTH(s_date)=3 THEN amt ELSE 0 END ) ) AS 3月
FROM exam GROUP BY YEAR(s_date)
-- 正确写法2 
SELECT YEAR(s_date) year_ ,
(SELECT SUM(amt) FROM exam WHERE YEAR(s_date)=year_ AND MONTH(s_date)=1 )AS 1月,
(SELECT SUM(amt) FROM exam WHERE YEAR(s_date)=year_ AND MONTH(s_date)=2 )AS 2月,
(SELECT SUM(amt) FROM exam WHERE YEAR(s_date)=year_ AND MONTH(s_date)=3 )AS 3月
FROM exam GROUP BY YEAR(s_date)

-- 4 一条sql语句查询出年份,本月销量,上月销量,环比%,去年同期销量,同比%列表
SELECT
 year_ AS 年份 ,benyue  AS 本月,shangyue AS 上月,benyue/shangyue AS 环比 ,
  ( SELECT SUM(amt) FROM exam WHERE YEAR(s_date)=(year_-1) AND MONTH(s_date)=MONTH(NOW()) ) AS 去年同期销量,
 ( benyue / ( SELECT SUM(amt) FROM exam WHERE YEAR(s_date)=(year_-1) AND MONTH(s_date)=MONTH(NOW()) ) )AS 同比
FROM 
( SELECT YEAR(s_date)AS year_, -- 先列出：  年 ， 本月 ， 上月
SUM(  (CASE WHEN MONTH(s_date)=MONTH(NOW()) THEN amt ELSE 0 END )-- 在聚合函数中，
-- case会对当前分组内所有的row进行条件检索，具体下面我会给出例子演示说明
   )   AS benyue,
SUM(  (CASE WHEN MONTH(s_date)=MONTH(NOW())-1 THEN amt ELSE 0 END )-- 严格来讲
-- 不能直接减，1月-1 = 0 
   )   AS shangyue  
    -- benyue/shangyue  as 环比  我想在这里直接相除，不料mysql不允许，
	-- 那就绕个弯吧
FROM exam  GROUP BY YEAR(s_date) 
) t -- 注意驱动表都要有别名才ok

 

 

 

 第四道题中刚说到要说一下case的用法，应为本人初次在ITeye中开设博客，不知道怎么传图，搞了半天也没弄清楚（本人使用 Chrome）,所以这里就简单说说。

 

 group 中使用 case when ：如果外部包含聚合函数，那么，case就会对改组内的row进行条件过滤，当XX=XX时的所 有满足条件的都拿出来产生聚合结果，就好比将该分组内的所有row当做一个表；如果没  有使用聚合函数，那么，那么case中处理的row有且只有一条，即：处理的是本组最终结 果。

 

本博客所有言论都建立在Mysql 5.5平台，