对于一个表的每一行,计数另一个表中指向Oracle中每个行的条目 [英] For each row of one table, count entries in another table pointing to each of those rows in Oracle
问题描述
我有以下表格,
<$ (19)
BRANCH_ID NUMBER(8)
REF_TYP_CD VARCHAR2(20)
- table = kms_fil_nm_t
DOC_ID VARCHAR2(19)PRIMARY KEY UNIQUE
例如,我可以计算所有的 kms_doc_ref_currnt_v
记录,其中 to_doc_id = 59678
,其中59678是 kms_fil_nm_t
,使用此查询,
select'doc_id 59678 has'| | count(to_doc_id)作为来自kms_doc_ref_currnt_v的cnt,其中branch_id = 1且(('CONREF','KBA')和to_doc_id = 59678中的ref_typ_cd;
kms_doc_ref_currnt_v.to_doc_id
是一个字段,其中一个 kms_fil_nm_t.doc_id
值。 kms_doc_ref_currnt_v.doc_id
也是 kms_fil_nm_t
中的一个值。
我正在查找的单个查询将遍历每个 kms_fil_nm_t.doc_id
并计算 kms_doc_ref_currnt_v
有类似的 to_doc_id
。返回的每一行看起来都像上面查询的输出。以下是输出示例,
doc_id 1有32
doc_id 2有314
doc_id 3有2718
doc_id 4有42
doc_id 5有128
doc_id 6有11235
。
。
。
可能很简单,但我无法弄清楚。
执行与两个表的连接并按如下所示添加 GROUP BY
子句:
SELECT'doc_id 59678 has'|| count(to_doc_id)as cnt
FROM kms_doc_ref_currnt_v kv,kms_fil_nm_t kt
WHERE kt.doc_id = kv.to_doc_id
AND kv.branch_id = 1
AND kv.ref_typ_cd in('CONREF ','KBA')
AND kv.to_doc_id = 59678
GROUP BY kv.to_doc_id;
编辑:
要从 kms_doc_ref_currnt_v
中获取所有记录,而不管它们在 kms_fil_nm_t
和 kv中的参考可用性.to_doc_id = 59678
,如下所示:
SELECT'doc_id 59678 has'||。 count(to_doc_id)as cnt
FROM kms_doc_ref_currnt_v kv
LEFT JOIN kms_fil_nm_t kt
ON(kt.doc_id = kv.to_doc_id)
WHERE kv.branch_id = 1
AND ('CONREF','KBA')
GROUP BY kv.to_doc_id中的kv.ref_typ_cd;
取代硬编码 59678
,您可以想要写:
SELECT'doc_id'|| kt.doc_id || `有'|| count(to_doc_id)as cnt
FROM kms_doc_ref_currnt_v kv
LEFT JOIN kms_fil_nm_t kt
ON(kt.doc_id = kv.to_doc_id)
WHERE kv.branch_id = 1
AND ('CONREF','KBA')
GROUP BY kv.to_doc_id,kt.doc_id中的kv.ref_typ_cd;
Not sure the title explains the problem well; this is what I'm working with,
I have the following tables,
-- table = kms_doc_ref_currnt_v
DOC_ID VARCHAR2(19)
TO_DOC_ID VARCHAR2(19)
BRANCH_ID NUMBER(8)
REF_TYP_CD VARCHAR2(20)
-- table = kms_fil_nm_t
DOC_ID VARCHAR2(19) PRIMARY KEY UNIQUE
For example, I can get a count of all kms_doc_ref_currnt_v
records that have a to_doc_id = 59678
, where 59678 is one value in kms_fil_nm_t
, with this query,
select 'doc_id 59678 has ' || count(to_doc_id) as cnt from kms_doc_ref_currnt_v where branch_id=1 and ref_typ_cd in ('CONREF', 'KBA') and to_doc_id=59678;
kms_doc_ref_currnt_v.to_doc_id
is a field that has one of the kms_fil_nm_t.doc_id
values. kms_doc_ref_currnt_v.doc_id
is also one of the values in kms_fil_nm_t
.
The single query I'm looking for would loop over each kms_fil_nm_t.doc_id
and count all the rows in kms_doc_ref_currnt_v
that have a similar to_doc_id
. Each row returned would look like the output of the query above. Here's example output,
doc_id 1 has 32
doc_id 2 has 314
doc_id 3 has 2718
doc_id 4 has 42
doc_id 5 has 128
doc_id 6 has 11235
.
.
.
Probably simple but I just can't figure it out.
Do a join with two tables and add a GROUP BY
clause as below:
SELECT 'doc_id 59678 has ' || count(to_doc_id) as cnt
FROM kms_doc_ref_currnt_v kv, kms_fil_nm_t kt
WHERE kt.doc_id= kv.to_doc_id
AND kv.branch_id=1
AND kv.ref_typ_cd in ('CONREF', 'KBA')
AND kv.to_doc_id=59678
GROUP BY kv.to_doc_id;
EDIT:
To get all records from kms_doc_ref_currnt_v
irrespective of their reference availability in kms_fil_nm_t
and kv.to_doc_id=59678
, do like this:
SELECT 'doc_id 59678 has ' || count(to_doc_id) as cnt
FROM kms_doc_ref_currnt_v kv
LEFT JOIN kms_fil_nm_t kt
ON (kt.doc_id= kv.to_doc_id )
WHERE kv.branch_id=1
AND kv.ref_typ_cd in ('CONREF', 'KBA')
GROUP BY kv.to_doc_id;
to replace the hardcoding 59678
, you may want to write:
SELECT 'doc_id ' || kt.doc_id || ` has ' || count(to_doc_id) as cnt
FROM kms_doc_ref_currnt_v kv
LEFT JOIN kms_fil_nm_t kt
ON (kt.doc_id= kv.to_doc_id )
WHERE kv.branch_id=1
AND kv.ref_typ_cd in ('CONREF', 'KBA')
GROUP BY kv.to_doc_id, kt.doc_id;
这篇关于对于一个表的每一行,计数另一个表中指向Oracle中每个行的条目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!