数据库查询实例 附带表格文件

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