sql - 如何有效使用min()和max()？

Question

我有一个SQL查询，其中检查值是否在表格的最大值和最小值之间。我现在实现如下：

SELECT spectrum_id, feature_table_id
FROM 'spectrum', 'feature' 
WHERE `spectrum`.msrun_msrun_id = 1
AND `feature`.msrun_msrun_id = 1
AND (SELECT min(rt) FROM `convexhull` WHERE `convexhull`.feature_feature_table_id =  `feature`.feature_table_id) <= scan_start_time 
AND scan_start_time <= (SELECT max(rt) FROM `convexhull` WHERE 'convexhull'.feature_feature_table_id = 'feature'.feature_table_id)
AND (SELECT min(mz) FROM `convexhull` WHERE `convexhull`.feature_feature_table_id = `feature`.feature_table_id) <= base_peak_mz 
AND base_peak_mz <= (SELECT max(mz) FROM `convexhull` WHERE `convexhull`.feature_feature_table_id = `feature`feature_table_id)

这运行的非常缓慢，因为每次运行此查询时，我都会从凸包中选择4次，因此我尝试使用内部联接对其进行改进：

SELECT spectrum_id, feature_table_id 
FROM 'spectrum', 'feature'
INNER JOIN `convexhull` ON `convexhull`.feature_feature_table_id = `feature`.feature_table_id
WHERE `spectrum`.msrun_msrun_id = ? "+ 
AND `feature`.msrun_msrun_id = ? "+
AND min(`convexhull`.rt) <= scan_start_time "+
AND scan_start_time <= max(`convexhull`.rt) "+
AND min(`convexhull`.mz) <= base_peak_mz "+
AND base_peak_mz <= max(`convexhull`.mz)", spectrumFeature_InputValues)

但是，只能在select语句之后使用min（）和max（）语句。如何提高第一个查询的效率，以便不必进行4个查询就可以获得最小和最大rt和mz？

Answer 1

编辑：还有几分钟，然后再次查看，并意识到所有数据都来自该一张表，所以这样的事情应该工作

SELECT 
    spectrum_id
    ,feature_table_id
FROM 
    spectrum AS s
    INNER JOIN feature AS f
        on f.msrun_msrun_id = s.msrun_msrun_id
    INNER JOIN (select 
         feature_feature_table_id
         ,min(rt) AS rtMin
        ,max(rt) AS rtMax
        ,min(mz) AS mzMin
        ,max(mz) as mzMax
     FROM 
        convexhull
     GROUP BY 
         feature_feature_table_id
     ) AS t
     ON t.feature_feature_table_id = f.feature_table_id
WHERE
    s.msrun_msrun_id = 1
    AND s.scan_start_time >= t.rtMin
    AND s.scan_start_time <= t.rtMax
    AND base_peak_mz >= t.mxMin
    AND base_peak_mz <= t.mzMax

我认为您想从凸包表中进行选择，并按feature_feature_table_id进行分组，以获取该分组中的最小和最大rt。

然后，您可以将选择内容括在方括号中，为其命名（如t）并加入该名称。

希望这足以使您上路..如果没有在此处创建示例架构： http://sqlfiddle.com/

并输入您的查询，我可以对其进行修改。

作为附带说明，我想您希望将这些表连接到特定字段，而不是使用where子句从两者中进行比较：

SELECT spectrum_id, feature_table_id
FROM 'spectrum', 'feature' 
WHERE `spectrum`.msrun_msrun_id = 1
AND `feature`.msrun_msrun_id = 1

和：

SELECT 
    spectrum_id
    ,feature_table_id
FROM 
    spectrum AS s
    INNER JOIN feature AS f
        on f.msrun_msrun_id = s.msrun_msrun_id
WHERE
    s.msrun_msrun_id = 1

如果我做错了事，请告诉我。