合并两个不相关查询的输出

Question

我需要一次性执行两个查询，并在一个组合输出中生成结果。两个查询都只返回1行

查询1:

SELECT city, state FROM "Table1" WHERE Id = '123'

查询2:

select count(*) as colCount from "Table2" where name = "xyz"

输出格式：city, state, colCount

Answer 1

使用子查询：

SELECT city, state, 
       colCount = (SELECT count(*)
                   FROM Table2 
                   WHERE name = "xyz")
FROM Table1 
WHERE Id = '123'

子查询不能返回多行。但事实并非如此。您可以使用TOP 1来确保它，但如果它是异常的，我更喜欢一个异常。

Answer 2

以下查询适用于您：

SELECT city, state, 
       colCount = (SELECT count(*)
                   FROM Table2 
                   WHERE name = "xyz") as colCount
FROM Table1 
WHERE Id = '123'