DB2603 - 数据库的安装、创建、使用

• 顾客表 customers

• 供应商表 agents

• 商品表 products

• 订单表 orders

(1) 查询仓库位于 Dallas 且库存数量低于 200000 的商品的编号、名称和库存数量；结果按照库存数量从小到大排序。

select pid, pname, quantity
from products
where city = 'Dallas' and quantity < 200000
order by quantity;

(2) 查询满足以下条件的订单的编号和销售金额：Dallas 的顾客通过位于 Duluth 的供应商购买商品；结果按照订单编号从大到小排序。

select ordno, dollars
from orders
join customers on orders.cid = customers.cid
join agents on orders.aid = agents.aid
where customers.city = 'Dallas' and agents.city = 'Duluth'
order by ordno desc;

SELECT o.ordno, o.dollars
FROM orders o, customers c, agents a
WHERE o.cid=c.cid and o.aid=a.aid and
c.city='Dallas' and a.city='Duluth'
ORDER BY o.ordno DESC;

(3) 查询满足以下条件的顾客的编号和姓名：没有购买过任何商品；结果按照顾客姓名从小到大排序。

select customers.cid, cname
from customers
left join orders on customers.cid = orders.cid
where orders.cid is null
order by cname;

SELECT c.cid, c.cname
FROM customers c
WHERE NOT EXISTS (
  SELECT * FROM orders o WHERE o.cid=c.cid )
ORDER BY c.cname;

SELECT c.cid, c.cname
FROM customers c
WHERE c.cid NOT IN (
    SELECT o.cid FROM orders o )
ORDER BY c.cname;

( SELECT cid, cname
FROM customers )
    EXCEPT
( SELECT c.cid, c.cname
FROM orders o , customers c
WHERE o.cid = c.cid );

(4) 查询满足以下条件的供应商编号 a 和商品编号 p：编号为 a 的供应商只销售过编号为 p 的这一种商品；结果按照供应商编号从小到大排序。

• 初版：

select distinct t2.aid a, orders.pid p
from (select t1.aid
      from (select distinct agents.aid, orders.pid
            from agents
                     join orders on agents.aid = orders.aid) t1
      group by t1.aid
      having count(t1.aid) = 1) t2
join orders on t2.aid = orders.aid
order by t2.aid;

初版思路：

1. 供应商表和订单表连接，并按供应商编号 aid 和商品编号 pid 去重，得到连接表 t1
2. 对连接表 t1 按供应商分组，找出其中只有一个订单（只销售过一种商品）的供应商编号，得到 t2
3. t2 和订单表连接，为了得到商品编号 p

改进方向：

1. 无需连接得到 t1，直接操作订单表即可

• 改进版 1：

select distinct t.aid, orders.pid # 注意要去重
from (select aid
      from orders
      group by aid
      having count(distinct pid) = 1) t
join orders on t.aid = orders.aid
order by t.aid;

• 一种错解（运行时报错）：

select aid, pid
from orders
group by aid
having count(distinct pid) = 1
order by aid;

错误原因是查询的第二个字段 pid 既不在 group by 子句中，也没有被聚合函数包裹，因此 SQL 语法不合法

但从逻辑上看，count(distinct pid) = 1 那么 pid 也只有一个，查询直接使用聚合函数 max(pid) 或 min(pid) 就可以了：

• 改进版 2：

select aid, min(pid)
from orders
group by aid
having count(distinct pid) = 1
order by aid;

SELECT DISTINCT x.aid, x.pid
FROM orders x
WHERE NOT EXISTS (
    SELECT * FROM orders y WHERE y.aid=x.aid and y.pid<>x.pid)
ORDER BY x.aid;

SELECT DISTINCT o.aid, o.pid
FROM orders o,
  (SELECT aid, COUNT(DISTINCT pid) as pid_num FROM orders GROUP BY aid) x 
WHERE o.aid = x.aid and x.pid_num = 1
ORDER BY o.aid;

(5) 查询每一个供应商的所有订单销售金额的最高值，结果返回供应商的编号及其订单金额的最高值；结果按照供应商编号从小到大排序。（不考虑没有销售订单的供应商；请分别写出：不使用统计函数、使用统计函数（使用或不使用 group by 子句）等三种不同表示方法及其返回的查询结果）

• 不使用统计函数

select distinct o1.aid, o1.dollars # 注意去重
from orders o1
left join orders o2 on o1.aid = o2.aid and o1.dollars < o2.dollars
where o2.ordno is null
order by o1.aid;

不使用统计函数那么本题语义在实现上需使用关系代数的减法

