Firebird SQL挑战-当select返回两行时，返回包含数据的一行 [英] Firebird SQL challenge - return one row that has the data when select returned two rows

问题描述

我非常需要使select始终返回一行

I have a quite unique need to make select always return one row

我的SQL:

select * from table1 Where (table1.pk = :p1) or (table1.fk1 = :p1)

上面的SQL总是有两种情况可以返回:

The above SQL always has two cases for return:

1- my Select返回两个记录: 唯一的不同是其中一个记录具有数据，而另一个记录仅具有填充数据的ID，而其余字段为空.在这种情况下，我需要仅返回在其他字段中具有数据的那个.

1- my Select return two records: The only different is one of the records has data while the other has only the ID filled with data while the rest of its fields are null. I need in this case to return only the one that has data in other fields.

2-我的选择返回一条记录 在这种情况下，返回的记录只有填充了数据的ID字段，而其余字段为null，但这是我想要的，不需要任何进一步的处理.

2- my Select return one record In this case the record returned has only the ID field filled with data while the rest of the fields are null however this is what I want and no need for any further processing.

请告知是否可以在一个简单的Select SQL中执行此操作.我不能使用存储过程.

Please advise if is it possible to do that in one plain Select SQL. I can not use stored procedure.

推荐答案

您可以使用select语句的first子句仅获得1行.

You can use the first clause of the select statement to get only 1 row.

根据您的特定条件，您可以对结果集进行排序，按字段的其余字段降序，以确保仅在没有数据行的情况下才选择空行(在火鸟2.5中，空行排在第一位，但是AFAIK在最新版本中有所更改，因此请在应用此版本之前检查您的特定版本.)

Given your specific conditions, you can order the result set descending by the rest of the fields to be sure the null row is selected only in case there's no data row (null goes first in firebird 2.5, but AFAIK this changed somewhere in the last versions, so check your specific version before applying this).

您的最终查询将如下所示:

Your final query will look like this:

select first 1 * 
  from table1 
 where (table1.pk = :p1) 
    or (table1.fk1 = :p1)
 order by somecolumn;

somecolumn是其他可能包含空值的其他字段中最相关的.

somecolumn being the most relevant of the other fields that can contain null values.

您可以使用以下语句对此进行测试:

you can test this with this statements:

--two rows, one with ID and values and the other with ID and null
with q1 as (
      select 1 id, 'data' othercolumn
        from rdb$database
      union
      select 2 id, null othercolumn
        from rdb$database
     ) 
select first 1 *
  from q1
 order by othercolumn nulls last;

--versus:

--onw row with ID and null
with q1 as (
      select 2 id, null othercolumn
        from rdb$database
     ) 
select first 1 *
  from q1
 order by othercolumn nulls last;

这篇关于Firebird SQL挑战-当select返回两行时，返回包含数据的一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！