SQL:如何查看一个列表中有多少项出现在一个表中?

I don't care about US cities, but just as an example - say I have a table of US cities, and I want to know how many from a list are in that table.

我不关心我们的城市，只是举个例子——假设我有一张美国城市的表格，我想知道列表中有多少城市。

SELECT
     COUNT(*)

FROM
     CITY_TABLE C

WHERE
    --the list
    C.NAME IN ('San Francisco', 'Springfield', 'Murderville')

The above would give how many hits - maybe 70, who knows., but what I really want is a response from 0-3.

上面给出了多少点击量——可能是70次，谁知道呢。，但我真正想要的是0-3的回应。

i.e., how many from my list, appear in the table.

即。，从我的列表中，有多少出现在表格中。

You could just run the above query, then use Excel to get the answer.
If in a more typical programming language, you could run a loop that has an x + 1 in it.

您可以运行上面的查询，然后使用Excel获取答案。如果使用更典型的编程语言，您可以运行一个包含x + 1的循环。

But is there a way to do this directly in an SQL query? Specifically T-SQL I guess, but I'm kinda curious in general.

但是有没有一种方法可以直接在SQL查询中实现这一点呢?我想是T-SQL，但总的来说我还是有点好奇。

1 个解决方案

#1

You can use this.

你可以使用这个。

SELECT
     COUNT(DISTINCT C.NAME)
FROM
     CITY_TABLE C
WHERE
    --the list
    C.NAME IN ('San Francisco', 'Springfield', 'Murderville')