node.js - 如果子查询结果为空，我不希望我的查询返回结果

Question

当我查询不存在的 ID 时，我的查询（子查询）返回一个所有字段为空的对象。理想情况下，结果将是 SQLite 的普通空数组，就像查询返回 0 行的典型实例一样。我认为问题在于我在子查询中创建了新列，即使它们的所有值都为空，外部查询也会保留这些列。有趣的是，如果我在外部查询中插入“COUNT(*)”，计数会显示 1 行：每一列都有一行空值。对此查询的长度表示歉意：

SELECT
  *,
  JSON_GROUP_ARRAY(JSON_OBJECT(
    'American Indian',
    CASE WHEN num_allegations > 4 THEN ROUND(american_indian * 1.0 / 
    num_allegations * 100.0, 2) END,
    'Asian',
    CASE WHEN num_allegations > 4 THEN. ROUND(asian * 1.0 / num_allegations * 
    100.0, 2) END,
    'Black',
    CASE WHEN num_allegations > 4 THEN
    ROUND(black * 1.0 / num_allegations * 100.0, 2) END,
    'Hispanic',
    CASE WHEN num_allegations > 4 THEN
    ROUND(hispanic * 1.0 / num_allegations * 100.0, 2) END,
    'White',
    CASE WHEN num_allegations > 4 THEN
    ROUND(white * 1.0 / num_allegations * 100.0, 2) END,
    'Other Ethnicity',
    CASE WHEN num_allegations > 4 THEN
    ROUND(other_ethnicity * 1.0 / num_allegations * 100.0, 2) END,
    'Ethnicity Unknown',
    CASE WHEN num_allegations > 4 THEN
    ROUND(ethnicity_unknown * 1.0 / num_allegations * 100.0, 2) END
    )) AS race_percentages,
  JSON_GROUP_ARRAY(JSON_OBJECT(
    'Female',
    CASE WHEN num_allegations > 4 THEN
    ROUND(female * 1.0 / num_allegations * 100.0, 2) END,
    'Male',
    CASE WHEN num_allegations > 4 THEN
    ROUND(male * 1.0 / num_allegations * 100.0, 2) END,
    'Female (trans)',
    CASE WHEN num_allegations > 4 THEN
    ROUND(trans_female * 1.0 / num_allegations * 100.0, 2) END,
    'Male (trans)',
    CASE WHEN num_allegations > 4 THEN
    ROUND(trans_male * 1.0 / num_allegations * 100.0, 2) END,
    'Gender-nonconforming',
    CASE WHEN num_allegations > 4 THEN
    ROUND(gender_non_conforming * 1.0 / num_allegations * 100.0, 2) END,
    'Unknown/refused',
    CASE WHEN num_allegations > 4 THEN
    ROUND(gender_unknown * 1.0 / num_allegations * 100.0, 2) END
    )) AS gender_percentages
    FROM (
      SELECT 
        cops.*,
        cmd_units.id as command_unit_id,
        CASE WHEN COUNT(allegations.id) > 9
          THEN (
            ROUND(COUNT(CASE WHEN allegations.board_disposition LIKE 'Substantiated%' THEN 1 END)*1.0 / COUNT(allegations.id) * 100.0, 2))
            END substantiated_percentage,
            COUNT(*) AS num_allegations,
            COUNT(CASE WHEN allegations.board_disposition LIKE 'Substantiated%' THEN 1 END) AS num_substantiated,
            COUNT(DISTINCT CASE WHEN allegations.complainant_ethnicity LIKE '%indian%' THEN allegations.id END) AS american_indian,
            COUNT(DISTINCT CASE WHEN allegations.complainant_ethnicity LIKE '%asian%' THEN allegations.id END) AS asian,
            COUNT(DISTINCT CASE WHEN allegations.complainant_ethnicity LIKE '%black%' THEN allegations.id END) AS black,
            COUNT(DISTINCT CASE WHEN allegations.complainant_ethnicity LIKE '%hispanic%' THEN allegations.id END) AS hispanic,
            COUNT(DISTINCT CASE WHEN allegations.complainant_ethnicity LIKE '%white%' THEN allegations.id END) AS white,
            COUNT(DISTINCT CASE WHEN allegations.complainant_ethnicity LIKE 'Other Race' THEN allegations.id END) AS other_ethnicity,
            COUNT(DISTINCT CASE WHEN allegations.complainant_ethnicity LIKE '' THEN allegations.id END) AS ethnicity_unknown,
            COUNT(DISTINCT CASE WHEN allegations.complainant_gender LIKE 'male%' THEN allegations.id END) AS male,
            COUNT(DISTINCT CASE WHEN allegations.complainant_gender LIKE '%female%' THEN allegations.id END) AS female,
            COUNT(DISTINCT CASE WHEN allegations.complainant_gender LIKE '%Gender non-conforming%' THEN allegations.id END) AS gender_non_conforming,
            COUNT(DISTINCT CASE WHEN allegations.complainant_gender LIKE '%Transman%' THEN allegations.id END) AS trans_male,
            COUNT(DISTINCT CASE WHEN allegations.complainant_gender LIKE '%Transwoman%' THEN allegations.id END) AS trans_female,
            COUNT(DISTINCT CASE WHEN allegations.complainant_gender LIKE '' THEN allegations.id END) AS gender_unknown,
            COUNT(DISTINCT complaints.id) AS num_complaints
            FROM 
              cops 
            JOIN 
              allegations 
            ON 
              cops.id = allegations.cop
            JOIN
              complaints
            ON 
              complaints.id = allegations.complaint_id 
            JOIN
              command_units cmd_units
            ON
              cops.command_unit = cmd_units.unit_id
            WHERE
              cops.id = (?)
            )
          WHERE id IS NOT NULL

它返回一个错误的 id：

{
id: null,
first_name: null,
last_name: null,
command_unit: null,
precinct: null,
shield_no: null,
rank_abbrev: null,
rank_full: null,
ethnicity: null,
gender: null,
command_unit_full: null,
command_unit_id: null,
substantiated_percentage: null,
num_allegations: 0,
num_substantiated: 0,
american_indian: 0,
asian: 0,
black: 0,
hispanic: 0,
white: 0,
other_ethnicity: 0,
ethnicity_unknown: 0,
male: 0,
female: 0,
gender_non_conforming: 0,
trans_male: 0,
trans_female: 0,
gender_unknown: 0,
num_complaints: 0,
race_percentages: {
American Indian: null,
Asian: null,
Black: null,
Hispanic: null,
White: null,
Other Ethnicity: null,
Ethnicity Unknown: null
},
gender_percentages: {
Female: null,
Male: null,
Female (trans): null,
Male (trans): null,
Gender-nonconforming: null,
Unknown/refused: null
}
}

标签： node.jssqlite