金三银四面试准备SQL题(附答案)

AC编程 08-16 363

前言：

此刻大家对“mysql数据库行转列”都比较讲究，同学们都想要了解一些“mysql数据库行转列”的相关资讯。那么小编也在网上搜集了一些有关“mysql数据库行转列””的相关内容，希望我们能喜欢，我们一起来了解一下吧！

第一节前言

本文总结了我们在开发中比较常用的一些查询SQL，有简单的分组统计、开发中常用的删除重复数据、实用的行转列且字段合并等等。这些查询SQL语句非常适合准备换工作的同学用来练习巩固自己的SQL知识。当然，这些查询SQL语句也是我们开发中经常会用到的，建议收藏方便查阅。

第二节推荐SQL在线编辑工具

如果只是练习SQL，不需要在本地搭建环境安装数据库，SQL Fiddle提供了在线编辑SQL功能，如下图：

SQL Fiddle

左边窗口写DDL and DML statements，右边窗口写select statements。

第三节 SQL题集

说明：以下SQL主要针对MySQL数据库。

题目1

NO1.1 问题描述

用一条SQL语句查出商品A、B、C目前还剩多少？

NO1.2 数据准备

CREATE TABLE t_book_store (    vc_name varchar(20),    n_num int);INSERT INTO t_book_store (vc_name, n_num)VALUES ('A', '100');INSERT INTO t_book_store (vc_name, n_num)VALUES ('B', '120');INSERT INTO t_book_store (vc_name, n_num)VALUES ('C', '50');CREATE TABLE t_book_sell (    vc_name varchar(20),    n_sellnum int);INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('A', '10');INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('A', '20');INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('B', '10');INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('B', '20');INSERT INTO t_book_sell (vc_name, n_sellnum)VALUES ('B', '30');

NO1.3 参考答案

SELECT     t.vc_name,    t.n_num,    COALESCE(t2.sum_sell_num, 0) sum_sell_num,    (t.n_num - COALESCE(t2.sum_sell_num, 0)) AS numFROM    t_book_store t        LEFT JOIN    (SELECT         vc_name, SUM(n_sellnum) sum_sell_num    FROM        t_book_sell    GROUP BY vc_name) t2 ON t.vc_name = t2.vc_name

NO1.4 补充说明

在本题案例中，产品C是没有销售记录的，所以我们用库存表去左关联销量表时，需要处理C产品销量为null的情况。这里我们用到了 COALESE（field,0）函数，这个函数会在fiele 为null时将值修改为后面指定的值0，当然还有 ifnull 和 is_null 函数有类似功能。

题目2

NO2.1 问题描述

查询有多个员工的工资不低于2000的部门编号(一个部门的员工大于2000的人数有两个或两个以上就查询出来)

NO2.2 数据准备

CREATE TABLE t_payroll_sheet (	vc_dep_id varchar(20),	vc_name varchar(20),	n_num int);INSERT INTO t_payroll_sheetVALUES ('1', 'aa', 2000);INSERT INTO t_payroll_sheetVALUES ('2', 'bb', 1200);INSERT INTO t_payroll_sheetVALUES ('1', 'cc', 2100);INSERT INTO t_payroll_sheetVALUES ('2', 'dd', 1800);INSERT INTO t_payroll_sheetVALUES ('1', 'ee', 2100);INSERT INTO t_payroll_sheetVALUES ('3', 'ff', 8000);INSERT INTO t_payroll_sheetVALUES ('2', 'gg', 2200);INSERT INTO t_payroll_sheetVALUES ('3', 'hh', 4500);

NO2.3 参考答案

SELECT     vc_dep_id, COUNT(1) AS count_numFROM    t_payroll_sheetWHERE    n_num > 2000GROUP BY vc_dep_idHAVING count_num > 1

NO2.4 补充说明

where 针对每一条记录筛选，而 having 对分组后的结果集进行筛选。

问题3

NO3.1 问题描述

删除重复数据（id没有重复）

NO3.2 数据准备

CREATE TABLE t_records (	vc_id int,	vc_name varchar(20),	vc_age varchar(20));INSERT INTO t_recordsVALUES (1, 'a', '20');INSERT INTO t_recordsVALUES (2, 'a', '20');INSERT INTO t_recordsVALUES (3, 'b', '25');INSERT INTO t_recordsVALUES (4, 'c', '27');INSERT INTO t_recordsVALUES (5, 'd', '24');INSERT INTO t_recordsVALUES (6, 'd', '24');INSERT INTO t_recordsVALUES (7, 'd', '24');INSERT INTO t_recordsVALUES (8, 'e', '28');

