mysql - 具有多个条件的自连接

Question

我正在使用 mysql 来存储我的化学分析结果。并使用 html/php 过滤结果并生成查询以获取我想要的结果。现在事情对我来说变得复杂，我正在尝试自加入表以应用所有过滤器。

我的表格设计是这样的，有超过 50k 行。

+--------+---------+-------------+----------------+
| Column |  Type   |    Index    |                |
+--------+---------+-------------+----------------+
| RID    | int     | primary_key | auto increment |
| ID     | int     | index       |                |
| Type   | int     |             |                |
| Order  | int     |             |                |
| Num    | int     |             |                |
| Val1   | decimal |             |                |
| Val2   | decimal |             |                |
+--------+---------+-------------+----------------+

每个样本都有一个ID、不同的类型和订单。 Val1和Val2是Num类型分析的结果。目前有 42 个不同的 Num。

例如，如果我的过滤器是这样的，

Select Types (1,3,9)
Select ANum (0,5)
Type 1, Num 5 should be bigger than 10 In Val2
Type 1, Num 5, should be smaller than 30 In Val2
Type 3, Num 0, should be smaller than 8 In Val1

我正在使用该查询来生成结果。

SELECT analyse.* FROM analyse 
INNER JOIN 
(SELECT ID FROM analyse WHERE Type = 1 AND Num = 5 AND Val2 < 30) AS a1 ON a1.SID = analyse.SID 
INNER JOIN 
(SELECT SID FROM  WHERE Type = 1 AND Num = 5 AND Val2 > 10) AS a2 ON a2.SID = analyse.SID
INNER JOIN 
(SELECT SID FROM analyse WHERE Type = 3 AND Num = 0 AND Val1 > 8) AS a3 ON a2.SID = analyse.SID 
WHERE Type IN (1,3,9) AND Num IN (0,5) ORDER BY ID, Type, Order, Num ASC

+-----+------+-------+---------------+--------------+--------------+--------------+
| ID  | Type | Order | Val1[Num[0]]  | Val2[Num[0]] | Val1[Num[5]] | Val2[Num[5]] |
+-----+------+-------+---------------+--------------+--------------+--------------+
| ... | ...  | ...   | ...           | ...          | ...          | ...          |
| 118 | 1    | 1     | 10.9000       | 2.2083       | 3.5056       | 15.2627      |
| 118 | 1    | 2     | 9.5000        | 1.9246       | 2.0305       | 11.7049      |
| 118 | 1    | 3     | 7.9000        | 1.6005       | 2.4274       | 16.6597      |
| 118 | 2    | 1     | 10.9000       | 2.2083       | 3.5056       | 15.2627      |
| 118 | 3    | 2     | 20.4000       | 4.1329       | 2.8187       | 22.9676      |
| 118 | 4    | 3     | 28.3000       | 5.7334       | 2.7094       | 29.6273      |
| 119 | 1    | 1     | 27.2000       | 6.8635       | 0.5506       | 14.9084      |
| 119 | 1    | 2     | 25.9000       | 6.5355       | 0.4249       | 10.9550      |
| 119 | 3    | 1     | 27.2000       | 6.8635       | 0.5506       | 14.9084      |
| 119 | 3    | 2     | 53.1000       | 13.3989      | 0.4893       | 25.8634      |
| ... | ...  | ...   | ...           | ...          | ...          | ...          |
+-----+------+-------+---------------+--------------+--------------+--------------+

期望的输出应该是这样的。但是，由于还有其他 Val1 和 Val2 值具有相同的 ID、相同的类型但不同的顺序，因此我猜 INNER JOIN 无法按预期工作。总是出现比我的过滤器更大和更小的值。其中一些从未出现过。

抱歉，如果太长了。我找不到正确的陈述或结构。有什么建议吗？

编辑:抱歉。我忘了提及必须显示 ID 的所有其他数据。这就是我尝试使用 join 的原因。

Answer 1

所以，您的要求是

Select Types (1,3,9)
Select Num (0,5)
Type 1, Num 5 should be bigger than 10 In Val2
Type 1, Num 5, should be smaller than 30 In Val2
Type 3, Num 0, should be smaller than 8 In Val1

这基本上是需要评估的 4 个不同标准(Type、num、Val1、Val2)

虽然可能有“更好”的解决方案(在性能或更容易阅读代码方面)，但最简单的方法是创建不同的条件对，除非您处理数百万条记录，否则这应该没问题。

根据您的要求，我会得出您需要获取以下行集:

1.) Type = 1, Num = 5, Val2 > 10
2.) Type = 1, Num = 5, Val2 < 30
3.) Type = 3, Num = 0, Val1 < 8
4.) Type = 9

因此，这将是“4 个显式”查询的联合:​​

SELECT * FROM analyse WHERE Type = 1 AND Num = 5 AND Val2 > 10
UNION
SELECT * FROM analyse WHERE Type = 1 AND Num = 5 AND Val2 < 10
UNION
SELECT * FROM analyse WHERE Type = 3 AND Num = 0 AND Val1 < 8
UNION
SELECT * FROM analyse WHERE Type = 9

要按 id 过滤最终结果，您可以简单地使用:

SELECT * FROM (
  SELECT * FROM analyse WHERE Type = 1 AND Num = 5 AND Val2 > 10
  UNION
  SELECT * FROM analyse WHERE Type = 1 AND Num = 5 AND Val2 < 10
  UNION
  SELECT * FROM analyse WHERE Type = 3 AND Num = 0 AND Val1 < 8
  UNION
  SELECT * FROM analyse WHERE Type = 9
) as tempTable WHERE id = 118

• 或者只是将 ID 附加到每个独立的查询中。