请教个SQL的面试题

P1.  You are given three separate tables (named trip_initiated, trip_cancel, and trip_complete) of the form:  

trip_initiated | trip_id, rider_id, driver_id, timestamp
trip_cancel | trip_id, rider_id, driver_id, timestamp
trip_complete | trip_id, rider_id, driver_id, timestamp

Each trip_id in these tables will be unique and only appear once, and a trip will only ever result in a single cancel event or it will be completed. Write a query to create a single table with one row per trip event sequence (trip initiated → cancel/complete):

dispatch_events | trip_id, rider_id, driver_id, initiated_ts, cancel_ts, complete_ts

There should only be a single row per trip with a unique trip_id.  

P2.  Write at least one test query to validate the data in the resulting table. Indicate what you would expect the query to return if the data were valid.


P1 如下写法对不对？

SELECT ti.trip_id trip_id, ti.rider_id rider_id, ti.driver_id driver_id, ti.timestamp initiated_ts, tc1.timestamp cancel_ts, tc2.timestamp complete_ts
FROM trip_initiated ti
JOIN trip_cancel tc1
ON t1.trip_id = tc1.trip_id
JOIN trip_complete tc2
ON t1.trip_id = tc2.trip_id


还有请教P2题目是什么意思？ 谢谢！

个人想法，有错欢迎指正。P1不能join， 需要left join两个table， 因为join的话你选择的是两个table都有的。2和3两个都是1的子集，需要left join

p2没见过，我的想法是，验证给的数据是valid的，根据题目，首先2和3没有交集，可以join 2，3看一下有没有交集。然后2，3各自不能有duplicate，可以看一下2，3 count(*)和count(unique id)是不是一样多。

P1可以只做一次join（或者left outer join，如果存在既没有cancel又没有complete的trip的话）

1. SELECT ti.trip_id, ti.rider_id, ti.driver_id,
   
2.        ti.timestamp AS initiated_ts,
   
3.        tc.cancel_ts, tc.complete_ts
   
4. FROM trip_initiated ti LEFT OUTER JOIN (
   
5.        SELECT trip_id, timestamp AS cancel_ts, NULL as complete_ts
   
6.        FROM trip_cancel
   
7.        UNION ALL
   
8.        SELECT trip_id, NULL AS cancel_ts, timestamp as complete_ts
   
9.        FROM trip_complete
   
10.      ) tc ON ti.trip_id = tc.trip_id;
    

复制代码

magicsets 发表于 2019-1-14 09:19
P1可以只做一次join（或者left outer join，如果存在既没有cancel又没有complete的trip的话）

[mw_shl_c ...

这题要用union all吗？ 如果两个table没有交集是不是用union也可以呢

little9 发表于 2019-1-19 04:10
这题要用union all吗？ 如果两个table没有交集是不是用union也可以呢

union等价于union distinct，相当于union all + 去重，换句话说union all是不去重的

参考：https://dev.mysql.com/doc/refman/8.0/en/union.html

所以应该是“一般情况下用union，然后如果两个table没有交集那么用union all也可以”


补充内容 (2019-1-19 06:12):
给的那个链接网页中有这么一句：A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.

1. 两个join都改成left join
2. 在result table里query trip_cancel表中的trip_id, 返回的query中complete_ts应该是null；同理在result table里query trip_complete表中的trip_id, 返回的query中cancel_ts应该是null
个人想法哈 你看对么

SQL小白拿第一题练一下手，请各位大神指教，请指出错误的地方，好让我学习学习，谢谢：

SELECT a.trip_id, a.rider_id, a.driver_id,
                a.timestamp as initiated_ts,
                b. timestamp as cancel_ts,
                c.timestamp as complete_ts
FROM  
(SELECT a.trip_id, a.rider_id, a.driver_id,
                  a.timestamp as initiated_ts,
                  b. timestamp as cancel_ts
FROM trip_initiated  a LEFT JOIN trip_cancel b ON a.trip_id = b.trip_id)
LEFT JOIN trip_complete  c ON a.trip_id = c.trip_id