NO3.3 易错SQL

DELETE FROM t_records WHERE    vc_id NOT IN (SELECT         vc_id    FROM        t_records    GROUP BY vc_name , vc_age    HAVING MAX(vc_id));

提示错误信息

You can't specify target table 't_records' for update in FROM clause

在同一语句中，不能先select出同一表中的某些值，再update这个表。也就是说将select出的结果再通过中间表select一遍，这样就规避了错误。注意，这个问题只出现于MySQL , Oracle不会出现此问题。

NO3.4 参考答案

DELETE FROM t_records WHERE    vc_id NOT IN (SELECT         t.vc_id    FROM        (SELECT             MAX(vc_id)        FROM            t_records        GROUP BY vc_name , vc_age        HAVING MAX(vc_id)) t);

NO3.5 补充说明

如果id也重复，变通找到不同的字段，oracle中有现成的一个字段：oracle的行号 rowid，所以可以很快得到删除语句。如果其他数据库没有这种字段，则可以自己新建一个临时行号字段：rowid，此时删除也可以继续进行了。

题目4

NO4.1 问题描述

找出忘记填写性别的员工

NO.4.2 数据准备

CREATE TABLE t_emp (	vc_id varchar(20),	vc_name varchar(20));CREATE TABLE t_sex (	vc_id varchar(20),	vc_sex varchar(20));INSERT INTO t_empVALUES ('1', 'a');INSERT INTO t_empVALUES ('2', 'b');INSERT INTO t_empVALUES ('3', 'c');INSERT INTO t_empVALUES ('4', 'd');INSERT INTO t_sexVALUES ('1', '男');INSERT INTO t_sexVALUES ('4', '女');INSERT INTO t_sexVALUES ('5', '男');

NO4.3 参考答案

用not in

SELECT     *FROM    t_empWHERE    vc_id NOT IN (SELECT             vc_id        FROM            t_sex)

用not exists

SELECT     *FROM    t_emp t1WHERE    NOT EXISTS( SELECT             *        FROM            t_sex t2        WHERE            t1.vc_id = t2.vc_id)

用left join

SELECT     t1.*FROM    t_emp t1        LEFT JOIN    t_sex t2 ON t1.vc_id = t2.vc_idWHERE    t2.vc_sex IS NULL

题目5

NO5.1 问题描述

算出部门2中得到奖金最多的员工姓名及奖金

NO5.2 数据准备

CREATE TABLE t_emp (	emp_no int,	e_name VARCHAR(10),	job VARCHAR(10),	mgr int,	hiredate DATE,	sal int,	comm int,	deptno int);INSERT INTO t_empVALUES (1, '员工1', '员工', 3, NULL	, 2000, 500, 1);INSERT INTO t_empVALUES (2, '员工2', '员工', 3, NULL	, 2500, 500, 1);INSERT INTO t_empVALUES (3, '员工3', '经理', 5, NULL	, 4000, 1000, 1);INSERT INTO t_empVALUES (4, '员工4', '财务', 5, NULL	, 3500, 500, 1);INSERT INTO t_empVALUES (5, '员工5', '主任', NULL, NULL	, 5000, 2000, 1);INSERT INTO t_empVALUES (6, '员工6', '员工', 8, NULL	, 2100, 500, 2);INSERT INTO t_empVALUES (7, '员工7', '员工', 8, NULL	, 2300, 500, 2);INSERT INTO t_empVALUES (8, '员工8', '经理', 10, NULL	, 5500, 1500, 2);INSERT INTO t_empVALUES (9, '员工9', '财务', 10, NULL	, 3000, 500, 2);INSERT INTO t_empVALUES (10, '员工10', '主任', NULL, NULL	, 6000, 2500, 2);

NO5.3 易错SQL写法

SELECT     e_name, MAX(comm) max_commFROM    t_empWHERE    deptno = 2;

该SQL由直观的题目要求得到，其实这是一条有语法错误的SQL,因为用到了聚合函数max，所以需要group by。e_name是取的第一条记录的员工姓名，而不是奖金最多的员工姓名，所以是错的。

