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筆數多,想利用這個方式找出哪裡缺了哪裡多了 求大神幫幫忙(跪
A.用 EXCEL 處理 :
TC053,smoda_num001 個別先在 EXCEL 內移除重複項
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
參考~比較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)