如何通过排除基于sql中的列值从多行中获取所需的行

My sql query potentially can pull 1 or 2 rows which differ in the value of the SourceType column as well as CreatedDate column. The possible values of the SourceType column are "New" or "Old". If only one row containing "Old" is present, I want to get that row. If two rows are present, I want to get the row with the value "New". I could have done this by ordering the rows by CreatedDate and get the top 1, but I would like to use the SourceType to get the required row. I am not sure whether coalesce will work here or not.

我的sql查询可能会拉出1或2行,这些行的SourceType列和CreatedDate列的值不同。 SourceType列的可能值为“New”或“Old”。如果只有一行包含“Old”,我想得到那一行。如果存在两行,我想获得值为“New”的行。我可以通过按CreatedDate排序行并获得前1来完成此操作,但我想使用SourceType来获取所需的行。我不确定合并是否会在这里起作用。

1 个解决方案

#1

You can do this using prioritization logic. It looks like this:

您可以使用优先级逻辑来完成此操作。它看起来像这样:

select t.*
from t
where t.sourcetype = 'New'
union all
select t.*
from t
where t.soucetype = 'Old' and
      not exists (select 1 from t t2 where t2.? = t.? and t2.sourcetype = 'New');

The ? is for the column that specifies the duplicates.

的?用于指定重复项的列。

Actually, the above logic will evaluate the subquery three times. That is okay for a single table. But there is actually a better way:

实际上,上面的逻辑将对子查询进行三次评估。这对单个表来说没问题。但实际上有更好的方法:

select q.*
from (select q.*,
             row_number() over (partition by ? order by sourceType) as seqnum
      from query q
     ) q
where seqnum = 1;

The ? is the id to identify multiple rows.

的?是标识多行的ID。

The order by sourceType is really la shorthand for order by case when sourceType = 'New' then 1 else 2 end.

当sourceType ='New'然后1 else 2结束时,sourceType的顺序实际上是la的简写。

#1