EXCEPT ALL sql server不支持。和table在一起

t1
a | b
--+--
1 | 1
1 | 1
1 | 1
1 | 2
1 | 2
1 | 3

以及

t2
a | b
--+--
1 | 1
1 | 2
1 | 4

查询

select a, b from t1
except all
select a, b from t2
order by a, b;

会回来的

a | b
--+--
1 | 1
1 | 1
1 | 2
1 | 3

因为t1比t2多包含两（1 | 1）行、一（1 | 2）行和一（1 | 3）行。
要在sql server中实现同样的效果，请对行进行编号：

select a, b from
(
  select a, b, row_number() over (partition by a, b order by a) as rn from t1
  except
  select a, b, row_number() over (partition by a, b order by a) as rn from t2
) evaluated
order by a, b;

尝试where not exists子句，这将允许您不获取不同的列表。

Use AdventureWorks2017;
Go
Create Table #A (a int, b varchar(1))
Insert into #A (a, b)
Select 1, 'A' UNION ALL
Select 1, 'A' UNION ALL
Select 1, 'A' UNION ALL
Select 2, 'B'
Create Table #B (a int, b varchar(1))
Insert into #B (a, b)
Select 2, 'B'
Select a, b 
FROM #A 
EXCEPT
Select a, b
From #B
Select a, b
from #A as a where not exists (Select 1 from #B as b
where a.a = b.a and a.b = b.b)

通过运行上述代码，可以看到where not exists子句和except之间的不同结果集。
希望这有帮助。

根据定义， EXCEPT 从左输入查询返回右输入查询未输出的不同行。
sql小提琴
ms sql server 2017架构设置：

CREATE TABLE MyTableA (ColA int, ColB int)
CREATE TABLE MyTableB (ColA int, ColB int)
INSERT INTO MyTableA (ColA, ColB) VALUES (15,1),(10,1),(2,1),(2,1),(16,1),(2,2),(3,3),(3,3)
INSERT INTO MyTableB (ColA, ColB) VALUES (1,1),(1,1),(1,1),(2,2),(4,5),(1,1),(4,5)

查询1：

SELECT * FROM MyTableA
EXCEPT
SELECT * FROM MyTableB

结果：

| ColA | ColB |
|------|------|
|    2 |    1 |
|    3 |    3 |
|   10 |    1 |
|   15 |    1 |
|   16 |    1 |

问题2：

Select *
from MyTableA as a where not exists (Select 1 from MyTableB as b
where a.ColA = b.ColA and a.ColB = b.ColB)

结果：

| ColA | ColB |
|------|------|
|   15 |    1 |
|   10 |    1 |
|    2 |    1 |
|    2 |    1 |
|   16 |    1 |
|    3 |    3 |
|    3 |    3 |