#init
select * from *
/*
if exists (select * from sysobjects where name='g9yaoluoran')
print "exists"
else
print "not exists"
#1
CREATE TABLE g9yaoluoran(
productCode varchar(15) NOT NULL PRIMARY KEY,
TS decimal(10, 2) NOT NULL
);
INSERT INTO g9yaoluoran SELECT productCode, SUM(quantityOrdered*priceEach) as TS FROM orderdetails GROUP BY productCode ORDER BY SUM(quantityOrdered*priceEach) LIMIT 10;
#2
CREATE TABLE g9yaoluoran_atv(
productCode varchar(15) NOT NULL PRIMARY KEY,
ATV decimal(10, 2) NOT NULL
);
INSERT INTO g9yaoluoran_atv SELECT productCode, SUM(quantityOrdered*priceEach)/SUM(quantityOrdered) as ATV FROM orderdetails GROUP BY productCode ORDER BY SUM(quantityOrdered*priceEach)/SUM(quantityOrdered) LIMIT 10;
#3
CREATE TABLE g9yaoluoran_top10customer(
productCode varchar(15) NOT NULL PRIMARY KEY,
ATV decimal(10, 2) NOT NULL
);
SELECT productCode, SUM(quantityOrdered*priceEach)/SUM(quantityOrdered) as ATV FROM orderdetails GROUP BY productCode ORDER BY SUM(quantityOrdered*priceEach)/SUM(quantityOrdered) LIMIT 10
*/
#SELECT * FROM orderdetails ORDER BY quantityOrdered*priceEach LIMIT 10
#SELECT * FROM orderdetails GROUP BY productCode ORDER BY SUM(quantityOrdered*priceEach) LIMIT 10
#SELECT * FROM orderdetails ORDER BY quantityOrdered
#SELECT * FROM orderdetails
2021-11-06 python作业 mysql数据库
于 2021-11-06 11:48:54 首次发布
![](https://img-home.csdnimg.cn/images/20240711042549.png)