5、6.合并

5-1.175.组合两个表

1）左联结（left outer join），联结结果保留左表的全部数据

 

2）右联结（right outer join），联结结果保留右表的全部数据

 

3）内联结（inner join），取两表的公共数据

 

# Write your MySQL query statement below
SELECT
   Person.firstname firstName,Person.lastname lastName,Address.city,Address.state
FROM
   Person LEFT OUTER JOIN Address ON Person.personID = Address.personID; //别忘了：on 两个表联结条件！！

 

5-2.1581.进店却未进行过交易的顾客

 LEFT OUTER JOIN + GROUP 

# Write your MySQL query statement below
SELECT customer_id,COUNT(customer_id) count_no_trans
FROM 
 Visits LEFT OUTER JOIN Transactions ON Visits.visit_id = Transactions.visit_id
WHERE 
 amount IS NULL
GROUP BY customer_id;

 

5-3.1148.文章浏览I

# Write your MySQL query statement below
SELECT DISTINCT
   author_id id
FROM
   Views
WHERE
   author_id = viewer_id
ORDER BY author_id;

 

6-1.197.上升的温度

时间计算函数：https://leetcode.cn/problems/rising-temperature/solution/tu-jie-sqlmian-shi-ti-ru-he-bi-jiao-ri-qi-shu-ju-b/

1.datediff(日期1, 日期2)：

得到的结果是日期1与日期2相差的天数

如果日期1比日期2大，结果为正；如果日期1比日期2小，结果为负；

2.timestampdiff(时间类型, 日期1, 日期2)：

这个函数和上面diffdate的正、负号规则刚好相反。

日期1大于日期2，结果为负，日期1小于日期2，结果为正。

在“时间类型”的参数位置，通过添加“day”, “hour”, “second”等关键词，来规定计算天数差、小时数差、还是分钟数差

 

i.

# Write your MySQL query statement below
SELECT w1.id Id
FROM 
   Weather w1,Weather w2
WHERE
   w1.Temperature > w2.Temperature AND DATEDIFF(w1.recordDate,w2.recordDate) = 1;

ii.

# Write your MySQL query statement below
SELECT w1.id Id
FROM 
   Weather w1 /*ACROSS*/ JOIN Weather w2 ON w1.Temperature > w2.Temperature AND DATEDIFF(w1.recordDate,w2.recordDate) = 1;   

 

6-2.607.销售员

题目没说公司RED的代号一定是1，所以where后面条件为

Company.name = 'RED' AND Orders.com_id = Company.com_id 而不是

Company.com_id = 1 AND Orders.com_id = Company.com_id

i.

# Write your MySQL query statement below
SELECT SalesPerson.name
FROM 
  SalesPerson
WHERE 
  SalesPerson.sales_id NOT IN (SELECT Orders.sales_id FROM Company,Orders WHERE Company.name = 'RED' AND Orders.com_id = Company.com_id);

ii. "LEFT OUTER JOIN" 也可以写作 "LEFT JOIN" 

SELECT s.name
FROM
    salesperson s
WHERE
    s.sales_id NOT IN (SELECT o.sales_id
               FROM orders o LEFT JOIN company c ON o.com_id = c.com_id
               WHERE c.name = 'RED');