来自内连接多个DB表的SQL计数
来自内连接多个DB表的SQL计数
提问于 2020-03-26 18:41:14
下面的查询返回几行，但是我想对BCO.MAIN_ID进行计数，因此在最终输出中，每一行中的列BCO.MAIN_ID都有一个连接到BCO.MAIN_ID的用户的计数。
SELET CS.[TEST_ID], CS.[TESTGROUP_ID], BCO.[MAIN_ID] as COUNT FROM [DB_01].[dbo].[DS_TABLE] as CS
LEFT JOIN
[DB_02].[dbo].[C_TABLE] as BCO on CS.[TEST_ID] = BCO.[TEST_ID]
LEFT JOIN
[DB_02].[dbo].[CR_TABLE] as FOO on BCO.[UID] = FOO.[UID]我在使用单个DB时测试了count，它工作得很好。但是当使用多个DB时，比如这里，它就不工作了。
如果我尝试计数，我会得到一个错误。
SELET CS.[TEST_ID], CS.[TESTGROUP_ID], count(BCO.[MAIN_ID]) as COUNT FROM [DB_01].[dbo].[DS_TABLE] as CS
LEFT JOIN
[DB_02].[dbo].[C_TABLE] as BCO on CS.[TEST_ID] = BCO.[TEST_ID]
LEFT JOIN
[DB_02].[dbo].[CR_TABLE] as FOO on BCO.[UID] = FOO.[UID]
Column 'DB01.dbo.DS_TABLE.test_id is invalid in the select list because it is not contained in either an aggregate function or the group by clause我甚至试过group_by group by BCO.[MAIN_ID]
Stack Overflow用户
回答已采纳
发布于 2020-03-26 18:42:51
您需要GROUP BY
SELECT CS.[TEST_ID], CS.[TESTGROUP_ID], count(BCO.[MAIN_ID]) as COUNT
FROM [DB_01].[dbo].[DS_TABLE] CS LEFT JOIN
[DB_02].[dbo].[C_TABLE] BCO
ON CS.[TEST_ID] = BCO.[TEST_ID] LEFT JOIN
[DB_02].[dbo].[CR_TABLE] as FOO
ON BCO.[UID] = FOO.[UID]
GROUP BY CS.[TEST_ID], CS.[TESTGROUP_ID];页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/60865391
复制相关文章
相似问题
腾讯云开发者
