使用一個連接兩個表以對另一個進行分組

我有一個帶有 2 個表的 sqlite 數據庫。表a有一個事件的執行列表和它們發生的時間。表b有每個事件的開始和結束時間。

如何從表中找到表a中定義的每個組的最小事件b？ Schema在我的 SQLFiddle 中有描述。

我試圖得到如下結果：

id1  id2  time event
c1   d1   2    5
c1   d2   1.2  10
c1   d1   5    8.2

SELECT a.id1, a.id2, `time`, event
FROM a
LEFT JOIN b ON a.id1 = b.id1 AND a.id2 = b.id2
           AND a.`time` BETWEEN b.stime AND b.etime
Where not exists ( select null from a A1
                         Where a1.id1=a.id1 and a1.id2=a.id2
                         AND a1.`time` BETWEEN b.stime AND b.etime
                         And a1.`time` < a.`time` )

見http://sqlfiddle.com/#!5/284fb/4

獲得這些結果的另一種方法：

with e as (select rowid R, b.* from b)
select e.*,  a.* from (
select min( a.`time` ) T, e.R  from a,  e
 where a.`time` between e.stime and e.etime
     and a.id1 = e.id1
     and a.id2 = e.id2
 group by e.R
) M, a, e
where e.R = M.R
  and a.id1 = e.id1
  and a.id2 = e.id2
  and a.`time` = M.T;

使用包含嚴重缺陷的目前模式，無法及時獲得結果。通過一些簡單的修改，你可以在 21 秒內得到它。

如下修改您的表：

CREATE TABLE a (
`time`        REAL       NOT NULL,
 idA number,
 id1      CHAR        NOT NULL,
 id2       CHAR        NOT NULL,
 event    REAL);
CREATE TABLE IF NOT EXISTS "b"(
 id1 TEXT,
 id2 TEXT,
 stime REAL,
 etime REAL,
 idB number);
CREATE UNIQUE INDEX pk_b on b(idB);
CREATE INDEX i_b1 on b(id1,id2);
CREATE INDEX i_a1 on a(idA);

我給了 ba 主鍵。a.idA 包含對 b 的引用（外鍵）。這次更新花了相當長的時間。現在以下查詢在 21 秒內完成。

select b.*, a.event, a.`time`
 from b, a    
 where a.idA = b.idB
   and not exists (select null from a a1
                    where a1.idA = b.idB
                      and a1.`time` < a.`time`)

--EQP-- 0,0,1,SCAN TABLE a
--EQP-- 0,1,0,SEARCH TABLE b USING INDEX pk_b (idB=?)
--EQP-- 0,0,0,EXECUTE CORRELATED SCALAR SUBQUERY 1
--EQP-- 1,0,0,SEARCH TABLE a AS a1 USING INDEX i_a1 (idA=?)
c2|d2|834.0|1395.0|5|49.407297300706|834.0
c1|d2|1750.0|5332.0|1|49.9163756294503|1750.0
c1|d1|2088.0|5330.0|2|49.6802055002045|2088.0
....
c2|d1|9994707.0|9998630.0|5986|49.4294519703533|9994707.0
c2|d2|9998164.0|10000000.0|5988|48.9248418596976|9998164.0
c1|d1|9998324.0|9999377.0|5990|17.4151947643984|9998324.0
c1|d2|9998791.0|10000000.0|5987|48.9222722695804|9998791.0
c2|d1|9999744.0|10000000.0|5989|49.8495713740755|9999744.0
Run Time: real 21.612 user 21.377030 sys 0.207799
sqlite> select count(*) from a;
11313047
sqlite> select count(*) from b;
5990

引用自：https://dba.stackexchange.com/questions/198978