时间戳

首页 > 解决方案 > INNER JOIN 多个数据库表中的 SQL 计数

sql - INNER JOIN 多个数据库表中的 SQL 计数

问题描述

以下查询返回几行，但我想对 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]

我在使用单个数据库时测试了计数，它工作正常。但是当使用像这里这样的多个数据库时它不起作用。

如果我尝试计数，则会出现错误。

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_bygroup by BCO.[MAIN_ID]

标签： sqlsql-server

解决方案

你需要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];

推荐阅读