NO5.4 参考答案

SELECT     e_name, commFROM    t_empWHERE    comm = (SELECT             MAX(comm) AS max_comm        FROM            t_emp        WHERE            deptno = 2);

题目6

NO6.1 问题描述

行转列查询

NO6.2 数据准备

CREATE TABLE stu_score (	grade_id varchar(10) DEFAULT NULL,	subject_name varchar(10) DEFAULT NULL,	max_score int(10) DEFAULT NULL);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('1', '语文', 98);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('2', '数学', 95);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('2', '政治', 87);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('5', '语文', 97);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('5', '数学', 100);INSERT INTO `stu_score` (`grade_id`, `subject_name`, `max_score`)VALUES ('5', '政治', 92);

NO6.3 参考答案

SELECT CASE grade_id		WHEN 1 THEN '一年级'		WHEN 2 THEN '二年级'		WHEN 5 THEN '五年级'	END AS '年级', MAX(IFNULL(CASE subject_name		WHEN '语文' THEN max_score	END, 0)) AS '语文'	, MAX(IFNULL(CASE subject_name		WHEN '数学' THEN max_score	END, 0)) AS '数学'	, MAX(IFNULL(CASE subject_name		WHEN '政治' THEN max_score	END, 0)) AS '政治'FROM stu_scoreGROUP BY grade_id;

行转列-结果

题目7

NO7.1 问题描述

行转列字段合并

NO7.2 数据准备

CREATE TABLE t_class (    n_class varchar(20),    n_time varchar(20),    n_stu_name varchar(20));INSERT INTO t_class (n_class, n_time,n_stu_name)VALUES ('101', '2019-01-01 12:12:12','张三');INSERT INTO t_class (n_class, n_time,n_stu_name)VALUES ('101', '2019-01-01 11:12:12','李四');INSERT INTO t_class (n_class, n_time,n_stu_name)VALUES ('101', '2019-01-01 14:12:12','王五');INSERT INTO t_class (n_class, n_time,n_stu_name)VALUES ('104', '2019-01-01 15:12:12','李白');

NO7.3 参考答案

SELECT     n_class, GROUP_CONCAT(n_stu_name) AS stuNameFROM    t_classGROUP BY n_class;

行转列字段合并

题目8

NO8.1 问题描述

max函数去重，取一条记录，只有ID不同。

NO8.2 数据准备

CREATE TABLE t_records (    vc_id int,    vc_name varchar(20),    vc_age varchar(20));INSERT INTO t_recordsVALUES (1, 'a', '20');INSERT INTO t_recordsVALUES (2, 'a', '20');INSERT INTO t_recordsVALUES (3, 'b', '25');INSERT INTO t_recordsVALUES (4, 'c', '27');INSERT INTO t_recordsVALUES (5, 'd', '24');INSERT INTO t_recordsVALUES (6, 'd', '24');INSERT INTO t_recordsVALUES (7, 'd', '24');INSERT INTO t_recordsVALUES (8, 'e', '28');

NO8.3 参考答案

SELECT     MAX(vc_id) vc_id, vc_name, vc_ageFROM    t_recordsGROUP BY vc_name , vc_age;

max函数去重

题目9

NO9.1 问题描述

max函数去重，取一条记录。

NO9.2 数据准备

CREATE TABLE t_user_bank (    vc_id int,    vc_user_id varchar(20),    vc_name varchar(20),    vc_bank varchar(20));INSERT INTO t_user_bankVALUES (1, 'A001','AC', '浦发银行');INSERT INTO t_user_bankVALUES (2, 'A001','AC', '农业银行');INSERT INTO t_user_bankVALUES (3, 'A002','Alan Chen', '邮政');INSERT INTO t_user_bankVALUES (4, 'A002','Alan Chen', '招商银行');INSERT INTO t_user_bankVALUES (5, 'A003','Kyra', '建设银行');

NO9.3 参考答案

一个用户有多个银行账号，但我们只想取用户的一条银行账号。

SELECT     MAX(vc_id) vc_id,    vc_user_id,    MAX(vc_name) vc_name,    MAX(vc_bank) vc_bankFROM    t_user_bankGROUP BY vc_user_id;

max函数去重，取一条记录

题目10

NO10.1 问题描述

统计历史累计数据，每天的数据需要进行累加。例如：

5⽉1号 = 5⽉1号的数据

