亚信科技笔试SQL题--case语法、生成序列号

1.创建一张表Table A，将上述数据插入表中，（字段名称自定）

CREATE TABLE `a` (
  `callnumber` varchar(255) DEFAULT NULL,
  `callednumber` varchar(255) DEFAULT NULL,
  `callcount` varchar(255) DEFAULT NULL,
  `calltime` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `a` VALUES ('001', '006', '2', '2019-11-21 00:00:00');
INSERT INTO `a` VALUES ('003', '008', '4', '2019-11-25 00:00:00');
INSERT INTO `a` VALUES ('005', '007', '5', '2019-11-14 00:00:00');
INSERT INTO `a` VALUES ('006', '007', '3', '2019-10-25 00:00:00');
INSERT INTO `a` VALUES ('006', '003', '1', '2019-07-04 00:00:00');
INSERT INTO `a` VALUES ('008', '002', '1', '2019-06-15 00:00:00');
INSERT INTO `a` VALUES ('002', '008', '5', '2019-09-26 00:00:00');
INSERT INTO `a` VALUES ('002', '004', '7', '2019-02-25 00:00:00');
INSERT INTO `a` VALUES ('007', '004', '6', '2019-04-11 00:00:00');
INSERT INTO `a` VALUES ('006', '004', '2', '2019-05-16 00:00:00');
INSERT INTO `a` VALUES ('007', '005', '8', '2019-06-15 00:00:00');
INSERT INTO `a` VALUES ('003', '001', '6', '2019-10-12 00:00:00');
INSERT INTO `a` VALUES ('007', '001', '2', '2019-11-11 00:00:00');
INSERT INTO `a` VALUES ('002', '009', '3', '2019-07-30 00:00:00');
INSERT INTO `a` VALUES ('001', '008', '2', '2019-11-22 00:00:00');
INSERT INTO `a` VALUES ('003', '002', '1', '2019-12-05 00:00:00');
INSERT INTO `a` VALUES ('004', '007', '4', '2019-02-28 00:00:00');
INSERT INTO `a` VALUES ('005', '009', '6', '2019-03-31 00:00:00');

2:计算Table A中每个主叫号码总的通话次数

SELECT callnumber,sum(callcount) FROM a GROUP BY callnumber

3:计算Table A 中2019年11月共有多少次通话

SELECT '2019-11',sum(callcount)  FROM a WHERE  calltime like '2019-11%'

4:找出Table A中总通话次数最多的月份

SELECT 
SUBSTRING(calltime,1,7) callmonth ,
sum(callcount) callcount
FROM a
GROUP BY callmonth
ORDER BY callcount desc
limit 1

5:将通话次数按从小到大排列，增加序号，选出排名第九的通话次数

SELECT rowNum,callcount
from 
(
	SELECT 
	(@rowNum:=@rowNum+1) as rowNum,
	callcount
	FROM a,(SELECT @rowNum:=0) r
	ORDER BY callcount asc 
) tmp
WHERE rowNum=9

6:将通话时间按上中下旬分类，并算出上中下旬的条数

SELECT
		#day,
		case 
			WHEN 1<=day and day<=10 THEN '上旬'
			WHEN 11<=day and day<=20 THEN '中旬'
			WHEN 21<=day and day<=31 THEN '下旬'
		end 所属旬,
		sum(callcount)
	from
	(
		SELECT 
		SUBSTRING(calltime,9,2) day ,callcount
		FROM a
	) tmp
	GROUP BY 所属旬

创建B表

CREATE TABLE `b` (
  `number` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

INSERT INTO `b` VALUES ('010');
INSERT INTO `b` VALUES ('004');
INSERT INTO `b` VALUES ('005');
INSERT INTO `b` VALUES ('003');
INSERT INTO `b` VALUES ('006');
INSERT INTO `b` VALUES ('011');
INSERT INTO `b` VALUES ('024');
INSERT INTO `b` VALUES ('001');

7:统计Table B中有多少个的号码在Table A中主叫号码列中出现过

SELECT 
count(number)
FROM a
JOIN b
on a.callnumber=b.number

8:统计Table B中有多少个的号码在Table A中被叫号码列中出现过

SELECT 
count(number)
FROM a
JOIN b
on a.callednumber=b.number