問題描述

我正在嘗試獲取兩個(gè)不同表的資源列的不同計(jì)數(shù)，然后顯示每個(gè)項(xiàng)目 ID 的比較.現(xiàn)在，此查詢?yōu)槲姨峁┝藘蓚€(gè)表的相同計(jì)數(shù)值.

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

推薦答案

當(dāng)然你會(huì)得到同樣的計(jì)數(shù)，你正在計(jì)算同一個(gè)表的列(它是由一個(gè)連接產(chǎn)生的，授予，但它仍然是一個(gè)長方形的桌子).

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).

您想要做的是使用子查詢.首先獲取每個(gè)項(xiàng)目 id 的列表(從一個(gè)表中，或解析兩個(gè)相關(guān)表的聯(lián)合，但這是數(shù)據(jù)庫規(guī)范化不良的標(biāo)志)，然后獨(dú)立查詢這些表的計(jì)數(shù):

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

這篇關(guān)于SQL:計(jì)算兩個(gè)不同表中的兩個(gè)不同列的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，也希望大家多多支持html5模板網(wǎng)！