sql - 在调试 SQL 查询时需要帮助

Question

我需要帮助来调试我的 sql 查询。比方说，我的表结构如下所示。

我想将结果合并到具有相同 Primary_Id 和 max(Row_Id) 的一行中

例如:- 对于以下内容

表:custom_tab_instance它仅包含“primary_id”和“instance_id”(此处不包含 imp)

表:custom_field_value

    +---------+-------------+----------+-------+
    | Row_Id  | Primary_Id  | Type     | Value |
    +=========+=============+==========+=======+
    | 4       | 1           | Name     | 123   |
    +---------+-------------+----------+-------+
    | 4       | 1           | Param_1  | 456   |
    +---------+-------------+----------+-------+
    | 5       | 1           | Name     | 456   |
    +---------+-------------+----------+-------+
    | 5       | 1           | Param_1  | 213   |
    +---------+-------------+----------+-------+
    | 3       | 2           | Name     | 745   |
    +---------+-------------+----------+-------+
    | 3       | 2           | Param_1  | 012   |
    +---------+-------------+----------+-------+
    | 4       | 2           | Name     | 203   |
    +---------+-------------+----------+-------+

预期结果:

+---------+-------------+------+----------+
| Row_Id  | Primary_Id  | Name | Param_1  |
+=========+=============+======+==========+
| 5       | 1           | 456  | 213      |
+---------+-------------+------+----------+
| 4       | 2           | 203  | NUll     |
+---------+-------------+------+----------+

当前结果集:

+---------+-------------+------+----------+
| Row_Id  | Primary_Id  | Name | Param_1  |
+=========+=============+======+==========+
| 5       | 1           | 456  | 213      |
+---------+-------------+------+----------+

我设法排在第一位。但是对于 Promary_Id = 2 ，我没有看到任何结果。

我的查询:

select
    max_cust.max_row_id as 'Row_Id',  
    cti.primary_id           as 'Primary Id',
    name.value         as 'Name',
    param_1.value      as 'Param_1'   
from  custom_tab_instance cti       
inner join (
             select max(row_id) as max_row_id, primary_id
             from custom_field_value where row_id !=null
             group by primary_id
        )max_cust
on max_cust.primary_id=cti.primary_id 

join custom_field_value name 
        on name.primary_id =max_cust.primary_id 
        and max_cust.max_row_id=name.row_id

join custom_field_value param_1
        on param_1.primary_id =max_cust.primary_id 
        and max_cust.max_row_id=param_1.row_id

我在这里错过了什么？我尝试执行左连接，但它不起作用。

注意:我正在使用 Sybase

谢谢。

Answer 1

您可以通过简化查询然后一次添加一大块信息来调试它，直到您丢失记录。有点像这样:

select * 
from   custom_tab_instance cti       

select max(row_id) as max_row_id, primary_id
from custom_field_value 
where row_id !=null
group by primary_id

select * 
from  custom_tab_instance cti       
inner join (
    select max(row_id) as max_row_id, primary_id
    from custom_field_value where row_id !=null
    group by primary_id
)max_cust
on max_cust.primary_id=cti.primary_id 

select * 
from  custom_tab_instance cti       
inner join (
     select max(row_id) as max_row_id, primary_id
     from custom_field_value where row_id !=null
     group by primary_id
)max_cust
on max_cust.primary_id=cti.primary_id 
join custom_field_value name 
on name.primary_id =max_cust.primary_id 
and max_cust.max_row_id=name.row_id

select * 
from  custom_tab_instance cti       
inner join (
     select max(row_id) as max_row_id, primary_id
     from custom_field_value where row_id !=null
     group by primary_id
)max_cust
on max_cust.primary_id=cti.primary_id 
join custom_field_value name 
on name.primary_id =max_cust.primary_id 
and max_cust.max_row_id=name.row_id
 join custom_field_value param_1
on param_1.primary_id =max_cust.primary_id 
and max_cust.max_row_id=param_1.row_id       

请注意，我使用了 select * 而不是您的字段列表。这是因为有时不同的字段是导致问题的原因，因此在尝试找出相对复杂的东西时查看所有字段通常很有帮助。不要在最终查询中留下 Select *。此处仅用于调试。

另请注意，如果您有很多结果，您可能需要添加一个 where 子句，专门限制您正在测试的记录，例如

WHERE cti.primary_id = 2

同样，这仅用于调试，您可以在完成后将其删除。

运行上面的每个查询后，检查 primary_id 是否仍然存在。这将帮助您确定失败的位置，然后查看先前查询中的值，看看在添加连接或 where 子句时是否可以确定是哪些值导致失败。

我的猜测是派生表最有可能出现问题。或者该数据甚至可能不在第一个表中(这不是我第一次对此感到惊讶)。