mysql - 存储过程在可选参数上返回空结果集

Question

当我传递查询中所需的所有值以返回正确的结果时。但是当我只传递 ID 时，我得到一个空结果集。在这种情况下，不应该忽略类型并仍然返回正确的值吗？

您能帮我解决这个问题并指出查询中的问题吗？

SELECT 'elementary_school' AS type, elementary_school AS obj, COUNT(*) AS count
FROM data
WHERE data.report_id = ReportId         
AND (data.book_section = type 
     OR data.book_section IS NULL
     OR data.book_section = ''
    )
GROUP BY elementary_school


UNION

SELECT 'middle_school' AS type, middle_school AS obj, COUNT(*) AS count
FROM data
WHERE data.report_id = ReportId     
AND (data.book_section = type 
     OR data.book_section IS NULL
     OR data.book_section = ''
    )
 GROUP BY  middle_school


UNION

SELECT 'high_school' AS type, high_school AS obj, COUNT(*) AS count
FROM data
WHERE data.report_id = ReportId     
AND (data.book_section = type 
     OR data.book_section IS NULL
     OR data.book_section = ''
    )
GROUP BY high_school     



UNION

SELECT 'lot_sqft' AS type, lot_sqft AS obj, COUNT(*) AS count
FROM data
WHERE data.report_id = ReportId     
AND (data.book_section = type 
     OR data.book_section IS NULL
     OR data.book_section = ''
    )
GROUP BY lot_sqft       

ORDER BY count, type

Answer 1

您可以简化查询:

SELECT t.type,
        (CASE WHEN t.type = 'elementary_school' THEN elementary_school 
              WHEN t.type = 'middle_school' THEN middle_school 
              WHEN t.type = 'high_school' THEN high_school 
              WHEN t.type = 'lot_sqft' THEN lot_sqft 

         END) AS obj, COUNT(*) AS count
FROM data d CROSS JOIN
     (SELECT 'elementary_school' AS type UNION ALL
      SELECT 'middle_school' AS type UNION ALL
      SELECT 'high_school' AS type UNION ALL
      SELECT 'lot_sqft' AS type
     ) t
WHERE d.report_id = @ReportId AND
      (d.book_section = @type OR 
       d.book_section IS NULL OR
       d.book_section = ''
      )
GROUP BY t.type. obj;

我使用 @ 突出显示了看似“参数”的内容，以澄清它们是参数而不是表中的列。

如果您想检查 NULL 值，请将逻辑更改为:

WHERE (d.report_id = @ReportId OR @ReportId IS NULL) AND
      (d.book_section = @type OR 
       d.book_section IS NULL OR
       d.book_section = ''
      )