sqlite 使用WHERE子句子查询，通过附加条件选择每组中最大的n

rur96b6h 于 2022-12-13 发布在 SQLite

关注(0)|答案(3)|浏览(155)

我在SQLite 3.38.2数据库中有一个RoadInsp表。为了回答这个问题，我将数据放在CTE中：

with roadinsp (objectid, asset_id, date_, condition) as (
values
(1, 1, '2016-04-01', 20),
(2, 1, '2019-03-01', 19),
(3, 1, '2022-01-01', 18),
  
(4, 2, '2016-04-01', 17),
(5, 2, '2022-01-01', 16),
  
(6, 3, '2022-03-01', 15),  --duplicate date
(7, 3, '2022-03-01', 14),  --duplicate date
(8, 3, '2019-01-01', 13)
)  
select * from roadinsp

objectid  asset_id      date_   condition
       1         1  2016-04-01         20
       2         1  2019-03-01         19
       3         1  2022-01-01         18

       4         2  2016-04-01         17
       5         2  2022-01-01         16

       6         3  2022-03-01         15
       7         3  2022-03-01         14
       8         3  2019-01-01         13

demo
我使用的GIS软件只允许我在WHERE子句/SQL表达式中使用SQL，而不是完整的SELECT查询。
我想使用WHERE子句选择 * 每组中最大的n *。换句话说，对于每个ASSET_ID，我想选择日期最晚的行。
我可以使用如下WHERE子句表达式来实现这一点：

date_ = 
  (select max(subq.date_) from roadinsp subq where roadinsp.asset_id = subq.asset_id)

objectid  asset_id      date_   condition
       3         1  2022-01-01         18
       5         2  2022-01-01         16
       6         3  2022-03-01         15
       7         3  2022-03-01         14

这样就可以了，但它为资产#3选择了两行，因为该资产有两行具有相同的日期。
因此，我想通过选择条件值最高的行来打破平局。它看起来像这样：

objectid  asset_id  date_       condition
       3         1  2022-01-01         18
       5         2  2022-01-01         16
       6         3  2022-03-01         15  --this row has a higher condition value than the other duplicate row.
                                           --so the other duplicate row was omitted.

我只想为每个资产选择一行。因此，如果也有重复的条件值，则选择哪一个并不重要，只要只选择一个即可。
使用WHERE子句子查询，如何选择 * 每组最大的n *，并使用具有最大条件的行打破平局？

sqlite

来源：https://stackoverflow.com/questions/74756261/using-a-where-clause-subquery-select-the-greatest-n-per-group-with-a-tie-breake

3条答案

按热度按时间

pieyvz9o1#

我将使用IN（）与ORDER BY和LIMIT来检查主键...

SELECT * 
  FROM roadinsp r
 WHERE objectid IN (
         SELECT objectid
           FROM roadinsp
          WHERE asset_id = r.asset_id
       ORDER BY date_ DESC, condition DESC
          LIMIT 1
       )

这很容易扩展到每个资产n行。并且可以很容易地向ORDER BY添加附加条件。
Demo

赞(0）回复(0）举报 2022-12-13

niknxzdl2#

也许您可以在 where 子句中添加另一个类似的条件（根据我的理解，您只能修改这些条件吗？）：

select * 
from roadinsp r
where 
  date_ = (
    select max(subq.date_) 
    from roadinsp subq 
    where r.asset_id = subq.asset_id
 )
 and condition = (
   select Max(condition) 
   from roadinsp c 
   where c.date_ = r.date_ 
   and c.asset_id = r.asset_id
);

更新的Fiddle

赞(0）回复(0）举报 2022-12-13

jgzswidk3#

要求

我使用的GIS软件只允许我在WHERE子句/SQL表达式中使用SQL，而不是完整的SELECT查询。
使用WHERE子句子查询，如何选择每组中最大的n，并与具有最大条件的行断开连接？
业务规则
对于每个asset_id，选择包含最新date_的asset_id
如果同一date_上有多行，则选择condition最大的行
解决方案
将date_和condition连接成一个，然后选择最大值（），如下所示：
添加前导0取决于condition的最大位数

select * 
  from roadinsp
 where date_ || substr('00'||condition,-2,2) = 
  (select max(subq.date_ || substr('00'||condition,-2,2)) from roadinsp subq where roadinsp.asset_id = subq.asset_id);

结果：

objectid|asset_id|date_     |condition|
--------+--------+----------+---------+
       3|       1|2022-01-01|       18|
       5|       2|2022-01-01|       16|
       6|       3|2022-03-01|       15|

sqlite 使用WHERE子句子查询，通过附加条件选择每组中最大的n

3条答案

相关问题

热门标签

最新问答