5⽉2号 = 5⽉1号+5⽉2号

5⽉3号 =5⽉1号+5⽉2号+5⽉3号

…

如果当天没有新增数据则跳过统计

NO10.2 数据准备

CREATE TABLE t_login_log (    id INT,	user_id INT,    login_time DATETIME);INSERT INTO t_login_logVALUES (1, '1001','2023-01-01 12:12:23');INSERT INTO t_login_logVALUES (2, '1001','2023-01-01 14:12:23');INSERT INTO t_login_logVALUES (3, '1002','2023-01-01 10:12:23');INSERT INTO t_login_logVALUES (4, '1001','2023-01-02 11:12:23');INSERT INTO t_login_logVALUES (5, '1002','2023-01-02 09:12:23');INSERT INTO t_login_logVALUES (6, '1001','2023-01-04 15:12:23');INSERT INTO t_login_logVALUES (7, '1002','2023-01-04 17:12:23');

NO10.3 参考答案

SELECT     c.tart_dat AS item, COUNT(c.tart_dat) AS total_countFROM    (SELECT         l_tab.tart_dat, r_tab.login_time    FROM        (SELECT         DATE_FORMAT(t.login_time, '%Y-%m-%d') AS tart_dat    FROM        t_login_log t    GROUP BY tart_dat) l_tab    INNER JOIN (SELECT         DATE_FORMAT(login_time, '%Y-%m-%d') AS login_time    FROM        t_login_log) r_tab ON l_tab.tart_dat >= r_tab.login_time) cGROUP BY c.tart_datORDER BY c.tart_dat

数据累计

第四节系列专题系列一：case when系列专题

NO1.1 数据准备

CREATE TABLE t_user (    id INT,    ch_name VARCHAR(20),    en_name VARCHAR(20),    sex INT,    phone VARCHAR(20),    age int);INSERT INTO t_userVALUES (1, '张三', 'AC',0,'1331781016x',17);INSERT INTO t_userVALUES (2, '', 'AlanChen',1,'1341781016x',9);INSERT INTO t_userVALUES (3, '李四', 'Kyra',2,'1301781016x',28);INSERT INTO t_userVALUES (4, '王五', 'Tom',1,'1332981014x',62);

NO1.2 数据统计

SELECT     COUNT(*) '总用户数',    COUNT(CASE        WHEN sex = 0 THEN sex    END) AS '未知',    COUNT(CASE        WHEN sex = 1 THEN sex    END) AS '男',    COUNT(CASE        WHEN sex = 2 THEN sex    END) AS '女'FROM    t_user;

case when数据统计

NO1.3 数据转换

select	id,	ch_name,	en_name,	sex,	case sex		when 0 then '未知'		when 1 then '男'		when 2 then '女'	end as sex_namefrom t_user;

数据转换

NO1.4 数据转换用like

select	id,	ch_name,	en_name,    phone,	case 		when phone like '133%' then '电信'		when phone like '134%' then '移动'		when phone like '130%' then '联通'	end as phone_channelfrom t_user;

数据转换用like

NO1.4 数据转换用比较运算

select	id,	ch_name,	en_name,    age,	case 		when age<18 then '未成年人'		when age>=18 and age < 60 then '青年'        when age>=60 then '老年'		else '其他'	end as age_typefrom t_user;

数据转换用比较运算

NO1.4 选取字段

SELECT     id,    CASE        WHEN ch_name IS NOT NULL AND ch_name != '' THEN ch_name        ELSE en_name    END AS user_nameFROM    t_user;

选取字段

系列2：查询统计系列专题

NO2.1 数据准备

