請教各位
有A,B兩表是一對多關係，需要JOIN出一個結果
條件為
以A資料筆數為主
B需要判斷STATUS是否為Y
如果Y，則抓取DATE
如果非Y，則抓取最大的DATE
請問MSSQL該如何下?
結構如下
表A
| ID | NAME |
|---|---|
| 001 | 王小明 |
| 002 | 張小花 |
| 003 | 李小壯 |
| 004 | 黃小美 |
表B
| ID | STATUS | DATE |
|---|---|---|
| 001 | N | 2024-01-01 |
| 001 | N | 2024-01-10 |
| 001 | Y | 2024-01-28 |
| 002 | X | 2024-03-10 |
| 002 | N | 2024-03-25 |
| 003 | N | 2024-02-05 |
| 003 | Y | 2024-02-13 |
| 004 | N | 2024-01-13 |
| 004 | R | 2024-01-15 |
預期結果
| ID | NAME | DATE |
|---|---|---|
| 001 | 王小明 | 2024-01-28 |
| 002 | 張小花 | |
| 003 | 李小壯 | 2024-02-13 |
| 004 | 黃小美 |
以下是原本預期結果 (抱歉已分享解法的大大，預期結果改成欄位留空，抱歉...)
| ID | NAME | DATE |
|---|---|---|
| 001 | 王小明 | 2024-01-28 |
| 002 | 張小花 | 2024-03-25 |
| 003 | 李小壯 | 2024-02-13 |
| 004 | 黃小美 | 2024-01-15 |
之前是使用UNION來完成的
想再請各位指點是否有其他辦法，謝謝 !
已邀請的邦友 {{ invite_list.length }}/5
恩~是不知道你的日期會不會突然跳大的日期~不然B寫法簡單~
declare @A table(
ID nvarchar(10)
,Name nvarchar(10)
)
insert into @A
values('001','王小明')
,('002','張小花')
,('003','李小壯')
,('004','黃小美')
declare @B table(
ID nvarchar(10)
,[STATUS] nvarchar(10)
,[DATE] DATE
)
insert into @B
values('001','N','2024-01-01')
,('001','N','2024-01-10')
,('001','Y','2024-01-28')
,('002','X','2024-03-10')
,('002','N','2024-03-25')
,('003','N','2024-10-05')
,('003','Y','2024-02-13')
,('004','N','2024-01-13')
,('004','R','2024-01-15')
--A寫法
select ID
,Name
,max(Max_Date) Max_Date
from (
select a.ID
,a.Name
,isNull((
select max(
case when c.[STATUS] = 'Y'
then [DATE]
else null
end
)
from @B c
where A.ID = C.ID
),[DATE]) Max_Date
from @A a
left join @B b on a.ID = b.ID
) k
group by ID
,Name
--B寫法
select a.ID
,a.Name
,max(b.Date) Max_Date
from @A a
left join @B b on a.ID = b.ID
group by a.ID
,a.Name
--C寫法
select a.ID
,a.Name
,isNull(
max(case when b.[STATUS] = 'Y' then b.Date else null end)
,max(b.[Date])
) Max_Date
from @A a
left join @B b on a.ID = b.ID
group by a.ID
,a.Name
order by a.ID
不好意思，如果結果如下，是不是無法用B寫法調整了，謝謝
預期結果
| ID | NAME | DATE |
|---|---|---|
| 001 | 王小明 | 2024-01-28 |
| 002 | 張小花 | |
| 003 | 李小壯 | 2024-02-13 |
| 004 | 黃小美 |
我自己是有直覺地寫出B寫法，但如果遇到要留空，就突然卡住....
好像能在SELECT中去CASE，但無法在WHERE子句中處理
可以這樣~
select a.ID
,a.Name
,isNull(
max(case when b.[STATUS] = 'Y' then b.Date else null end)
,max(b.[Date])
) Max_Date
from @A a
left join @B b on a.ID = b.ID
group by a.ID
,a.Name
order by a.ID
感謝大大，我將您的說明調整以下就是我想呈現的方式，謝謝
select a.ID
,a.Name
,isNull( max( case when b.[STATUS] = 'Y' then b.Date
else null end)
,null
) Max_Date
from @A a
left join @B b on a.ID = b.ID
group by a.ID ,a.Name
order by a.ID
--C 寫法 改
SELECT
A.ID,
A.NAME,
CASE
WHEN (SELECT count(DATE) FROM #B B WHERE B.ID = A.ID AND B.STATUS = 'Y')>0
THEN subquery.MAX_DATE
ELSE null
END AS 'DATE'
FROM
#A A
CROSS APPLY
(
SELECT MAX(B.DATE) AS MAX_DATE
FROM #B B
WHERE B.ID = A.ID
) AS subquery;
SELECT * INTO #A FROM ( VALUES
('001',N'王小明')
,('002',N'張小花')
,('003',N'李小壯')
,('004',N'黃小美')
) AS A([ID],[NAME])
;
SELECT * INTO #B FROM ( VALUES
('001','N',CONVERT(DATE,'2024-01-01'))
,('001','N','2024-01-10')
,('001','Y','2024-01-28')
,('002','X','2024-03-10')
,('002','N','2024-03-25')
,('003','N','2024-02-05')
,('003','Y','2024-02-13')
,('004','N','2024-01-13')
,('004','R','2024-01-15')
) AS B([ID],[STATUS],[DATE])
;
SELECT * FROM #A;
SELECT * FROM #B;
-- version 1
WITH S AS (
SELECT A.ID, A.NAME, B.[DATE]
, ROW_NUMBER() OVER(PARTITION BY A.ID ORDER BY IIF(STATUS = 'Y',0,1),[DATE]) [SN]
FROM #A A
LEFT OUTER JOIN #B B ON A.ID = B.ID
)
SELECT S.ID, S.NAME, S.DATE
FROM S WHERE SN = 1
;
-- version 2 (沒Y留空)
SELECT A.ID, A.NAME, B.[DATE]
FROM #A A
LEFT OUTER JOIN #B B ON A.ID = B.ID AND B.[STATUS] = 'Y'
;
參考看看
SELECT
A.* ,
IIF(IsIncludeY > 0 , MaxDate , NULL) AS [Date]
FROM tblA AS A
JOIN
(
SELECT ID ,
SUM(IIF([Status] = 'Y' , 1 , 0)) AS IsIncludeY ,
MAX([Date]) AS MaxDate
FROM tblB
GROUP BY ID
) AS B ON A.ID = B.ID
看影片追技術