請教一道SQL題

樓主最近在準備SQL的面試，忽然想到之前面過的一道題，但是記不清另外一種方法怎麼寫了。總之大概意思是求每個event type最近一天的數值是多少：
Event_type        Date        Num
A                        8/1           60
A                        8/2          45
B                        8/1           21
B                        8/2           11
A                        8/3           55
所以最後結果應該是A對應8/3的55， B對應8/2 的11。樓主知道可以用row number來求。但是我記得有一種更簡單的方法，好像用where date等於一個東西。所以如果不用row number跟join，要怎麼寫呢？ 希望各位大神幫忙一下！

PostgreSQL / MySQL 特有的解法：（2颗星，考察你是否了解 PostgreSQL / MySQL 和 SQL Server 的区别，实际ETL中不推荐，执行效率很低）

1. SELECT Event_type,
   
2.        Date,
   
3.        Num
   
4. FROM EVENT
   
5. WHERE (Event_type,Date) IN
   
6.                       ( SELECT Event_type,MAX(Date)
   
7.                         FROM EVENT
   
8.                         GROUP BY Event_type )

复制代码

SQL Server 传统解法一：（3颗星）

1. SELECT Event_Type,
   
2.        Date,
   
3.        Num
   
4. FROM (
   
5.     SELECT Event_Type,
   
6.            Date,
   
7.            Num,
   
8.            DENSE_RANK() OVER(PARTITION BY Event_Type ORDER BY Date DESC) AS RK
   
9.     FROM Event) T
   
10. WHERE RK = 1

复制代码

SQL Server 传统解法二：（3.5颗星）

1. WITH CTE AS
   
2. (
   
3. SELECT Event_Type,MAX(Date) AS DATE FROM Event GROUP BY Event_Type
   
4. )
   
5. 
   
6. SELECT E.Event_Type,
   
7.        E.Date,
   
8.        E.Num
   
9. FROM Event E INNER JOIN CTE ON E.Event_Type = CTE.Event_Type AND E.Date = CTE.DATE
   
10. ORDER BY 1 ASC

复制代码

SQL Server 传统解法三：（4颗星）

1. WITH CTE AS
   
2. (
   
3. SELECT Event_Type,MAX(Date) AS DATE FROM Event GROUP BY Event_Type
   
4. )
   
5. 
   
6. SELECT E.Event_Type,
   
7.        E.Date,
   
8.        E.Num
   
9. FROM Event E,CTE
   
10. WHERE E.Event_Type = CTE.Event_Type AND E.Date = CTE.DATE
    
11. ORDER BY 1 ASC

复制代码

SQL Server 传统解法四：（2.5颗星）

1. SELECT Event_type,
   
2.        Date,
   
3.        Num
   
4. FROM EVENT E1
   
5. WHERE Date = (SELECT MAX(Date)
   
6.                         FROM EVENT E2
   
7.                         WHERE E1.Event_type = E2.Event_type)
   
8. ORDER BY 1 ASC

复制代码

where (event_type, date) in (select event_type, max(date) from table group by event_type) 大概这个意思？不太确定有没有语法错误

可以用correlated subquery来做

SELECT Event_type,
             Num
FROM table OUTER
WHERE Date = (SELECT MAX(Date)
                        FROM table INNER
                        WHERE INNER.Event_type = OUTER.Event_type)

colospring 发表于 2020-08-20 00:11:51
where (event_type, date) in (select event_type, max(date) from table group by event_type) 大概这个意思？不太确

就是這個意思。謝謝🙏🙏

这样用where去check的话，会很慢啊。
应该用一张subquery去搞一下with test as (select *, rank() over(partition by event_type order by date desc) as xxx from table)
然后在 select * except xxx from test where xxx = 1就差不多了

sdszilong 发表于 2020-08-20 01:49:54
这样用where去check的话，会很慢啊。
应该用一张subquery去搞一下with test as (select *, rank() over(partition by event_type

怎麼決定哪種方法更快？
row number也要把每個都過一遍⋯

不知道这样可不可以。。。如果有error的话 估计还是要用where date=（select。。）
select event_type,  max(date), num from xx, group by event_type

rongmeimei 发表于 2020-8-20 23:06
怎麼決定哪種方法更快？
row number也要把每個都過一遍⋯

额，其实在用的时候，计算速度不是太大的问题。
我的理解是，这个rank里面有partition by啊，所以只会对每个不同的event进行一个rank而已，如果event不是很单一的话，应该会快。
用where (event, date) in select (....)的话，相当于是每个event,date都要去查一张你后面生成的表格，这个cost应该会很大。比这个好一些的是把后面那个select也做成subtable，然后两张表去join就好了。

原來如此。我不是很懂cost方面... 當時面這道題的時候我是用row number，但是那個面試官給我展示了where的用法...

我还是不凑这热闹了。告辞告辞