笛卡尔积的条件 o1.aid = o2.aid and o1.dollars < o2.dollars：

• 对每个 o1.aid，遍历相等的 o2.aid，保留 o1.dollars < o2.dollars 的记录

而“减”的操作使用左连接和判空实现：

• 左连接后左表不满足 o1.dollars < o2.dollars 的表项与右表无匹配项，使用 where 判空得出

当然，关键是只需找出不满足 o1.dollars < o2.dollars 的记录，也可使用 not exists 实现：

select distinct o1.aid, o1.dollars
from orders o1
where not exists(
    select *
    from orders o2
    where o1.aid = o2.aid and o1.dollars < o2.dollars
)
order by o1.aid;

SELECT DISTINCT o.aid, o.dollars
FROM orders o
WHERE o.dollars >= ALL(SELECT y.dollars FROM orders y WHERE y.aid=o.aid)
ORDER BY o.aid;

• 使用统计函数，使用 group by 子句

select aid, max(dollars)
from orders
group by aid
order by aid;

• 使用统计函数，不使用 group by 子句

select distinct
    o1.aid,
    (select max(o2.dollars)
     from orders o2
     where o2.aid = o1.aid)
from orders o1
order by aid;

SELECT DISTINCT o.aid, o.dollars
FROM orders o
WHERE o.dollars IN (SELECT MAX(y.dollars) FROM orders y WHERE y.aid=o.aid)
ORDER BY o.aid;

(6) 查询满足下述条件的供应商：该供应商每一份订单的销售金额都超过 500 元；结果返回供应商的编号及其所有订单的累计销售金额，并按照下述要求进行排序：先按照累计销售金额从大到小排序，在累计销售金额相同时再按照供应商的编号从小到大排序。（不考虑没有销售订单的供应商；请写出使用 HAVING 子句和不使用 HAVING 子句的两种不同表示方法）

• 使用 HAVING 子句

select aid, sum(dollars) ds
from orders
group by aid
having min(dollars) > 500
order by ds desc, aid;

• 不使用 HAVING 子句

• 改进版（采用）：

select aid, ds
from (select
          aid,
          sum(dollars) ds,
          min(dollars) md
      from orders
      group by aid) t
where md > 500
order by ds desc, aid;

另一种解法：逆向思维，“该供应商每一份订单的销售金额都超过 500 元”，即排除掉 “存在一份订单的销售金额不超过 500 元” 的供应商

select aid, sum(dollars) ds
from orders
where aid not in(
    select aid from orders where dollars <= 500
)
group by aid
order by ds desc, aid;

SELECT o.aid, SUM(o.dollars)
FROM orders o
WHERE NOT EXISTS(
  SELECT *
  FROM orders x
  WHERE x.aid=o.aid and x.dollars<=500)
GROUP BY o.aid
ORDER BY SUM(o.dollars) DESC, o.aid;

(7) 查询满足以下条件的顾客的编号：通过所有供应商都购买过商品；结果按照顾客编号从小到大排序。

select cid
from customers
where not exists( # 不存在一个供应商
    select *
    from agents
    where not exists( # 使得顾客没有在其那里买过商品
        select *
        from orders
        where orders.cid = customers.cid and orders.aid = agents.aid
    )
)
order by cid;

SELECT cid
FROM customers
WHERE cid NOT IN (
  SELECT c.cid FROM customers c, agents a WHERE NOT EXISTS (
      SELECT * FROM orders o WHERE o.cid = c.cid and o.aid = a.aid ) )
ORDER BY cid;

SELECT cid
FROM orders
GROUP BY cid
HAVING COUNT(DISTINCT aid) = (SELECT COUNT(*) FROM agents) # 注意去重
ORDER BY cid ASC;

SELECT cid
FROM ( SELECT cid, COUNT(DISTINCT aid) as agent_COUNT
       FROM orders
       GROUP BY cid ) AS customer_agents
WHERE agent_COUNT = (SELECT COUNT(*) FROM agents)
ORDER BY cid ASC;

(8) 查询满足以下条件的商品的编号：单价不小于 1，并且所有位于 Duluth 市的顾客都购买过；结果按照商品编号从小到大排序。

select pid
from products
where price >= 1
  and not exists( # 不存在一个位于 Duluth 市的顾客
      select *
      from customers
      where city = 'Duluth'
        and not exists( # 使得该顾客没有购买过该商品
            select *
            from orders
            where orders.pid = products.pid and orders.cid = customers.cid
        )
)
order by pid;

