SQL:计算两个不同表中的两个不同列

2021-09-10 00:00:00 sql tsql sql-server

我正在尝试获取两个不同表的资源列的不同计数，然后显示每个项目 ID 的比较.现在，此查询为我提供了两个表的相同计数值.

I am trying to get the distinct counts for the resource column of two different tables, then show the comparison for each project ID. Right now, this query gives me the same count values for both tables.

select 
    t1.PRJCT_ID,
    count(t1.RSRC_ID) as TBL1_RSRC_CNT,
    t2.PRJCT_ID,
    count(t2.RSRC_ID) as TBL2_RSRC_CNT
from
    DATA_TABLE_1 t1
LEFT OUTER JOIN 
    DATA_TABLE_2 t2 on t1.PRJCT_ID = t2.PRJCT_ID
GROUP BY
    t1.PRJCT_ID, t2.PRJCT_ID
order by 1

推荐答案

当然你会得到同样的计数，你正在计算同一个表的列(它是由一个连接产生的，授予，但它仍然是一个长方形的桌子).

Of course you're going to get the same count like that, you're counting the columns of the same table (which is made by a join, granted, but it's still a rectangular table).

您想要做的是使用子查询.首先获取每个项目 id 的列表(从一个表中，或解析两个相关表的联合，但这是数据库规范化不良的标志)，然后独立查询这些表的计数:

What you want to do is use subqueries. First get a list of every project id (from a table, or an union of parsing both tables in question, but that's a sign of bad database normalization), then query the tables independently for their count:

select p.ID,
  (select count(*) from DATA_TABLE_1 t1 where t1.ID=p.ID) Count1,
  (select count(*) from DATA_TABLE_2 t2 where t2.ID=p.ID) Count2
from projects p

相关文章