Question

我有一个父母/子女/子女/子女类型表关系,定义如下:

tableA: parentID description

tableB: childID parentId description

tableC: grandchildID childId description itemComplete

I need to write a query that will list all records in tableB for any given parentID, along with the total count of grandchild records and the total count of grandchild records completed (where itemComplete = true).

父母 ID将成为条款的一部分(摘自括号内为Id=x的表B)。我可以列举的问题是,如何从表格中得出计数。 C 因为这一计算取决于儿童生长的目前价值。

换言之,我需要某种怀疑:

select description,  
(select count (*) from tableC where childId = X) as Items,
(select count (*) from tableC where childId = X And itemComplete = true) as CompleteItems
from tableB where parentId=Y

如果X是表B中现有的儿童。我如何在我的分询问中逐个提及儿童,以便完成项目数目和项目数目?

Answer 1

使用分辨率是一种选择,但我倾向于JOIN,两种表格和使用。按分类采用<代码>CASE 报到总数的条款。

SELECT b.description
       , COUNT(*) AS Items
       , SUM(CASE WHEN c.itemComplete = true THEN 1 ELSE 0 END) AS CompleteItems
FROM   tableB b
       LEFT OUTER JOIN tableC c ON c.childid = b.childid
WHERE  b.parentID =  Y 
GROUP BY
       b.description

If you insist on using your original statement, all that was missing was the reference to the outer table tableB.childID

select description,  
(select count (*) from tableC where childId = tableB.childID) as Items,
(select count (*) from tableC where childId = tableB.childID And itemComplete = true) as CompleteItems
from tableB where parentId=Y

或调整

SELECT description
       ,  (SELECT COUNT(*) FROM tableC WHERE childId = tableB.childID) AS Items,
       ,  (SELECT COUNT(*) FROM tableC WHERE childId = tableB.childID AND itemComplete = true) AS CompleteItems
FROM   tableB 
WHERE  parentId=Y

友情链接