SQL Server - 为where子句条件提供优先级

请考虑以下SQL

Please consider the following SQL

declare @t1 table(site int, id int, name varchar(2))
declare @t2 table(site int, id int, mark int)

insert into @t1
select 1,1,'A'
union select 1,2,'B'
union select 1,3,'C'
union select 2,2,'D'
union select 2,3,'C'

insert into @t2
select 1,1,10
union select 1,2,20
union select 0,3,30
union select 1,3,40
union select 2,3,40
union select 2,3,40

select distinct a.site, a.id,a.name,b.mark
from @t1 a 
inner join @t2 b
on (a.site =b.site or b.site = 0) and a.id = b.id
where a.site=1

它产生以下结果

网站ID名称标记

------------------------- ---

1 1 A 10

1 2 B 20

1 3 C 30

1 3 C 40



这是正确的。



但我想要一个人的数据一次。 SQL应首先检查@ t2中是否有针对特定站点的人员条目。如果找到了条目，则使用它。如果没有，那个人的标记将是在网站0中具有相同名称的人的标记。



在这种情况下，我希望结果为如下：

网站ID名称标记

----------------------------

1 1 A 10

1 2 B 20

1 3 C 40



但是如果（1,3,40）不在@ t2，结果应该如下。

网站ID名称标记

---- ------------------------

1 1 A 10

1 2 B 20

1 3 C 30



我该怎么做？

我可以用CTE来做。

所以请给我一个更快的方法。

我会在大约1亿行上运行它。

It produces the following result
site id name mark
----------------------------
1 1 A 10
1 2 B 20
1 3 C 30
1 3 C 40

It''s correct.

But I want a person''s data exactly once. The SQL should first check whether there is an entry for a person in @t2 for a specific site. If entry is found, then use it. If not, the mark of that person will be the person''s mark who has the same name in site 0.

In this case, I want the result as follows.
site id name mark
----------------------------
1 1 A 10
1 2 B 20
1 3 C 40

But if (1,3,40) isn''t in @t2, The result should be as follows.
site id name mark
----------------------------
1 1 A 10
1 2 B 20
1 3 C 30

How can I do this?
I can do it using CTE.
So please provide me a faster way.
I''ll run it on about 100 millions rows.