CREATE DATABASE IF NOT EXISTS 0630_practice;
USE 0630_practice;
CREATE TABLE IF NOT EXISTS stuInfo(
CLASSNO VARCHAR(50),
STUDENTNO INT(11),
`NAME` VARCHAR(50),
SEX VARCHAR(50),
COURSE VARCHAR(50),
GRADE INT(11)
);
INSERT INTO stuInfo VALUES
('Class1',1001,'Jennifer','F','English',98),
('Class1',1002,'Tommy','M','English',89),
('Class1',1003,'Alex','M','English',91),
('Class2',2001,'Emar','F','English',91),
('Class2',2002,'Racher','F','English',80),
('Class2',2003,'Chandler','M','English',81),
('Class3',3001,'Roben','M','English',65),
('Class3',3002,'Hugo','M','English',66),
('Class3',3003,'Ellen','F','English',67),
('Class1',1004,'Jonny','F','French',88),
('Class1',1005,'Tedesco','M','French',88),
('Class1',1006,'Scofield','M','French',89),
('Class2',2001,'Emar','F','French',80),
('Class2',2002,'Racher','F','French',78),
('Class2',2003,'Chandler','M','French',84),
('Class3',3001,'Roben','M','French',67),
('Class3',3002,'Hugo','M','French',70),
('Class3',3003,'Ellen','F','French',71);
SELECT
ABS(
(
SELECT AVG(s.`GRADE`)
FROM stuInfo s
WHERE s.`COURSE`='English' AND s.`CLASSNO`='Class1') -
(
SELECT AVG(s.`GRADE`)
FROM stuInfo s
WHERE s.`COURSE`='English' AND s.`CLASSNO`='Class2')) 1班和2班英语平均分差值;
SELECT SUM(CASE
WHEN s.`GRADE`<a.avgg THEN 1
ELSE 0
END) 人数,s.`CLASSNO` 班级
FROM stuInfo s ,
(SELECT AVG(s.`GRADE`) avgg,s.`CLASSNO` CLASS
FROM stuInfo s
WHERE s.`COURSE`='French'
GROUP BY s.`CLASSNO`) a
WHERE s.`COURSE`='French' AND s.`CLASSNO`=a.CLASS
GROUP BY s.`CLASSNO`;
SELECT a.n 姓名,a.c 班级
FROM
(
SELECT MAX(s.`GRADE`),s.`NAME` n,s.`CLASSNO` c
FROM stuInfo s
WHERE s.`COURSE`='English'
GROUP BY s.`CLASSNO`) a;
SELECT @b:=@b+1 排名,s.`CLASSNO` 班级,s.`NAME` 姓名,s.`GRADE` 分数
FROM stuInfo s ,(SELECT @b:=0) a
WHERE s.`COURSE`='English'
ORDER BY s.`GRADE` DESC;
CREATE TABLE IF NOT EXISTS exam(
examNo INT(10) PRIMARY KEY,
`Name` VARCHAR(10),
exam1 INT(10),
exam2 INT(10)
);
INSERT INTO exam VALUES
(1,'A',35,43),
(2,'B',78,65),
(3,'C',43,48),
(4,'A',59,67),
(5,'A',84,81),
(6,'C',58,59),
(7,'B',89,60),
(8,'D',59,94),
(9,'D',65,75),
(10,'D',84,63);
SELECT d.examNo 考试编号,d.name 姓名,d.exam1 模块1考试成绩,d.exam2 模块2考试成绩, CASE WHEN TRUE THEN '考试通过' END 考试结果
FROM
(
SELECT *
FROM
(
SELECT DISTINCT a.*,a.exam1+a.exam2 su
FROM
(SELECT e.*,(CASE
WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
ELSE '未通过'
END) 考试结果
FROM exam e) a
WHERE a.考试结果='通过') b) d,
(
SELECT MAX(c.su) su,c.name
FROM
(
SELECT *
FROM
(
SELECT DISTINCT a.*,a.exam1+a.exam2 su
FROM
(SELECT e.*,(CASE
WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
ELSE '未通过'
END) 考试结果
FROM exam e) a
WHERE a.考试结果='通过') b) c
GROUP BY c.name) e
WHERE d.name=e.name AND e.su=d.su
ORDER BY d.examNo;
SELECT d.examNo 考试编号,d.name 姓名,d.exam1 模块1考试成绩,d.exam2 模块2考试成绩, CASE WHEN TRUE THEN '考试未通过' END 考试结果
FROM
(
SELECT *,a.exam1+a.exam2
FROM
(SELECT e.*,(CASE
WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
ELSE '未通过'
END) 考试结果
FROM exam e) a
WHERE a.考试结果='未通过' AND a.name NOT IN
(SELECT DISTINCT a.name
FROM
(SELECT e.*,(CASE
WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
ELSE '未通过'
END) 考试结果
FROM exam e) a
WHERE a.考试结果='通过' )
GROUP BY a.name
HAVING MAX(a.exam1+a.exam2)) d;
SELECT *
FROM
(SELECT d.examNo 考试编号,d.name 姓名,d.exam1 模块1考试成绩,d.exam2 模块2考试成绩, CASE WHEN TRUE THEN '考试通过' END 考试结果
FROM
(
SELECT *
FROM
(
SELECT DISTINCT a.*,a.exam1+a.exam2 su
FROM
(SELECT e.*,(CASE
WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
ELSE '未通过'
END) 考试结果
FROM exam e) a
WHERE a.考试结果='通过') b) d,
(
SELECT MAX(c.su) su,c.name
FROM
(
SELECT *
FROM
(
SELECT DISTINCT a.*,a.exam1+a.exam2 su
FROM
(SELECT e.*,(CASE
WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
ELSE '未通过'
END) 考试结果
FROM exam e) a
WHERE a.考试结果='通过') b) c
GROUP BY c.name) e
WHERE d.name=e.name AND e.su=d.su
ORDER BY 考试编号 ASC) a1
UNION
SELECT *
FROM
(
SELECT d.examNo 考试编号,d.name 姓名,d.exam1 模块1考试成绩,d.exam2 模块2考试成绩, CASE WHEN TRUE THEN '考试未通过' END 考试结果
FROM
(
SELECT *,a.exam1+a.exam2
FROM
(SELECT e.*,(CASE
WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
ELSE '未通过'
END) 考试结果
FROM exam e) a
WHERE a.考试结果='未通过' AND a.name NOT IN
(SELECT DISTINCT a.name
FROM
(SELECT e.*,(CASE
WHEN e.exam1>=60 AND e.exam2>=60 THEN '通过'
ELSE '未通过'
END) 考试结果
FROM exam e) a
WHERE a.考试结果='通过' )
GROUP BY a.name
HAVING MAX(a.exam1+a.exam2)) d ) a2;
CREATE TABLE IF NOT EXISTS orderList(
orderId INT(10),
orderDate DATETIME,
orderPerson VARCHAR(11),
orderAddress VARCHAR(11)
);
INSERT INTO orderList VALUES
(1001,'2020/4/1 16:58:00','A','中国'),
(1002,'2020/5/14 16:58:00','A','美国'),
(1003,'2020/5/20 16:58:00','B','英国'),
(1004,'2020/6/10 16:58:00','C','中国'),
(1005,'2020/4/23 16:58:00','D','法国'),
(1006,'2020/6/9 16:58:00','C',NULL);
CREATE TABLE IF NOT EXISTS orderProduct(
orderId INT(10),
productId INT(10),
productNum INT(10),
productPrice DOUBLE(8,2)
);
INSERT INTO orderProduct VALUES
(1001,001,5,6),
(1001,002,8,23),
(1002,003,12,17),
(1003,001,3,6),
(1003,004,4,36),
(1004,005,9,15),
(1004,002,14,23),
(1005,003,23,17),
(1006,003,14,17);
SELECT c.orderperson 姓名,SUM(c.s) 45俩月总金额
FROM
(SELECT o.`orderPerson`,o.`orderId`,b.s
FROM
(SELECT SUM(a.产品总价) s, a.orderid
FROM (SELECT *,p.`productNum`*p.`productPrice` 产品总价
FROM orderProduct p
WHERE p.`orderId` IN
(SELECT o.`orderId`
FROM orderList o
WHERE CONVERT(DATE_FORMAT(CONVERT(o.`orderDate`,DATE),'%m'),UNSIGNED INTEGER) IN (4,5))) a
GROUP BY a.orderid ) b JOIN orderList o
ON o.`orderId`=b.orderid) c
GROUP BY c.orderperson
ORDER BY 45俩月总金额 DESC;
SELECT a.时间,a.orderid,SUM(a.productNum*a.productPrice) 订单申请总金额
FROM (
SELECT o.*,p.`productNum`,p.`productPrice`,CONCAT(DATE_FORMAT(o.`orderDate`,'%Y'),'年',DATE_FORMAT(o.`orderDate`,'%m'),'月') 时间
FROM orderList o JOIN orderProduct p
ON p.`orderId`=o.`orderId`
WHERE o.`orderAddress` NOT IN ('英国','法国') OR o.`orderAddress` IS NULL) a
GROUP BY a.时间,a.orderid;
CREATE TABLE IF NOT EXISTS sale(
proId VARCHAR(10),
saleNum INT(10),
saleTime DATETIME
);
INSERT INTO sale VALUES
('C1001',15,'2020/6/1 0:00'),
('C1002',26,'2020/5/2 0:00'),
('C1003',21,'2020/4/3 0:00'),
('C1003',23,'2020/4/4 0:00'),
('C1003',0,'2020/3/5 0:00'),
('C1001',16,'2020/2/6 0:00'),
('C1002',32,'2020/1/7 0:00'),
('C1001',16,'2019/12/8 0:00'),
('C1001',32,'2019/6/9 0:00'),
('C1002',17,'2019/5/9 0:00');
CREATE TABLE IF NOT EXISTS product(
productId VARCHAR(10) PRIMARY KEY NOT NULL,
productName VARCHAR(10),
productPrice DOUBLE(8,2)
);
INSERT INTO product VALUES
('C1001','产品A',45),
('C1002','产品B',52),
('C1003','产品C',39);
SELECT 2020 年,a.月份 月,a.总销售数*p.productprice 总销售额
FROM
(
SELECT proid,SUM(salenum) 总销售数,2020 年,CONVERT(DATE_FORMAT(s.`saleTime`,'%m'),UNSIGNED INTEGER)月份
FROM sale s
WHERE DATE_FORMAT(s.`saleTime`,'%Y')=2020
GROUP BY 月份,proid
ORDER BY 月份 ) a JOIN product p
ON p.`productId`=a.proid
ORDER BY 月;
Processed:
0.040, SQL:
9
