Access SQL多AND条件查询无结果，需获取同一化合物双分析符合条件数据

你遇到的核心问题是单条记录不可能同时属于两个不同的AnalysisType，所以直接用AND连接两个AnalysisType的条件会返回空结果，而OR会包含只满足其中一个条件的记录。要找到同时满足两种条件的样本，需要从「样本维度」判断该样本是否存在两种符合要求的分析记录，以下是两种可行的解决方案：

方法1：使用EXISTS子查询 #

通过子查询验证同一个样本是否存在另一种符合条件的分析记录，适合需要返回详细分析数据的场景：

SELECT 
    s.SampleID, s.SampleName,
    c.CompoundName,
    a1.AnalysisMean AS AnalysisMean_Type2,
    at1.AnalysisTypeName AS AnalysisType_Name2
FROM Sample s
INNER JOIN Analysis a1 ON s.SampleID = a1.SampleID
INNER JOIN Compound c ON a1.CompoundID = c.CompoundID
INNER JOIN AnalysisType at1 ON a1.AnalysisTypeID = at1.AnalysisTypeID
WHERE 
    a1.CompoundID = 6
    AND a1.AnalysisTypeID = 2
    AND a1.AnalysisMean BETWEEN 100 AND 200
    AND EXISTS (
        SELECT 1
        FROM Analysis a2
        WHERE 
            a2.SampleID = s.SampleID
            AND a2.CompoundID = 6
            AND a2.AnalysisTypeID = 6
            AND a2.AnalysisMean BETWEEN 200 AND 400
    )

方法2：使用分组+HAVING子句 #

先筛选出符合任一条件的记录，再通过分组统计确保样本同时包含两种AnalysisType，适合只需要获取样本列表的场景：

SELECT 
    s.SampleID, s.SampleName,
    c.CompoundName
FROM Sample s
INNER JOIN Analysis a ON s.SampleID = a.SampleID
INNER JOIN Compound c ON a.CompoundID = c.CompoundID
INNER JOIN AnalysisType at ON a.AnalysisTypeID = at.AnalysisTypeID
WHERE 
    a.CompoundID = 6
    AND (
        (a.AnalysisTypeID = 2 AND a.AnalysisMean BETWEEN 100 AND 200)
        OR (a.AnalysisTypeID = 6 AND a.AnalysisMean BETWEEN 200 AND 400)
    )
GROUP BY s.SampleID, s.SampleName, c.CompoundName
HAVING COUNT(DISTINCT a.AnalysisTypeID) = 2

用R的dplyr实现（适配你的统计分析工作流） #

如果已经将数据导入R，也可以用tidyverse工具链直接处理，逻辑和SQL分组方法一致：

library(dplyr)

# 先关联所有表（假设表名和字段名与SQL一致）
combined_data <- Analysis %>%
  inner_join(Sample, by = "SampleID") %>%
  inner_join(Compound, by = "CompoundID") %>%
  inner_join(AnalysisType, by = "AnalysisTypeID")

# 筛选并验证同时满足两种条件的样本
result <- combined_data %>%
  filter(
    CompoundID == 6,
    (AnalysisTypeID == 2 & AnalysisMean >= 100 & AnalysisMean <= 200) |
    (AnalysisTypeID == 6 & AnalysisMean >= 200 & AnalysisMean <= 400)
  ) %>%
  group_by(SampleID, SampleName, CompoundName) %>%
  filter(n_distinct(AnalysisTypeID) == 2) %>%
  ungroup()

内容的提问来源于stack exchange，提问作者user2299029