SQL -返回的查询乘记录-笛卡尔产品

Question

我正在使用以下查询，该查询返回大量的乘积记录--即笛卡尔积：

SELECT * FROM specimen, topography_index, morphology, functions 
WHERE 
SUBSTRING(specimen.topography_index, 2, 2) = topography_index.topography_index_code 
AND 
if(specimen.snop_axis = 'M', morphology.morphology_code = specimen.snop_code, functions.functions_code = specimen.snop_code) 
AND 
specimen.topography_index = '_ORGAN_'
ORDER BY 
(specimen.room = 'f') DESC,specimen.snop_code

返回的记录数目约为59 000条。然而，标本表只有469条记录。形态学和函数表每个都有大约2000条记录，而topography_index表有29条记录，因此我猜想结果被“topography_index”和“形态学”或“functions”表(29x 2000)中的记录数相乘，无论哪种表在if子句中没有联接。

应该如何编写查询，这样才不会发生这种情况？

注意，GROUP不是一个选项，因为查询非常慢，所有记录都要返回。

http://sqlfiddle.com/#!2/2bda8/1

也许这可以由子select语句来处理？

任何帮助都很感激！

Answer 1

笛卡尔积应该是正确连接表中行#的乘积，乘以表中未正确连接的行(即在WHERE子句中没有有效条件)。对于specimen.snop_axis = 'M'形态学和样本被很好地连接但函数没有连接的情况，以及当specimen.snop_axis <> 'M'函数和样本正确地连接但形态学却没有，这很可能是产生的行数似乎不是表中任何行的直接乘积的原因。

为了解决这个问题，我首先尝试通过替换

AND if(specimen.snop_axis = 'M', morphology.morphology_code = specimen.snop_code, functions.functions_code = specimen.snop_code) 

使用

AND functions.functions_code = specimen.snop_code
AND morphology.morphology_code = specimen.snop_code

在我看来，这似乎给出了样本数据的ok结果，但我不太确定它如何处理实际数据。

我不完全确定，通过在IF子句中添加WHERE条件，您想得到什么。如果目标是在specimen.snop_axis='M'中返回形态学数据而不返回函数数据，而在其他情况下则返回函数数据而不返回形态学数据，我建议将选择逻辑应用于返回的列，具体如下：

SELECT
IF(specimen.snop_axis = 'M', morphology_pk, functions_pk) matching_pk,
IF(specimen.snop_axis = 'M', morphology_code, functions_code) matching_code,
IF(specimen.snop_axis = 'M', morphology_nomen, functions_nomen) matching_nomen,
specimen.*,
topography_index.*

而不是总是从两个表中返回列。但正如我所说，我不太清楚什么是必需的。

Answer 2

两个表的笛卡尔乘积使每一行与每一行相乘，这是不应该发生的。我将用这个例子解释这一点。

您可以看到这个结果有些不理想，因为一些元组提供了错误的信息。

因此，为了防止这种情况，使它以我们想要的方式运作，而不是写：

SELECT  ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS, ORDERS;

我们应该写：

SELECT  ID, NAME, AMOUNT, DATE
 FROM CUSTOMERS, ORDERS WHERE ORDERS.CUSTOMER_ID=CUSTOMERS.ID;

希望你知道你需要做什么才能得到正确的输出。