-- 学生表CREATE TABLE student (    sid varchar(10),    sName varchar(20),    sAge varchar(200),    sSex varchar(10) DEFAULT NULL,    PRIMARY KEY (sid));-- 课程表CREATE TABLE course (    cid varchar(10) NOT NULL,    cName varchar(10) DEFAULT NULL,    tid int(20) DEFAULT NULL,    PRIMARY KEY (cid));-- 成绩表CREATE TABLE sc (    sid varchar(10) DEFAULT NULL,    cid varchar(10) DEFAULT NULL,    score int(10) DEFAULT NULL);-- 教师表CREATE TABLE teacher (    tid int(10) DEFAULT NULL,    tName varchar(10) DEFAULT NULL);INSERT INTO teacher (tid, tName)VALUES (1, '李老师'),    (2, '何以琛'),    (3, '叶平');INSERT INTO student (sid, sName, sAge, sSex)VALUES ('1001', '张三丰', '1980-10-12 23:12:36', '男'),    ('1002', '张无极', '1995-10-12 23:12:36', '男'),    ('1003', '李奎', '1992-10-12 23:12:36', '女'),    ('1004', '李元宝', '1980-10-12 23:12:36', '女'),    ('1005', '李世明', '1981-10-12 23:12:36', '男'),    ('1006', '赵六', '1986-10-12 23:12:36', '男'),    ('1007', '田七', '1981-10-12 23:12:36', '女');INSERT INTO sc (sid, cid, score)VALUES ('1001', '001', 80),    ('1001', '002', 60),    ('1001', '003', 75),    ('1002', '001', 85),    ('1002', '002', 70),    ('1003', '004', 100),    ('1003', '001', 90),    ('1003', '002', 55),    ('1004', '002', 65),    ('1004', '003', 60);INSERT INTO course (cid, cName, did)VALUES ('001', '企业管理', 3),    ('002', '马克思', 3),    ('003', 'UML', 2),    ('004', '数据库', 1),    ('005', '英语', 1);

NO2.2 查询“001”课程比“002”课程成绩高的所有学生的学号

SELECT     t1.*, t2.*FROM    sc t1,    sc t2WHERE    t1.sid = t2.sid AND t1.cid = '001'        AND t2.cid = '002'        AND t1.score > t2.score;

NO2.3 查询平均成绩大于60分的同学的学号和平均成绩

方式一，自己算平均数

SELECT     sid, SUM(score) / COUNT(1) AS averageFROM    scGROUP BY sidHAVING average > 60;

方式二，用平均数函数

SELECT     sid, AVG(score) AS averageFROM    scGROUP BY sidHAVING average > 60;

NO2.4 查询所有同学的学号、姓名、选课数、总成绩

SELECT     t2.sid, t2.sName, t1.score_count, t1.score_sumFROM    (SELECT         sid, COUNT(1) AS score_count, SUM(score) AS score_sum    FROM        sc    GROUP BY sid) t1,    student t2WHERE    t1.sid = t2.sid;

NO2.5 查询姓“李”的老师的个数

SELECT     COUNT(1)FROM    teacherWHERE    tName LIKE '李%';

NO2.6 查询没学过“叶平”老师课的同学的学号、姓名

方式一：两表关联+ group by

select sid, sNamefrom studentwhere sid not in (	select sid	from (		select sid		from sc		where cid in (			select t1.cid			from course t1, teacher t2			where t1.tid = t2.tid				and t2.tName = '叶平'		)		group by sid	) t);

方式二：三表关联+ distinct

SELECT     t.sid, t.sNameFROM    student tWHERE    t.sid NOT IN (SELECT DISTINCT            sid        FROM            sc t1,            course t2,            teacher t3        WHERE            t1.cid = t2.cid AND t2.tid = t3.tid                AND t3.tName = '叶平')

NO2.7 查询学过“001”并且也学过编号“002”课程的同学的学号、姓名

易错SQL写法

SELECT     t1.sid, t1.sNameFROM    student t1,    (SELECT DISTINCT        sid AS sid    FROM        sc    WHERE        cid IN ('001' , '002')) t2WHERE    t1.sid = t2.sid;

in是“或”的意思，这个查的是：学过“001” 或者学过编号“002”课程的同学的学号、姓名

参考答案

SELECT     t3.sid, t3.sNameFROM    sc t1,    sc t2,    student t3WHERE    t1.sid = t2.sid AND t2.sid = t3.sid        AND t1.cid = '001'        AND t2.cid = '002'

NO2.8 查询所有课程成绩小于80分的同学的学号、姓名

SELECT     t1.sid, t1.sNameFROM    student t1,    (SELECT DISTINCT        sid AS sid    FROM        sc    WHERE        sid NOT IN (SELECT DISTINCT                sid AS sid            FROM                sc            WHERE                score >= 80)) t2WHERE    t1.sid = t2.sid;

本文地址：http://www.longkongtuishu.com/ca6c6BARsBFYCCVZR.html

标签： #mysql数据库行转列 #oraclesql试题及答案 #oracle获取行号 #oracle显示行号