假设我有一个表TableA
:
parent_id int
this_id int
filter int
this_date date
还有一些示例数据:
parent_id = 1, this_id = 1, filter = 1, this_date = ...
parent_id = 1, this_id = 2, filter = 0, this_date = ...
parent_id = 1, this_id = 3, filter = 1, this_date = ...
parent_id = 4, this_id = 4, filter = 0, this_date = ...
parent_id = 4, this_id = 5, filter = 0, this_date = ...
parent_id = 4, this_id = 6, filter = 1, this_date = ...
parent_id = null, this_id = 7, filter = 0, this_date = ...
parent_id = null, this_id = 8, filter = 1, this_date = ...
parent_id
总是与其中一个孩子的this_id
相同。如果没有子项,则parent_id
为空
桌子不能改变,这就是我们所拥有的。
我创建了一个视图来给我摘要:
SELECT ISNULL(parent_id,this_id) id, COUNT(*) numparts, MAX(this_date)...
FROM TableA
GROUP BY ISNULL(parent_id,this_id)
我想在我的摘要视图中添加具有filter=1
的零件数。
所以我对这个例子的结果(如果我做一个SELECT * FROM theview
)将是:
id numparts dt numOfFilter1
1 3 ... 2
4 3 ... 1
7 1 ... 0
8 1 ... 1
实际上,我的过滤器列将是一个子查询,但我认为一旦我对此位进行了排序,我就可以弄清楚这一点。
像这样尝试:
SELECT ISNULL(parent_id,this_id) id,
COUNT(*) numparts,
MAX(this_date),
SUM(CASE WHEN filter = 1 THEN 1 ELSE 0 END) numOfFilter1
FROM TableA
GROUP BY
ISNULL(parent_id,this_id)