如何从子查询返回两个字段 [英] How to return two fields from a subquery

问题描述

下面的查询有效，但我想知道是否有更好的方法.

The query below works, but I would like to know if there is a better way to do it.

有一个子查询使用两个子查询.这两个子查询是相同的，但返回两个不同的字段.有没有办法只使用一个返回两个字段的子查询?

There is one subquery that uses two subqueries. The two subqueries are identical but return two different fields. Is there a way to use only one subquery that returns two fields?

我检查了类似的问题(this、this 和 this)，但我认为它们不适用于这种情况.

I checked similar questions (this, this and this), but I don't think they apply in this case.

这是查询:

SELECT *,
       time(strftime('%s', EndTime) - strftime('%s', StartTime), 'unixepoch') AS Duration
  FROM (
           SELECT (
                      SELECT Time
                        FROM Log AS LogStart
                       WHERE LogStart.User = Log.User AND 
                             LogStart.Time <= Log.Time AND 
                             LogStart.[Action] != 'done'
                       ORDER BY LogStart.Time DESC
                       LIMIT 1
                  )
                  AS StartTime,
                  Time AS EndTime,
                  User,
                  (
                      SELECT [Action]
                        FROM Log AS LogStart
                       WHERE LogStart.User = Log.User AND 
                             LogStart.Time <= Log.Time AND 
                             LogStart.[Action] != 'done'
                       ORDER BY LogStart.Time DESC
                       LIMIT 1
                  )
                  AS [Action]
             FROM Log
            WHERE [Action] = 'done'
       )
 ORDER BY duration DESC;

这里是一些测试数据:

CREATE TABLE Log (
    Time     DATETIME,
    User     CHAR,
    [Action] CHAR
);

insert into Log values('2017-01-01 10:00:00', 'Joe', 'Play');
insert into Log values('2017-01-01 10:01:00', 'Joe', 'done');
insert into Log values('2017-01-01 10:02:00', 'Joe', 'Sing');
insert into Log values('2017-01-01 10:03:00', 'Joe', 'done');
insert into Log values('2017-01-01 10:04:00', 'Ann', 'Play');
insert into Log values('2017-01-01 10:04:30', 'Bob', 'Action without corresponding "done" which must be ignored');
insert into Log values('2017-01-01 10:05:00', 'Joe', 'Play');
insert into Log values('2017-01-01 10:06:00', 'Ann', 'done');
insert into Log values('2017-01-01 10:07:00', 'Joe', 'done');
insert into Log values('2017-01-01 10:08:00', 'Ann', 'Play');
insert into Log values('2017-01-01 10:09:00', 'Ann', 'done');

推荐答案

使用自连接...我没有 SQLLite，所以这里的语法可能不正确，但你应该明白...

Use a self join... I don't have SQLLite, so the syntax may be off here, but you should get the idea...

Select e.*, time(strftime('%s', e.[Time]) - 
           strftime('%s', s.[Time]), 'unixepoch') AS Duration
From log e join log s  -- s is for the startevent; e for end event
    on s.[User] = e.[User]
       and s.[Action] != 'done'
       and e.[Action] = 'done'
       and s.[Time] = 
          (Select Max([time] from log
           where [User] = e.[User]
                   and [time] <= e.[Time]
                   and [Action] != 'done')

这篇关于如何从子查询返回两个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！