SQL语法学习记录（二）

牛客-SQL必知必会 里的题目，这里只记录34~50题

注意，牛客上的测评时间无太大意义，因为测试数据组数很少，结果波动非常大

-- 34. 多重where子句
SELECT cust_email
FROM Customers
WHERE cust_id in (
    SELECT cust_id FROM Orders
    WHERE order_num in (
        SELECT order_num FROM OrderItems
        WHERE prod_id='BR01'
    )
)

-- 全连接
SELECT c.cust_email
FROM OrderItems as a, Orders as b, Customers as c
WHERE a.order_num = b.order_num and b.cust_id = c.cust_id and a.prod_id = 'BR01'

-- 两个LEFT JOIN
SELECT c.cust_email
FROM OrderItems as a
LEFT JOIN Orders as b
ON a.order_num = b.order_num
LEFT JOIN Customers as c
ON b.cust_id = c.cust_id
WHERE a.prod_id = "BR01"

-- 35. left join 
SELECT a.cust_id, sum(item_price*quantity) as total_ordered
FROM Orders as a
LEFT JOIN OrderItems as b
ON a.order_num = b.order_num
GROUP BY a.cust_id
ORDER BY total_ordered desc

-- inner join = join 
SELECT b.cust_id, sum(item_price*quantity) as total_ordered
FROM OrderItems as a
JOIN Orders as b
ON a.order_num = b.order_num
GROUP BY b.cust_id
ORDER BY 2 desc


-- 36. left join
SELECT a.prod_name, sum(quantity) as quant_sold
FROM Products as a
LEFT JOIN OrderItems  as b
ON a.prod_id = b.prod_id
GROUP BY a.prod_name

-- 37. 简单的等连接语法
SELECT a.cust_name, b.order_num
FROM Customers as a, Orders as b
WHERE a.cust_id = b.cust_id
ORDER BY cust_name,order_num


-- inner join
SELECT a.cust_name, b.order_num
FROM Customers as a
INNER JOIN Orders as b
ON a.cust_id = b.cust_id
ORDER BY cust_name,order_num


-- 38. 多次left join 
SELECT cust_name, b.order_num, quantity*item_price as OrderTotal
FROM Customers as a
LEFT JOIN Orders as b
ON a.cust_id = b.cust_id
LEFT JOIN OrderItems as c
ON b.order_num = c.order_num
ORDER BY cust_name, b.order_num

-- 39. where子查询
SELECT cust_id, order_date
FROM Orders
WHERE order_num in (
    SELECT order_num FROM OrderItems WHERE prod_id='BR01'
)
ORDER BY order_date


-- 40. 多次join
SELECT cust_email
FROM OrderItems as a
LEFT JOIN Orders as b
ON a.order_num = b.order_num
LEFT JOIN Customers as c
ON b.cust_id = c.cust_id
WHERE prod_id = "BR01"


-- inner join+using语法
SELECT cust_email
FROM OrderItems as a
JOIN Orders as b using(order_num)
JOIN Customers as c using(cust_id)
WHERE prod_id = "BR01"

-- 嵌套子查询
-- a->b->c  写的时候就from c->b->a
SELECT cust_email
FROM Customers
WHERE cust_id in (
    SELECT cust_id 
    FROM Orders
    WHERE order_num in (
        SELECT order_num 
        FROM OrderItems
        WHERE prod_id = "BR01"
    )
)


-- 41. group + having
SELECT cust_name, sum(item_price*quantity) as total_price
FROM OrderItems
JOIN Orders using(order_num)
JOIN Customers using(cust_id)
GROUP BY cust_name
HAVING total_price >=1000
ORDER BY total_price

-- 反向 子查询
-- 可以发现，它其实是对order_num group的，而不是对cust_name
-- 之所以能这么做是因为只有order_num有重复，其他都是一一对应
SELECT cust_name, total_price
FROM Customers as a
JOIN Orders as b using(cust_id)
JOIN (
    SELECT order_num, sum(item_price*quantity) as total_price
    FROM OrderItems
    GROUP BY order_num
    HAVING total_price>=1000
) as c using(order_num)
ORDER BY total_price

-- 42. join
SELECT cust_name, order_num
FROM Customers
JOIN Orders using(cust_id)
ORDER BY cust_name

-- 子查询
-- 不行，拿不到order_num
SELECT cust_name
FROM Customers
WHERE cust_id in (
    SELECT cust_id FROM Orders
)
ORDER BY cust_name

-- 用left join, 要注意order_num为空的情况
SELECT cust_name, order_num
FROM Customers
LEFT JOIN Orders using(cust_id)
WHERE order_num is not null
ORDER BY cust_name

-- 43. 空也要输出，使用left join, 不能用inner join
SELECT cust_name, order_num
FROM Customers
LEFT JOIN Orders using(cust_id)
ORDER BY cust_name

-- 44.  left join
SELECT prod_name, order_num
FROM Products as a
LEFT JOIN OrderItems as b using(prod_id)
ORDER BY prod_name

-- 不过题目强制要求用outer join, mysql不支持
-- 使用union left join + right join 
SELECT prod_name, order_num
FROM Products as a
LEFT JOIN OrderItems as b using(prod_id)
UNION
SELECT prod_name, order_num
FROM Products as a
RIGHT JOIN OrderItems as b using(prod_id)

ORDER BY prod_name

-- 45. 使用unoin+left join+right join模拟 outer join, 再分组查询
SELECT prod_name, count(order_num) as orders
FROM (
    SELECT prod_name, order_num
    FROM Products as a
    LEFT JOIN OrderItems as b using(prod_id)
    UNION
    SELECT prod_name, order_num
    FROM Products as a
    RIGHT JOIN OrderItems as b using(prod_id)
) as a
GROUP BY prod_name
ORDER BY prod_name

-- 46. 不想写outer join了，left join真香
SELECT vend_id, count(prod_id) as prod_id
FROM Vendors
LEFT JOIN Products using(vend_id)
GROUP BY vend_id
ORDER BY vend_id

-- 47. union
-- join---连接表，对列操作
-- union--连接表，对行操作。
-- union--将两个表做行拼接，同时自动删除重复的行。
-- union all---将两个表做行拼接，保留重复的行。

SELECT prod_id, quantity FROM OrderItems WHERE quantity=100
UNION
SELECT prod_id, quantity FROM OrderItems WHERE prod_id like "BNBG%"
ORDER BY prod_id

-- 48. 将两个select拼接成一个select, 关系为or
SELECT prod_id, quantity 
FROM OrderItems
WHERE quantity=100 or prod_id like 'BNBG%'

-- 49. 简单union
SELECT prod_name FROM Products
UNION
SELECT cust_name as prod_name FROM Customers
ORDER BY prod_name

-- 50. 两个union,只需要最后排序一次
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' 
UNION 
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'IL'

ORDER BY cust_name;

-- 改成or更简单啦
SELECT cust_name, cust_contact, cust_email 
FROM Customers 
WHERE cust_state = 'MI' or cust_state = 'IL'
ORDER BY cust_name;