SQL Count次数值出现在另一个表的特定列中

Ok here is my problem I am trying to create a view that counts how many times a particular value is used in another table.

好的,这是我的问题我正在尝试创建一个视图,计算在另一个表中使用特定值的次数。

My View is like this

我的观点是这样的

SELECT 
table1.id AS code_id,
table2.asset_standard_id,
       Count(table2.pkey) AS COUNT
FROM table1  LEFT JOIN table2 
ON table2.code_id = table1.Id
where table1.code_type = (select pkey from imtbl_code_type where imtbl_code_type.id = 'A-Problem') and table2.asset_standard_id = '25209-45MEO'
GROUP BY table1.id,
table2.asset_standard_id
order by count

and this returns the count, but my problem is I also want to show zeros as well.

这会返回计数,但我的问题是我也想显示零。

I do not think it is possible, but any help would be appreciated, I am at a loss.

我不认为这是可能的,但任何帮助都会受到赞赏,我不知所措。

Thanks!

Update (thanks for input :) ) This is a view that will be called by passing in the asset_standard_id, so I am not sure if I can do a On there.

更新(感谢输入:))这是一个将通过传入asset_standard_id来调用的视图,所以我不确定我是否可以在那里做一个。

Here is some sample for it the client will enter in an asset_standard_id of 25209-45MEO through a c# interface into Active Reports. this view will then execute to return all occurances that are found, plus ones that are not.

下面是一些示例,客户端将通过c#接口在asset报告_idard_id 25209-45MEO中输入Active Reports。然后,该视图将执行以返回找到的所有发生,以及不发现的发生。

Table 1                        ||   table 2
---------------------------------
Code  | asset_standard_id       ||  Code
----------------------------------------------
c1      25209-45MEO            ||  c1
c3      25209-45MEO            ||  c2
c3      25209-45MEO            ||  c3

And what I would love to see in the results are:
code_id || asset_standard_id || count
c3           25209-45MEO         2
c1           25209-45MEO         1
c2           25209-45MEO         0

1 个解决方案

#1

The condition on the second table needs to be in the on clause:

第二个表上的条件需要在on子句中:

select t1.id AS code_id, t2.asset_standard_id,
       Count(t2.pkey) as cnt
from table1 t1 left join
     table2 t2
     on t2.code_id = t1.Id and
        t2.asset_standard_id = '25209-45MEO'
where t1.code_type = (select ict.pkey from imtbl_code_type ict where ict.id = 'A-Problem')
group by t1.id, t2.asset_standard_id
order by cnt;

Note that I changed the query to use table aliases. This makes the query easier to write and to read.

请注意,我更改了查询以使用表别名。这使查询更容易编写和读取。

#1