MySQL 练习(订单表数据库)

Posted on 2021-05-04 Edited on 2021-06-23 In DB.MySQL Views:

订单表数据库查询练习

SQL 查询练习

订单表

这是一个自行车制造和销售公司的数据库，该公司建立自己的销售网站，提供在线销售。由于adventureworks数据库所含表的类型过多，这里只介绍题目中涉及到了表及字段。

关于该数据库可以参考：http://www.uml.org.cn/sjjm/201905103.asp

product(ProductID,Name,ProductModelID,ListPrice) person(FirstName,MiddleName,LastName) salesorderdetail(SalesOrderID,SalesOrderDetailID,OrderQty,ProductID,UnitPrice,UnitPriceDiscount,LineTotal)

product:产品ID,产品名字，产品类型ID(不同的产品ID可能具有相同的产品类型ID),产品标价 person:姓名 salesorderdetail:这笔订单的ID(不是主键，可能有ID相同的记录)，订单详细ID,订单涉及的产品数量，产品ID，单元价格，单元价格折扣，销售总额

注意：每个产品的没有打折之前的销售总额=OrderQty x UnitPrice,打折之后的销售总额=OrderQty x UnitPrice x UnitPriceDiscount

其他参考：数据库说明

salesorderheader(SalesOrderID, RevisionNumber, OrderDate, DueDate, ShipDate, Status, OnlineOrderFlag, SalesOrderNumber, PurchaseOrderNumber, AccountNumber, CustomerID, SalesPersonID, TerritoryID, BillToAddressID, ShipToAddressID, ShipMethodID, CreditCardID, CreditCardApprovalCode, CurrencyRateID, SubTotal, TaxAmt, Freight, TotalDue, Comment, rowguid, ModifiedDate)

address(AddressID, AddressLine1, AddressLine2, City, StateProvinceID, PostalCode, SpatialLocation, rowguid, ModifiedDate)

businessentityaddress(BusinessEntityID, AddressID, AddressTypeID, rowguid, ModifiedDate)

在Product表中查出所有产品信息，按产品名升序排列
输出格式：Name, ProductNumber, ListPrice

1
2
3

select Name, ProductNumber, ListPrice
from product
order by Name asc

查出每种产品类型均价等于该类产品最高标价的产品类型号
涉及表：Product
输出格式为(ProductModelID)

select ProductModelID
from product
group by ProductModelID
having avg(ListPrice) = max(ListPrice)

查出每个销售订单的销售总额
涉及表：SalesOrderDetail
输出格式为(SalesOrderID,total[=sum(LineTotal)])

1
2
3

select SalesOrderID, sum(LineTotal) total
from salesorderdetail
group by SalesOrderID

查出标价大于1000的产品，求出这些产品的均价(按ProductModelID分组)
涉及表：Product
输出格式为(ProductModelID,avg_price[avg(ListPrice)])

select ProductModelID, avg(ListPrice)
from product
where ListPrice > 1000
group by ProductModelID

查出总销量大于5的产品ID，并按总销量排序
涉及表：salesorderdetail
输出格式为(ProductID,count[=sum(OrderQty])

select ProductID, sum(OrderQty) count
from salesorderdetail
group by ProductID
having sum(OrderQty) > 5
order by sum(OrderQty) desc

查出所有商品的没有打折之前的销售额NonDiscountSales，打折之后的销售额Discounts，按ProductName逆序返回
输出格式为(ProductName,NonDiscountSales,Discounts)

1
2
3

select Name, OrderQty*UnitPrice NonDiscountSales, OrderQty*UnitPrice*UnitPriceDiscount Discounts
from salesorderdetail s inner join product p on s.ProductID = p.ProductID
order by p.Name desc

查询所有名字以“Chain”开头的ProductID,Name

1
2
3

select ProductID, Name
from product
where Name like 'Chain%'

从Person表中选出MiddleName中包含E或B字符的the business entity ID number, first name, middle name, 和 last name

1
2
3

select BusinessEntityID, FirstName, MiddleName, LastName
from person
where MiddleName regexp '.*[ebEB].*'

定义每个员工参与的所有订单的总销售额(sum(LineTotal))作为员工的业绩
列出每个城市的业绩排名前三的员工
涉及表(salesorderheader,salesorderdetail,address,businessentityaddress)
输出格式为(city,SalesPersonID)

with TMP as(
    select SalesPersonID, sum(LineTotal) totalMoney, ad.City
    from salesorderheader sh
    left join salesorderdetail sd
    on sd.SalesOrderID = sh.SalesOrderID and not isNULL(SalesPersonID)
    left join businessentityaddress bea
    on sh.SalesPersonID = bea.BusinessEntityID
    left join address ad
    on bea.AddressID = ad.AddressID
    group by SalesPersonID, ad.City
)

select City, SalesPersonID
from TMP
where (
    select count(*) from TMP t1
    where t1.City = TMP.City and t1.totalMoney > TMP.totalMoney
) <= 2
and not isNull(City)
and not isNull(SalesPersonID)
order by City asc

找出交易过四次以上（不包括四次）的顾客，输出这些顾客对应的订单信息
涉及表(SalesOrderHeader)
输出格式为(CustomerID,SalesOrderID,OrderDate)

with TMP as (
    select CustomerID from salesorderheader
    group by CustomerID
    having count(*) > 4
)

select s.CustomerID CustomerID, s.SalesOrderID SalesOrderID, s.OrderDate OrderDate
from TMP inner join salesorderheader s on TMP.CustomerID = s.CustomerID