2021-11-06 python作业 mysql数据库

#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