SELECT p1.pid
FROM products p1
WHERE p1.price >= 1 and p1.pid NOT IN (
  SELECT p2.pid
  FROM products p2, customers c
  WHERE c.city = 'Duluth' and NOT EXISTS (
      SELECT *
      FROM orders o
      WHERE o.cid = c.cid and o.pid = p2.pid )
)
ORDER BY p1.pid;

SELECT p.pid
FROM products p, customers c, orders o
WHERE p1.price >= 1 and o.pid = p.pid and o.cid = c.cid and c.city = ‘Duluth’
GROUP BY p.pid
HAVING COUNT(DISTINCT o.cid) =
      (SELECT COUNT(*) FROM customers WHERE city = 'Duluth')
ORDER BY p.pid;

(9) 查询每一位顾客的最后一份订单，结果返回顾客编号，最后一份订单的订单编号、订购年份、订购月份、距离当前的时间差（天数）。结果按照距离当前的天数从大到小排序。（以订单编号的大小区分订单的先后，编号大的订单在后；不考虑没有订单的顾客；请使用系统内置的日期函数）

select
    tmp.cid,
    tmp.mo,
    year(orders.orddate) year,
    month(orders.orddate) month,
    datediff(curdate(), orders.orddate) till_now
from (select cid, max(ordno) mo
      from orders
      group by cid) tmp
join orders on tmp.mo = orders.ordno
order by till_now desc;

SELECT o.cid, o.ordno,
  YEAR(o.orddate) lastord_year, MONTH(o.orddate) lastord_month,
  DATEDIFF(CURDATE(), o.orddate) lastord_days
FROM orders o
WHERE o.ordno >= all (SELECT x.ordno FROM orders x WHERE x.cid=o.cid)
ORDER BY lastord_days DESC;

SELECT c.cid, o.ordno,
  YEAR(o.orddate) lastord_year, MONTH(o.orddate) lastord_month,
  DATEDIFF(CURDATE(), o.orddate) lastord_days
FROM customers c left outer join orders o on c.cid = o.cid
WHERE o.ordno >= all (SELECT x.ordno FROM orders x WHERE x.cid=c.cid)
ORDER BY lastord_days DESC;

(10) 查询满足以下条件的商品编号 pid、供应商编号 aid、顾客所在城市 city：位于同一个城市 city 中的所有顾客，都通过供应商 aid 去购买过商品 pid；结果依次按照商品编号、供应商编号、顾客所在城市从小到大排序。

• 法一：集合与计数

select distinct
    t2.pid,
    t2.aid,
    t2.city
from (select city, count(cid) cnt
      from customers
      group by city) t1
join (select
          city,
          orders.aid,
          orders.pid,
          count(distinct orders.cid) cnt
      from orders
               join customers on orders.cid = customers.cid
      group by city, orders.aid, orders.pid) t2
on t1.city = t2.city and t1.cnt = t2.cnt
order by t2.pid, t2.aid, t2.city;

本题的 “考察对象” 不再是单一的商品或单一的城市，而是一个组合：(city, pid, aid)

对于一个特定的 city，可以通过顾客表得到对应的顾客总数 那么如果对订单表按 (city, pid, aid) 分组，只需与对应的 city 的顾客数量进行比较

1. 先从顾客表中统计每个城市的顾客数量，得到表 t1
2. 再从订单表与顾客表的连接表中，按城市、供应商编号、商品编号分组，统计每个组合的顾客数量，得到表 t2
3. 最后连接 t1 和 t2，找出顾客数量相等的记录，即满足条件的记录

• 法二：双重否定

select distinct
    o.pid,
    o.aid,
    c.city
from orders o
join customers c on o.cid = c.cid
where not exists( # 不存在一个位于该特定（同一）城市的顾客
    select *
    from customers
    where c.city = customers.city
      and not exists( # 使得该顾客没有通过供应商 aid 去购买过商品 pid
        select *
        from orders
        where orders.cid = customers.cid
          and orders.pid = o.pid
          and orders.aid = o.aid
    )
)
order by o.pid, o.aid, c.city;

“位于同一个城市 city 中的所有顾客，都通过供应商 aid 去购买过商品 pid”，即 “对于一个特定的组合 (city, pid, aid)，不存在一个顾客，使得该顾客位于 city 中，并且没有通过供应商 aid 去购买过商品 pid”

• 最内层子查询 (cid, pid, aid) 中 cid 关联中间层， pid 和 aid 关联最外层
• 中间层使用 c.city = customers.city 来关联最外层查询实现 “同一城市” 的限制，也是对最内层通过 city 进行的扩大范围