获取子表中的记录数 [英] Get number of records in child table

问题描述

我需要获取子表中的记录数.

I need to get number of record in child table.

父表

+-------------+------------+  
| Id          | Name       |  
+-------------+------------+  
| 1           | a          |  
+-------------+------------+    
| 2           | b          |    
+-------------+------------+ 
| 3           | c          | 
+-------------+------------+ 

子表:

+-------------+------------+  
| Id          | Code       |  
+-------------+------------+
| 1           | d1         |  
+-------------+------------+ 
| 1           | d2         | 
+-------------+------------+    
| 1           | d3         |  
+-------------+------------+ 
| 1           | d1         | 
+-------------+------------+    
| 2           | d2         |  
+-------------+------------+ 
| 2           | d3         | 
+-------------+------------+ 
| 3           | d1         |  
+-------------+------------+ 
| 3           | d2         | 
+-------+-----+------------+   

结果应该是:

+-------------+------------+------------+ 
| Id          | Name       | NumOfRec   |  
+-------------+------------+------------+ 
| 1           | a          |        4   |
+-------------+------------+------------+  
| 2           | b          |        2   |
+-------------+------------+------------+  
| 3           | c          |        2   |
+-------------+------------+ -----------+

我已经尝试过使用 COUNT，但是我只得到了一个 ID 的记录数，而没有得到其他 ID 的记录数.

I've tried with COUNT but then I get only number of records for one id but not for other ID's.

推荐答案

应该这样做:

SELECT ID, COUNT(ID) AS RECCOUNT FROM CHILD_TABLE
GROUP BY ID;

要根据 PARENT ID 获取记录，您可以这样做，

To get the records based on PARENT ID, you can do,

SELECT P.ID, P.NAME, COUNT(C.ID) FROM
PARENT P LEFT OUTER JOIN CHILD C
ON P.ID = C.ID
GROUP BY P.ID, P.NAME;

SQL FIDDLE HERE:http://sqlfiddle.com/#!8/08d58/3

对于没有对应子记录的父 ID，左外连接导致记录数为 0.

Left outer join results in number of records as 0 for parent ids not having corresponding child records.

这篇关于获取子表中的记录数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！