1,查询销量最好的商品是哪一个
要求展示该商品的销售总数量,商品名称
SELECT
*
FROM
(
SELECT
SUM(quantity) s1,
productid
FROM
`order details`
GROUP BY
productid
) a,
(SELECT
MAX(s1) s1
FROM
(
SELECT
SUM(quantity) s1
FROM
`ORDER details`
GROUP BY
productid
) a
) b
WHERE a.s1=b.s1
#2 查询哪一个供应商提供的商品种类最多
SELECT
*
FROM
suppliers a,
(SELECT
a.sl, b.supplierid
FROM
(SELECT
MAX(sl) sl
FROM
(SELECT
COUNT(productID) sl, supplierID
FROM
products
GROUP BY supplierID) a) a, (SELECT
COUNT(productID) sl, supplierID
FROM
products
GROUP BY supplierID) b
WHERE
a.sl = b.sl) b
WHERE
a.SupplierID = b.supplierID
#3,查询哪一个客户购消费的金额最多
select sum(quantity*unitPrice) totalPrice,orderID from `order details` group by orderID
SELECT
*
FROM
(SELECT
MAX(totalPrice) totalPrice
FROM
(SELECT
SUM(totalPrice) totalPrice, customerID
FROM
(SELECT
a.customerID, a.orderID, b.totalPrice
FROM
orders a, (SELECT
SUM(quantity * unitPrice) totalPrice, orderID
FROM
`order details`
GROUP BY orderID) b
WHERE
a.orderid = b.orderid) a
GROUP BY customerID) a) a,
(SELECT
SUM(total) totalPrice, customerID
FROM
(SELECT
a.customerID, b.total, a.orderid
FROM
orders a, (SELECT
SUM(quantity * unitPrice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) a
GROUP BY customerID) b
WHERE
a.totalprice = b.totalPrice
#4查询每一个商品的销售总金额
SELECT
*
FROM
products a,
(SELECT
SUM(quantity * unitPrice) total, productid
FROM
`order details`
GROUP BY productid) b
WHERE
a.productid = b.productid
#5统计每笔订单的总额,并且展示出是每笔订单哪一个客户买的
SELECT
*
FROM
customers a,
(SELECT
a.customerid, b.total, b.orderid
FROM
orders a, (SELECT
SUM(quantity * unitPrice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) b
WHERE
a.customerid = b.customerid
#6查询没有下过订单的用户信息
SELECT
*
FROM
(SELECT
a.customerid, a.companyname, b.customerid id
FROM
customers a
LEFT JOIN (SELECT DISTINCT
(customerid)
FROM
orders) b ON a.customerid = b.customerid) a
WHERE
a.id IS NULL
#7统计哪一个员工销售的金额最多
SELECT
*
FROM
employees a,
(SELECT
a.total, b.employeeid
FROM
(SELECT
MAX(total) total
FROM
(SELECT
SUM(total) total, employeeid
FROM
(SELECT
a.orderid, b.total, a.employeeid
FROM
orders a, (SELECT
SUM(quantity * unitPrice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) a
GROUP BY employeeid) a) a, (SELECT
SUM(total) total, employeeid
FROM
(SELECT
a.orderid, b.total, a.employeeid
FROM
orders a, (SELECT
SUM(quantity * unitPrice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) a
GROUP BY employeeid) b
WHERE
a.total = b.total) b
WHERE
a.employeeid = b.employeeid
#8统计哪一天销售额最多
SELECT
SUM(total)
FROM
(SELECT
a.orderid, b.total, a.orderdate
FROM
orders a, (SELECT
SUM(quantity * unitprice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) a
GROUP BY orderdate
#9查询名字中带有A的用户所下过的所有订单以及该订单的总金额
SELECT
*
FROM
(SELECT
a.customerid, a.orderid
FROM
orders a, (SELECT
customerid
FROM
customers
WHERE
customerid LIKE '%A%') b
WHERE
a.customerid = b.customerid) a,
(SELECT
SUM(quantity * unitPrice), orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid
#10统计哪一个商品没有售出过
SELECT
*
FROM
(SELECT
a.productid, a.productname, b.productid id
FROM
products a
LEFT JOIN (SELECT DISTINCT
(productid)
FROM
`order details`) b ON a.productid = b.productid) a
WHERE
a.id IS NULL
要求展示该商品的销售总数量,商品名称
SELECT
*
FROM
(
SELECT
SUM(quantity) s1,
productid
FROM
`order details`
GROUP BY
productid
) a,
(SELECT
MAX(s1) s1
FROM
(
SELECT
SUM(quantity) s1
FROM
`ORDER details`
GROUP BY
productid
) a
) b
WHERE a.s1=b.s1
#2 查询哪一个供应商提供的商品种类最多
SELECT
*
FROM
suppliers a,
(SELECT
a.sl, b.supplierid
FROM
(SELECT
MAX(sl) sl
FROM
(SELECT
COUNT(productID) sl, supplierID
FROM
products
GROUP BY supplierID) a) a, (SELECT
COUNT(productID) sl, supplierID
FROM
products
GROUP BY supplierID) b
WHERE
a.sl = b.sl) b
WHERE
a.SupplierID = b.supplierID
#3,查询哪一个客户购消费的金额最多
select sum(quantity*unitPrice) totalPrice,orderID from `order details` group by orderID
SELECT
*
FROM
(SELECT
MAX(totalPrice) totalPrice
FROM
(SELECT
SUM(totalPrice) totalPrice, customerID
FROM
(SELECT
a.customerID, a.orderID, b.totalPrice
FROM
orders a, (SELECT
SUM(quantity * unitPrice) totalPrice, orderID
FROM
`order details`
GROUP BY orderID) b
WHERE
a.orderid = b.orderid) a
GROUP BY customerID) a) a,
(SELECT
SUM(total) totalPrice, customerID
FROM
(SELECT
a.customerID, b.total, a.orderid
FROM
orders a, (SELECT
SUM(quantity * unitPrice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) a
GROUP BY customerID) b
WHERE
a.totalprice = b.totalPrice
#4查询每一个商品的销售总金额
SELECT
*
FROM
products a,
(SELECT
SUM(quantity * unitPrice) total, productid
FROM
`order details`
GROUP BY productid) b
WHERE
a.productid = b.productid
#5统计每笔订单的总额,并且展示出是每笔订单哪一个客户买的
SELECT
*
FROM
customers a,
(SELECT
a.customerid, b.total, b.orderid
FROM
orders a, (SELECT
SUM(quantity * unitPrice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) b
WHERE
a.customerid = b.customerid
#6查询没有下过订单的用户信息
SELECT
*
FROM
(SELECT
a.customerid, a.companyname, b.customerid id
FROM
customers a
LEFT JOIN (SELECT DISTINCT
(customerid)
FROM
orders) b ON a.customerid = b.customerid) a
WHERE
a.id IS NULL
#7统计哪一个员工销售的金额最多
SELECT
*
FROM
employees a,
(SELECT
a.total, b.employeeid
FROM
(SELECT
MAX(total) total
FROM
(SELECT
SUM(total) total, employeeid
FROM
(SELECT
a.orderid, b.total, a.employeeid
FROM
orders a, (SELECT
SUM(quantity * unitPrice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) a
GROUP BY employeeid) a) a, (SELECT
SUM(total) total, employeeid
FROM
(SELECT
a.orderid, b.total, a.employeeid
FROM
orders a, (SELECT
SUM(quantity * unitPrice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) a
GROUP BY employeeid) b
WHERE
a.total = b.total) b
WHERE
a.employeeid = b.employeeid
#8统计哪一天销售额最多
SELECT
SUM(total)
FROM
(SELECT
a.orderid, b.total, a.orderdate
FROM
orders a, (SELECT
SUM(quantity * unitprice) total, orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid) a
GROUP BY orderdate
#9查询名字中带有A的用户所下过的所有订单以及该订单的总金额
SELECT
*
FROM
(SELECT
a.customerid, a.orderid
FROM
orders a, (SELECT
customerid
FROM
customers
WHERE
customerid LIKE '%A%') b
WHERE
a.customerid = b.customerid) a,
(SELECT
SUM(quantity * unitPrice), orderid
FROM
`order details`
GROUP BY orderid) b
WHERE
a.orderid = b.orderid
#10统计哪一个商品没有售出过
SELECT
*
FROM
(SELECT
a.productid, a.productname, b.productid id
FROM
products a
LEFT JOIN (SELECT DISTINCT
(productid)
FROM
`order details`) b ON a.productid = b.productid) a
WHERE
a.id IS NULL