oracle如何从 count 函数删除空值

标签: sql Oracle
发布时间: 2017/4/15 19:03:06
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有毛病我我计数函数的结果,我认为其应有到 count(a1_journal.publisher_id) > 然而 = 3 而不是删除的结果,是 < 3 从显示它只是给了他们一个 null 值。

有什么办法,要么从显示中删除空值 (我试过在哪里上校不是空值,但它不能正常工作) 或阻止他们被选在计数?

SELECT publisher_name, (select count(a1_journal.publisher_id)
FROM a1_journal
WHERE a1_journal.publisher_id=a1_publisher.publisher_id 
HAVING count(a1_journal.publisher_id) >=3)
AS Number_of_Journals
FROM a1_publisher
ORDER by Number_of_Journals DESC;

谢谢你

解决方法 1:

问题是你在表 a1_publisher 中返回的所有行。试试这个。

select j.publisher_id, count(j.publisher_id)
FROM a1_journal j inner join a1_publisher p ON  j.publisher_id=p.publisher_id 
GROUP BY j.publisher_id
HAVING count(j.publisher_id) >=3
ORDER BY count(j.publisher_id) DESC

更新︰

若要选择发布服务器的名称有两种方式。

  1. 如果发布服务器的名称是唯一的可以自己添加的列,方法是︰ 按这样的分组

    select j.publisher_id,p.publisher_name, count(j.publisher_id)
    FROM a1_journal j 
      inner join a1_publisher p ON  j.publisher_id=p.publisher_id 
    GROUP BY j.publisher_id, p.publisher_name
    HAVING count(j.publisher_id) >=3
    ORDER BY count(j.publisher_id) DESC
    
  2. 如果它不是唯一的你应该有这样的 a1_publisher 与另一个连接。

    SELECT aj.publisher_id, aj.numberOfJournals, ap.publisher_name
    FROM a1_publisher ap 
    INNER JOIN (
        SELECT j.publisher_id, count(j.publisher_id) numberOfJournals
        FROM a1_journal j 
           inner join a1_publisher p ON  j.publisher_id=p.publisher_id 
        GROUP BY j.publisher_id
        HAVING count(j.publisher_id) >=3  ) aj 
    ON ap.publisher_id = ap.publisher_id
    ORDER BY count(j.publisher_id) DESC
    
赞助商