How to return two fields from a subquery(如何从子查询中返回两个字段)
本文介绍了如何从子查询中返回两个字段的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
下面的查询有效，但我想知道是否有更好的方法.
有一个子查询使用两个子查询.这两个子查询相同，但返回两个不同的字段.有没有办法只使用一个返回两个字段的子查询?
我检查了类似的问题(this、this 和 this)，但我认为它们不适用于这种情况.
这里是查询:
SELECT *,time(strftime('%s', EndTime) - strftime('%s', StartTime), 'unixepoch') AS 持续时间从 (选择 (选择时间FROM Log AS LogStartWHERE LogStart.User = Log.User ANDLogStart.Time <= Log.Time ANDLogStart.[Action] != '完成'按 LogStart.Time DESC 排序限制 1)作为开始时间，时间作为结束时间，用户，(选择 [动作]FROM Log AS LogStartWHERE LogStart.User = Log.User ANDLogStart.Time <= Log.Time ANDLogStart.[Action] != '完成'按 LogStart.Time DESC 排序限制 1)作为 [行动]来自日志WHERE [动作] = '完成')按持续时间 DESC 排序；
这是一些测试数据:
创建表日志 (时间日期时间，用户 CHAR,[行动] CHAR);插入日志值('2017-01-01 10:00:00'，'Joe'，'Play')；插入日志值('2017-01-01 10:01:00'，'Joe'，'done')；插入日志值('2017-01-01 10:02:00'，'Joe'，'Sing')；插入日志值('2017-01-01 10:03:00'，'Joe'，'done')；插入日志值('2017-01-01 10:04:00'，'Ann'，'Play')；插入日志值('2017-01-01 10:04:30', 'Bob', '没有相应完成"的操作必须被忽略')；插入日志值('2017-01-01 10:05:00'，'Joe'，'Play')；插入日志值('2017-01-01 10:06:00', 'Ann', 'done')；插入日志值('2017-01-01 10:07:00'，'Joe'，'done')；插入日志值('2017-01-01 10:08:00'，'Ann'，'Play')；插入日志值('2017-01-01 10:09:00'，'Ann'，'done')；
解决方案
使用自连接...我没有 SQLLite，所以这里的语法可能不对，但你应该明白...
p>
选择 e.*, time(strftime('%s', e.[Time]) -strftime('%s', s.[Time]), 'unixepoch') AS 持续时间from log e join log s——s为startevent；e 用于结束事件在 s.[用户] = e.[用户]和 s.[Action] != 'done'和 e.[Action] = '完成'和 s.[时间] =(从日志中选择 Max([时间]其中 [用户] = e.[用户]和 [时间] <= e.[时间]和 [Action] != '完成')
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?
I checked similar questions (this, this and this), but I don't think they apply in this case.
Here is the query:
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;
Here is some test data:
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');
解决方案
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')
这篇关于如何从子查询中返回两个字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！
本站部分内容来源互联网,如果有图片或者内容侵犯您的权益请联系我们删除！
