SQLITE - 合并与伯爵的和sum的两个表为一个结果

问题描述：

SELECT 
    b.ref, 
    ts.id_baits, 
    SUM(strftime('%s',ts.endtime) -strftime('%s',ts.initime)) AS t,COUNT(*) AS n 
FROM TimeSegments AS ts 
INNER JOIN Baits AS b ON b.id = ts.Id_Baits 
GROUP BY ts.id_baits 
ORDER BY b.ref 


SELECT b.ref, 
    COUNT(CASE WHEN e.Status=1 THEN 1 END) AS Undefined, 
    COUNT(CASE WHEN e.Status=1 THEN 1 END) AS GetClose, 
    COUNT(CASE WHEN e.Status=2 THEN 1 END) AS Threat, 
    COUNT(CASE WHEN e.Status=3 THEN 1 END) AS Attack, 
    COUNT(CASE WHEN e.Status=4 THEN 1 END) AS Hooked, 
    COUNT(CASE WHEN e.Status=5 THEN 1 END) AS Captured, 
    COUNT(CASE WHEN e.Status=6 THEN 1 END) AS Tagged, 
    COUNT (*) AS TOTAL 
FROM CastingsEvents AS e 
LEFT JOIN Trajectories AS tr ON tr.id = e.id_trajectories 
LEFT JOIN TimeSegments AS ts ON ts.id = tr.id_timesegments 
LEFT JOIN Baits AS b ON b.id = ts.Id_Baits 
GROUP BY ts.id_baits 
ORDER BY b.ref

可以看出，这两个表由id_baits分组。

我想将结果合并到一张表中，有人知道它是如何做到的？

我想，这可能有助于[UNION子句（https://www.tutorialspoint.com/sqlite/sqlite_unions_clause.htm） – Jerrol

“合并”是什么意思？请提供一些示例数据和您想要的输出。（请参阅[如何格式化堆栈溢出中的SQL表后？]（https://meta.stackexchange.com/q/96125）如何添加一些内容。） –

Hello Jerrol，UNION子句需要两个表都有相同的列（正确的？），情况并非如此。 – sgm

答

的提议（JOIN two SELECT statement results），一个LEFT JOIN解决的问题：

SELECT * FROM (
    SELECT 
     b.ref AS bait, 
     ts.id_baits, 
     SUM(strftime('%s',ts.endtime) -strftime('%s',ts.initime)) AS t, 
     COUNT(*) AS n 
    FROM TimeSegments as ts 
    INNER JOIN Baits as b ON b.id = ts.id_baits 
    GROUP BY ts.id_baits 
) BaitsUsage 
LEFT JOIN (
    SELECT 
     ts.id_baits, 
     COUNT(CASE WHEN e.Status=1 THEN 1 END) AS Undefined, 
     COUNT(CASE WHEN e.Status=1 THEN 1 END) AS GetClose, 
     COUNT(CASE WHEN e.Status=2 THEN 1 END) AS Threat, 
     COUNT(CASE WHEN e.Status=3 THEN 1 END) AS Attack, 
     COUNT(CASE WHEN e.Status=4 THEN 1 END) AS Hooked, 
     COUNT(CASE WHEN e.Status=5 THEN 1 END) AS Captured, 
     COUNT(CASE WHEN e.Status=6 THEN 1 END) AS Tagged, 
     COUNT (*) AS TOTAL 
    FROM CastingsEvents AS e 
    LEFT JOIN Trajectories AS tr ON tr.id = e.id_trajectories 
    LEFT JOIN TimeSegments AS ts ON ts.id = tr.id_timesegments 
    LEFT JOIN Baits AS b ON b.id = ts.Id_Baits 
    GROUP BY ts.id_baits 
) BaitsEvents 
ON BaitsUsage.id_baits=BaitsEvents.id_baits 
ORDER BY bait

SQLITE - 合并与伯爵的和sum的两个表为一个结果

相关推荐