firebase 如果SQL查询结果中没有找到记录,则应为0

iklwldmw  于 2023-06-07  发布在  其他
关注(0)|答案(3)|浏览(218)

我在这里得到了一年中每个月的计数。下面的查询返回如果在月份中找到记录,则显示。如果在特定月份没有找到记录,则不会显示。我需要如果没有找到任何记录计数应显示在特定月份
我正在使用Firebird SQL
下面是我的提问。我该怎么办?我是SQL新手

SELECT 
  CASE EXTRACT(MONTH FROM pd.Date)
    WHEN 1 THEN 'January'
    WHEN 2 THEN 'February'
    WHEN 3 THEN 'March'
    WHEN 4 THEN 'April'
    WHEN 5 THEN 'May'
    WHEN 6 THEN 'June'
    WHEN 7 THEN 'July'
    WHEN 8 THEN 'August'
    WHEN 9 THEN 'September'
    WHEN 10 THEN 'October'
    WHEN 11 THEN 'November'
    WHEN 12 THEN 'December'
    END AS "MONTH",
    Count(distinct pd.numbers) AS "Count"       
     FROM Power_D pd 
    WHERE EXTRACT(YEAR FROM pd.Date) = '2016' 
     AND pd.Name = 'Test' GROUP BY EXTRACT(MONTH FROM pd.Date)

对于下面的查询结果。

February    330
March       212
April       178
May         222
June        487
July        695
August      433
September   757
October     1307
November    321
December    143

我需要像下面。

January     0
February    330
March       212
April       178
May         222
June        487
July        695
August      433
September   757
October     1307
November    321
December    143
ao218c7q

ao218c7q1#

使用SQL COALESCE:
COALESCE(Count(distinct pd.numbers),0)AS“Count”

omqzjyyz

omqzjyyz2#

解决这个问题的唯一方法是遵循@JNevill的建议-这是一个常见的问题,解决方案是一个标准模式。此外,可能有一个标准函数会给予你月份名称,而不是数字,所以你不需要CASE语句--但是因为你没有用你正在使用的DBMS标记你的问题,所以不可能知道。
无论如何,类似以下的东西应该可以工作-您可能需要针对特定的DBMS进行调整:

with month_list(month_name) as
        select *
        from values
        ('January'),
        ('February'),
        ('March'),
        ('April'),
        ('May'),
        ('June'),
        ('July'),
        ('August'),
        ('September'),
        ('October'),
        ('November'),
        ('December')
    )
    SELECT 
    ml.month_name
    Count(distinct pd.numbers) AS "Count"       
    FROM month_limit ml
    LEFT OUTER JOIN Power_D pd on ml.month_name = TO_CHAR(pd.Date,'MMMM')
    AND EXTRACT(YEAR FROM pd.Date) = '2016' 
    AND pd.Name = 'Test' 
    GROUP BY  ml.month_name
    ;
aoyhnmkz

aoyhnmkz3#

这是基于@JNevill评论的MySQL/MariaDb的可能解决方案

SELECT m.month_name, count(distinct pd.numbers) AS "Count"
FROM 
(
    SELECT 1 AS month, 'January' as month_name
    UNION ALL SELECT 2, 'February'
    UNION ALL SELECT 3, 'March'
    UNION ALL SELECT 4, 'April'
    UNION ALL SELECT 5, 'May'
    UNION ALL SELECT 6, 'June'
    UNION ALL SELECT 7, 'July'
    UNION ALL SELECT 8, 'August'
    UNION ALL SELECT 9, 'September'
    UNION ALL SELECT 10, 'October'
    UNION ALL SELECT 11, 'November'
    UNION ALL SELECT 12, 'December'
    
) m

LEFT JOIN 

(
    SELECT *    
    FROM Power_D 
    WHERE EXTRACT(YEAR FROM Date) = '2016' 
      AND Name = 'Test' 
      
) pd ON EXTRACT(MONTH FROM pd.Date) = m.month

GROUP BY m.month_name
ORDER BY m.month

相关问题