1、求拥有2辆及以上车的人每辆车的购车金额占个人总购车金额的比重
--- 建表
CREATE TABLE car (
userid int,
carid varchar(10),
price decimal(6, 2),
date date
);
--- 插入数据
INSERT INTO car
VALUES (1, '沪A66666', 66.66, '2020-1-1'),
(2, '沪A13256', 100.66, '2020-1-1'),
(3, '沪A95466', 20.66, '2020-1-3'),
(4, '沪A78945', 50.66, '2020-1-4'),
(1, '沪A33666', 70.66, '2020-1-4'),
(1, '沪A68886', 1006.66, '2020-1-5'),
(2, '沪A88886', 88.66, '2020-1-5'),
(4, '沪A45466', 123.66, '2020-6-6'),
(1, '沪A66886', 2066.66, '2020-6-1');
思路：按题意，需按userid利用窗口函数(利用GROUP BY分组也可以)分别求出每人所拥有的车辆数量和汽车总价值，然后利用单价/总价值得出比重，最后由于MySQL无法直接显示百分数，需要利用CONCAT函数将小数前N位和百分号进行组合'%'。
SELECT userid, carid,CONCAT(LEFT(percent*100,4),'%') AS '占比'
FROM(
SELECT
*,
COUNT(*) OVER(PARTITION BY userid) AS car_number,
SUM(price) OVER(PARTITION BY userid) AS total_price,
price/SUM(price) OVER(PARTITION BY userid) AS percent
FROM car
) AS t1
WHERE car_number >=2
得出如下结果
第一题结果
2、求年累加值，总累加值
--- 建表
CREATE TABLE temp (
DATE DATETIME,
VALUE INT
);
--- 插入数据
INSERT INTO temp
VALUES ('2018/11/23', 10),
('2018/11/25', 12),
('2018/12/31', 3),
('2019/2/9', 53),
('2019/3/31', 23),
('2019/7/8', 11),
('2019/7/31', 10);
思路：
1.先利用YEAR()和MONTH()函数求出每条记录的年和月；
2.按年和月组合条件分组求和，得出每月总和；
3.上述得出的结果作为子表，然后取出Year和Month；
4.利用窗口函数，窗口范围为从第一行到当前行(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)，进行累积求和
SELECT
Year,
Month,
SUM(Value_Month) OVER(PARTITION BY Year ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Y_SUM,
SUM(Value_Month) OVER(ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS CUM_SUM
FROM(
SELECT
*,
YEAR(DATE) AS Year,
MONTH(DATE) AS Month,
SUM(VALUE) AS Value_Month
FROM temp
GROUP BY YEAR(DATE),MONTH(DATE)
) AS t1
得出结果如下：
第二题结果
3、列转行
--- 建表
CREATE TABLE st_score (
userid varchar(20) NOT NULL COMMENT '用户ID',
subject varchar(20) COMMENT '科目',
score int(4) COMMENT '成绩'
)
--- 插入数据
INSERT INTO st_score
VALUES ('001', '语文', 90),
('001', '数学', 92),
('001', '英语', 80),
('002', '语文', 88),
('002', '数学', 90),
('002', '英语', 75),
('003', '语文', 70),
('003', '数学', 85),
('003', '英语', 90),
('003', '政治', 82);
思路：本题考查条件语句的应用：if(判断条件,条件为真,条件为假)，查找到对应科目时返回该科目分数，否则为0，由于本题不需要分组，所以仅用if即可，如果需要分组需要对每一个科目进行SUM求和，并按userid进行分组。
SELECT
userid,
IF(subject = '语文',score,0) AS '语文',
IF(subject = '数学',score,0) AS '数学',
IF(subject = '英语',score,0) AS '英语',
IF(subject = '政治',score,0) AS '政治'
FROM st_score
得出结果如下：
第三题结果
4、行转列
--- 建表
CREATE TABLE st_score1 (
userid VARCHAR ( 20 ) NOT NULL COMMENT '用户id',
cn_score DOUBLE COMMENT '语文成绩',
math_score DOUBLE COMMENT '数学成绩',
en_score DOUBLE COMMENT '英语成绩',
po_score DOUBLE COMMENT '政治成绩'
)
--- 插入数据
INSERT INTO st_score1
VALUES ('001', 90, 92, 80, 0),
('002', 88, 90, 75.5, 0),
('003', 70, 85, 90, 82);
思路:先单独取出单科的所有学生的成绩，然后使用UNION将不同科目的成绩组合起来，最后按userid进行排序，注意UNION和UNION ALL有一定区别，本题使用UNION即可
SELECT userid,'语文' AS course, cn_score AS score FROM st_score1
UNION
SELECT userid,'数学' AS course, math_score AS score FROM st_score1
UNION
SELECT userid,'英语' AS course, en_score AS score FROM st_score1
UNION
SELECT userid,'政治' AS course, po_score AS score FROM st_score1
ORDER BY userid
得出结果如下：
第四题结果
5、计算各院系的男女计数以及合计
--- 建表
CREATE TABLE st (
id VARCHAR(20),
NAME VARCHAR(20),
gender CHAR(1),
birth VARCHAR(20),
department VARCHAR(20),
address VARCHAR(20)
)
--- 插入数据
INSERT INTO st
VALUES ('201901', '张大佬', '男', '1985', '计算机系'
, '北京市海淀区'),
('201902', '郭大侠', '男', '1986', '中文系'
, '北京市昌平区'),
('201903', '张三', '女', '1990', '中文系'
, '湖南省永州市'),
('201904', '李四', '男', '1990', '英语系'
, '辽宁市阜新市'),
('201905', '王五', '女', '1991', '英语系'
, '福建省厦门市'),
('201906', '王六', '男', '1988', '计算机系'
, '湖南省衡阳市');
思路：首先按院系+性别，两个条件进行联合分组计数，作为子表，然后利用列转行的知识转化为对应的格式。
SELECT department AS '院系', SUM(IF(gender = '男',cont,0)) AS '男', SUM(IF(gender = '女',cont,0)) AS '女',SUM(cont) AS '总计'
FROM(
SELECT department,gender,count(id) AS cont
FROM st
GROUP BY department,gender
)AS t1
GROUP BY department
得出结果如下：
第五题结果
