問題描述
我正在嘗試獲取兩個不同表的資源列的不同計數，然后顯示每個項目 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
這篇關于SQL:計算兩個不同表中的兩個不同列的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！