我的表 a 有 s_id,status_id,created_on 列。表結構-A：
s_id status_id created_on
bbbef7457cf38d1e265955 54 2021-11-23 14:20:09
bbbef7457cf38d1e265955 5 2021-11-22 14:20:17
bbbef7457cf38d1e265955 1 2021-11-20 14:20:29
bbbef7457cf38d1e265955 7 2021-11-19 12:20:00
另一個 B 表：
id task status
54 Mobile Number SUCCESS
5 Mobile Number FAILURE
1 Email SUCCESS
7 EMail Failure
我正在嘗試為 表 A 中的任務手機號碼選擇最新的created_on
我試過的查詢
select
max (case when id = 5 or id=54 then created_on end) as mobileNumber
from A where s_id='bbbef7457cf38d1e265955'
預期產出
MobileNumber EMail
2021-11-23 14:20:09 2021-11-20 14:20:29
這沒有按預期作業。
uj5u.com熱心網友回復：
如果您希望通過 s_id 持續 created_on，則查詢將是：
select
s_id
,max (created_on) as mobileNumber
from A
where s_id='bbbef7457cf38d1e265955'
AND id in (5,54)
GROUP BY s_id
uj5u.com熱心網友回復：
它可以通過視窗函式來完成：
with cte as (
select a.*, row_number() over (
partition by a.s_id, b.task
order by a.created_on desc
) as rn
from a
join b on a.status_id = b.id
)
select *
from cte
where rn = 1
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/379326.html
標籤：sql sql-server