在SQL Server中使用AND命令未给出预期结果

Question

在执行其他代码之前，我正在尝试编写一些代码来确定一个表中的两个值是否存在于另一个表。我发现这些：

select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Home Team');
select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Away Team');

当值出现在表Team列TeamName中时，两者都会按预期返回值1。然而，这：

select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Home Team')
AND TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Away Team');

当（据我所见）它应该组合前两个查询并返回1时，返回0。有人能告诉我我做错了什么吗？

Answer 1

我认为这样更可读：

select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header in ('Home Team', 'Away Team'))

返回1的一种方法是解码结果：

select case when count(1) = 0 then 0 else 1 end
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header in ('Home Team', 'Away Team'))

Answer 2

您当前的查询显示：统计TeamName为主客场团队的所有行。用OR替换AND：一支球队可以是主队也可以是客场球队：

select count(1)
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Home Team')
OR TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Away Team');

编辑：还有一种快速而肮脏的方式，只返回1:

select CASE WHEN count(1) > 0 THEN 1 ELSE 0 END
from Team
where TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Home Team')
OR TeamName IN (select Data FROM CSVTest_Match WHERE Header = 'Away Team');