各位大大請教一下，MSSQL指令有TableX跟TableA資料表，X資料表就正常的查詢出來，但A資料表裡面有資料的時候，就要取代X裡面的資料，條件就是日期，大概是這樣。
這樣做得到嗎？
感謝各位大大的回覆，我好像說的不夠詳細，不過R大大的方法一，有符合需求，我要表達的意思，當20210102這個日期，在X有10筆資料，只要A有20210102有資料1筆，就要被取代掉。查詢出來的表格20210102這個就只有A的一筆資料，如果A有20筆資料，那查詢出來的就是只有A的20筆
已邀請的邦友 {{ invite_list.length }}/5
CREATE TABLE TableX
(ID varchar(20),
NO varchar(20),
DIA varchar(20));
INSERT INTO TableX
VALUES
('20210101','A01','100'),
('20210101','A02','200'),
('20210102','A01','100'),
('20210103','A01','100'),
('20210103','A01','100');
CREATE TABLE TableA
(ID varchar(20),
NO varchar(20),
DIA varchar(20));
INSERT INTO TableA
VALUES
('20210102','X01','100'),
('20210102','X02','150');
-- 解法一
SELECT X.ID,X.NO,X.DIA
FROM TableX AS X
WHERE X.ID NOT IN (SELECT B.ID FROM TableA AS B)
UNION ALL
SELECT A.ID,A.NO,A.DIA
FROM TableA AS A
ORDER BY X.ID,X.NO
-- 解法二
SELECT A.ID,
CASE WHEN X.NO IS NOT NUll THEN X.NO ELSE A.NO END AS NO,
CASE WHEN X.DIA IS NOT NUll THEN X.DIA ELSE A.DIA END AS DIA
FROM TableX AS A
LEFT JOIN TableA AS X ON X.ID = A.ID
-- 解法三
select X.ID
, coalesce(A.NO, X.NO) AS NO
, coalesce(A.DIA, X.DIA) AS DIA
from TableX AS X
left join TableA AS A
on X.ID = A.ID
我也來插一腳XD...
-- 解法四
SELECT a.ID
,isNull(b.NO,a.NO) NO
,isNull(b.DIA,a.DIA) DIA
FROM TableX a
left join TableA b on a.ID = b.ID
SELECT X.ID AS ID ,
CASE
WHEN A.NO IS NOT NULL
THEN A.NO
ELSE X.NO
END AS NO ,
CASE
WHEN A.DIA IS NOT NULL
THEN A.DIA
ELSE X.DIA
END AS DIA
FROM X
LEFT JOIN A
ON A.ID = X.ID
iThome鐵人賽
看影片追技術