MySQL >、< 和按组缺失

Question

我在 MySQL 中有两个表，我正在将它们与以下属性进行比较:

tbl_fac : facility_id, chemical_id, criteria
             10      , 25         , 50
             10      , 26         , 60
             10      , 27         , 60
             11      , 25         , 30
             11      , 27         , 31 
              etc...

tbl_samp: sample_id, chemical_id, result
            5     ,    25         , 51
            5     ,    26         , 61
            6     ,    25         , 51
            6     ,    26         , 61
            6     ,    27         , 500

              etc.... 

这些表通过chemical_id连接(多对多----呃)，有几千个facility_id，每个facility_id有几百个chemical_id。还有数千个sample_id，每个sample_id 都有数百个chemical_id。总而言之，tbl_fac 中有大约 500,000 条记录，tbl_samp 中有 1,000,000 多条记录。

我正在尝试从此数据集中提取三组sample_id:

第1组:任何sample_id，其中tbl_samp.result > tbl_fac.criteria(即结果超出标准)

第2组:任何sample_id，其中tbl_samp.result < tbl_fac.criteria，并且所有tbl_fac.chemical_id都存在于该sample_id中(即，结果小于标准，并且一切都在那里)

第3组:任何sample_id，其中tbl_samp.result < tbl_fac.criteria，但sample_id中缺少一个或多个tbl_fac.chemical_id(即，结果小于标准，但缺少某些内容)

问题如下:如何在一个查询中有效地获取所有三个组？

我已经尝试过:

select * 
from tbl_fac 
left join tbl_samp 
    on tbl_fac.chemical_id = tbl_samp.chemical_id

但这只会产生整个数据集(而不是单个样本)缺失的值。我有一个黑客查询工作，使用第三个表来连接 tbl_fac 和 tbl_samp，但它太丑了，我实际上不好意思发布它......

一如既往，非常感谢您对此的想法!

干杯，

乔什

编辑:理想情况下，我希望返回sample_id和Group——每个样本ID只有一个Group(我对数据的了解表明它们总是属于上述三个类别之一) )。

Answer 1

此答案假设 tbl_fac 中的 facility_id 和 chemical_id 存在唯一约束，并且 sample_id 存在唯一约束 和tbl_samp 中的chemical_id。我所做的就是一次一步地构建查询。这是否有效还有待观察。

第1组:任何sample_id，其中tbl_samp.result > tbl_fac.criteria(即结果超出标准)

SELECT tbl_samp.sample_id,
       'ResultsGreaterThanCriteria' AS samplegroup
FROM   tbl_fac
       INNER JOIN tbl_samp
         ON tbl_fac.chemical_id = tbl_samp.chemical_id
WHERE  tbl_samp.result > tbl_fac.criteria
GROUP  BY tbl_samp.sample_id

第2组:任何sample_id，其中tbl_samp.result < tbl_fac.criteria，并且所有tbl_fac.chemical_id都存在于该sample_id中(即，结果小于标准，并且一切都在那里)

SELECT tbl_samp.sample_id,
       'ResultLessThanCriteriaAndAllChems' AS samplegroup
FROM   tbl_fac
       INNER JOIN tbl_samp
         ON tbl_fac.chemical_id = tbl_samp.chemical_id
WHERE  tbl_samp.result < tbl_fac.criteria
       AND NOT EXISTS (SELECT *
                       FROM   tbl_fac tf
                              LEFT JOIN tbl_samp ts
                                ON tf.chemical_id = ts.chemical_id
                       WHERE  ts.chemical_id IS NULL
                              AND tbl_samp.sample_id = ts.sample_id)
GROUP  BY tbl_samp.sample_id

第3组:任何sample_id，其中tbl_samp.result < tbl_fac.criteria，但sample_id中缺少一个或多个tbl_fac.chemical_id(即，结果小于标准，但缺少某些内容)

SELECT tbl_samp.sample_id,
       'ResultsLessThanCriteriaWithMissingChems' AS samplegroup
FROM   tbl_fac
       INNER JOIN tbl_samp
         ON tbl_fac.chemical_id = tbl_samp.chemical_id
WHERE  tbl_samp.result < tbl_fac.criteria
       AND EXISTS (SELECT *
                   FROM   tbl_fac tf
                          LEFT JOIN tbl_samp ts
                            ON tf.chemical_id = ts.chemical_id
                   WHERE  ts.chemical_id IS NULL
                          AND tbl_samp.sample_id = ts.sample_id)
GROUP  BY tbl_samp.sample_id 

最后，将所有三个查询合并在一起并得到:

SELECT tbl_samp.sample_id,
       'ResultsGreaterThanCriteria' AS samplegroup
FROM   tbl_fac
       INNER JOIN tbl_samp
         ON tbl_fac.chemical_id = tbl_samp.chemical_id
WHERE  tbl_samp.result > tbl_fac.criteria
GROUP  BY tbl_samp.sample_id
UNION ALL
SELECT tbl_samp.sample_id,
       'ResultLessThanCriteriaAndAllChems' AS samplegroup
FROM   tbl_fac
       INNER JOIN tbl_samp
         ON tbl_fac.chemical_id = tbl_samp.chemical_id
WHERE  tbl_samp.result < tbl_fac.criteria
       AND NOT EXISTS (SELECT *
                       FROM   tbl_fac tf
                              LEFT JOIN tbl_samp ts
                                ON tf.chemical_id = ts.chemical_id
                       WHERE  ts.chemical_id IS NULL
                              AND tbl_samp.sample_id = ts.sample_id)
GROUP  BY tbl_samp.sample_id
UNION ALL
SELECT tbl_samp.sample_id,
       'ResultsLessThanCriteriaWithMissingChems' AS samplegroup
FROM   tbl_fac
       INNER JOIN tbl_samp
         ON tbl_fac.chemical_id = tbl_samp.chemical_id
WHERE  tbl_samp.result < tbl_fac.criteria
       AND EXISTS (SELECT *
                   FROM   tbl_fac tf
                          LEFT JOIN tbl_samp ts
                            ON tf.chemical_id = ts.chemical_id
                   WHERE  ts.chemical_id IS NULL
                          AND tbl_samp.sample_id = ts.sample_id)
GROUP  BY tbl_samp.sample_id