iT邦幫忙

1

SQL 不同伺服器的SELECT 結合

SELECT count(*) as WF_PO_count,TC053 COLLATE Chinese_Taiwan_BOPOMOFO_CI_AI
FROM [192.168.0.246].[TPS].[dbo].[COPTC]
WHERE TC027='Y' AND TC002 LIKE '11001%'
GROUP BY TC053
ORDER BY WF_PO_count,TC053 DESC

SELECT COUNT(*) as HT1000_PO_count,smoda_num001
FROM [NEWHERAN].[dbo].[smoda]
WHERE smoda_docdt like '2021-01%' and smoda_stus='Y' and smoda_site='HERAN'
GROUP BY smoda_num001
ORDER BY HT1000_PO_count,smoda_num001 DESC

以上A的TC053 和 B的smoda_num001 是一樣的 想知道怎麼結合憶起 A筆數筆B筆數多,想利用這個方式找出哪裡缺了哪裡多了 求大神幫幫忙(跪

看更多先前的討論...收起先前的討論...
在線等了
rogeryao iT邦大師 5 級 ‧ 2021-02-22 14:12:27 檢舉
1.不同伺服器 ? 2 台伺服器都是 MSSQL ?
2. A,B 是 Table ?
3.A.TC053 及 B.smoda_num001 是欄位 ?

建議將資料建到 https://dbfiddle.uk/?rdbms=sqlserver_2017
石頭 iT邦研究生 2 級 ‧ 2021-02-22 14:13:55 檢舉
聽起來你在找 UNION ALL
https://www.1keydata.com/tw/sql/sqlunionall.html
rogeryao iT邦大師 5 級 ‧ 2021-02-22 14:36:51 檢舉
若是一次性需求 , 匯出到 Excel 處理可能較快
因為他們筆數不同但是廠商是一樣的 想利用廠商對應的比數去找出哪個有少的
EXCEL 嗎? 要怎麼做@@

2 個回答

1
rogeryao
iT邦大師 5 級 ‧ 2021-02-22 15:01:18
最佳解答

A.用 EXCEL 處理 :
TC053,smoda_num001 個別先在 EXCEL 內移除重複項
https://ithelp.ithome.com.tw/upload/images/20210222/20085021HLI1tvBkLf.png

https://ithelp.ithome.com.tw/upload/images/20210222/20085021xjR7BgaB9z.png

https://ithelp.ithome.com.tw/upload/images/20210222/20085021heLB5VRdVT.png

B.MSSQL 處理 :
a.不同伺服器串接
1.請參閱 : 建立連結的伺服器 (SQL Server Database Engine)
2.請參閱 :sp_addlinkedserver (Transact-SQL)
b.SQL 指令

CREATE TABLE TABLE1 (
TC053 varchar(40) NULL);

INSERT INTO TABLE1 (TC053)
VALUES 
('a'),
('c'),
('e'),
('e'),
('a'),
('b'),
('c');
CREATE TABLE TABLE2 (
smoda_num001 varchar(40) NULL);

INSERT INTO TABLE2 (smoda_num001)
VALUES 
('a'),
('c'),
('k'),
('k'),
('a'),
('b'),
('c');
SELECT DISTINCT A.TC053,B.smoda_num001
FROM (
SELECT 'X' AS X,TC053,'' AS Y
FROM TABLE1) AS A
FULL OUTER JOIN (
SELECT '' AS X,smoda_num001,'Y' AS Y
FROM TABLE2) AS B ON A.TC053=B.smoda_num001
WHERE A.X IS NULL
OR B.Y IS NULL

Demo

1
純真的人
iT邦高手 1 級 ‧ 2021-02-22 16:09:07

參考~比較A或B表哪個多哪個缺~

declare @ta table(
	ts1 nvarchar(50)
	,Name1 nvarchar(50)
)
declare @tb table(
	ts2 nvarchar(50)
	,Name2 nvarchar(50)
)

insert into @ta
values('454656456','aaa')
,('4234324','aaa')
,('45345345','bbb')
,('42345654324','bbb')
,('456546','aaa')
,('547867','aaa')

insert into @tb
values('454656456','aaa')
,('4234324','aaa')
,('45345345','bbb')
,('42345654324','bbb')
,('456546','ccc')
,('547867','ccc')

select Name
,Sum(Num1) ta_Num1
,Sum(Num2) tb_Num2
from ((
	select count(*) Num1
	,0 Num2
	,Name1 Name
	from @ta
	group by Name1
)union all(
	select 0 Num1
	,count(*) Num2
	,Name2 Name
	from @tb
	group by Name2
)) k
group by Name
having Sum(Num1) <> Sum(Num2)

https://ithelp.ithome.com.tw/upload/images/20210222/20061369zGZ2wiFx49.png

我要發表回答

立即登入回答