为什么 SQL 查询只返回 'WHERE' 'IN' 子句中单个项目的记录？-解网

问：

为什么此查询：

SELECT
        dr.facility_id,
        ds.sys_sample_code,
        ds.sys_loc_code,
        ds.matrix_code,
        ds.sample_type_code,
        ds.start_depth,
        ds.end_depth,
        ds.sample_date,
        ds.task_code,
        dt.prep_date,
        dt.analysis_date,
        dt.lab_name_code,
        dt.lab_sdg,
        dt.lab_sample_id,
        dt.analytic_method,
        dt.prep_method,
        dr.cas_rn,
        dr.custom_field_1 as chemical_name, 
        dr.detect_flag,
        dr.result_numeric as result_value_rdl,
        dr.result_numeric as result_value_mdl,
        dr.result_numeric as result_text_rdl,
        dr.result_numeric as result_text_mdl,
        dr.validator_qualifiers as final_qualifier,
        dr.lab_qualifiers,
        dr.result_unit,
        dr.method_detection_limit,
        dr.quantitation_limit,
        dr.reporting_detection_limit,
        dr.detection_limit_unit,
        dr.reportable_result,
        dr.result_type_code,
        dt.percent_moisture,
        dt.fraction, dt.basis,
        dt.dilution_factor,
        dr.custom_field_2 as validation_date,
        dr.dqm_remark as validation_comment,
        dr.custom_field_3 as validator_name,
        dr.validated_yn, 
        NULL as result_comment,
        dr.approval_code,
        dc.x_coord, dc.y_coord,
        'Groundwater' AS matrix_name
    FROM [dbo].dt_sample ds
    INNER JOIN [dbo].dt_test dt ON dt.sample_id = ds.sample_id
    INNER JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
    LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
    WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
    AND dt.facility_id = 928061
) subq;

在以下情况下仅返回“X3B0341”的记录：

SELECT dt.lab_sdg, COUNT(*)
FROM [dbo].dt_test dt
WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
--AND dt.facility_id = 928061
GROUP BY dt.lab_sdg;

给出此输出（显然数据可用性不是问题）：

X3B0341 408
X3C0002 239
X3D0121 438
X3E0289 673
X3E0336 303

我已经厌倦了更改以选择更少的值，但无济于事。我可以使用一个巨大的脚本获得所需的结果，每个脚本都有乘数并集，但我想避免这种情况。非常感谢任何和所有的帮助。WHEREWHERE dt.lab_sdg = 'X3D0121' OR dt.lab_sdg = 'X3E0336'dt.lab_sdg

sql 联接 where-clause in-clause

SELECT *
    FROM [dbo].dt_sample ds
    INNER JOIN [dbo].dt_test dt ON dt.sample_id = ds.sample_id
    --INNER JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
    --LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
    WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
    AND dt.facility_id = 928061;

还要检查没有 where 子句，您是否获得“X3B0341”以外的数据。

SELECT *
    FROM [dbo].dt_sample ds
    INNER JOIN [dbo].dt_test dt ON dt.sample_id = ds.sample_id
    INNER JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
    LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
    WHERE 
    --dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
    dt.facility_id = 928061;

0赞 Adrian Maxwell 11/15/2023 #2

更改表格的顺序，然后向左加入其余表格。其他表不得具有与所需列表匹配的相关值：例如：

SELECT
      dt.lab_sdg
    , dr.facility_id
    , ds.sys_sample_code
    , ds.sys_loc_code
    -- more as needed
FROM  [dbo].dt_test dt
LEFT JOIN [dbo].dt_sample ds ON dt.sample_id = ds.sample_id
LEFT JOIN [dbo].dt_result dr ON dr.test_id = dt.test_id
LEFT JOIN [dbo].dt_coordinate dc ON ds.sys_loc_code = dc.sys_loc_code
WHERE dt.lab_sdg IN ('X3A0217', 'X3B0341', 'X3C0002', 'X3D0121', 'X3E0289', 'X3E0336')
    AND dt.facility_id = 928061

上一个：在 df1 和 df2 之间执行left_join，其中 df2 给出了 df1 中连接变量的范围

下一个：根据另一个数据集（DF2）中的变量在 DF1 中保留（过滤）观测值

为什么 SQL 查询只返回 'WHERE' 'IN' 子句中单个项目的记录？

Why is SQL query only returning records for a single item in 'WHERE' 'IN' clause?

评论