将多个sql select语句组合成一个

oyt4ldly 于 2021-06-17 发布在 Mysql

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

我需要将以下三条select语句组合成一条sql语句：

1. SELECT TotalTime FROM Session WHERE device_uid = ${my_uid} ORDER BY StartTime DESC LIMIT 1;

2. SELECT HoursAtService FROM Service WHERE device_uid = ${my_uid} ORDER BY StartTime DESC LIMIT 1;

3. SELECT ServiceHours FROM Type WHERE type_uid = ${my_type_uid};

最好是将结果作为包含以下字段的单行：

Result:
----------------------------------------------------------------
uid, TotalTime, HoursAtService, ServiceHours

我想我可能需要做一个内部连接，但我不是100%确定如何接近它。比如：

SELECT uid, TotalTime, HoursAtService, ServiceHours FROM Device AS t1 INNER JOIN (... -> continues

在表之间进行连接非常简单，但是如何使用多个where子句为不同的表进行连接呢？
有人能告诉我如何构造查询吗？
编辑表模式

Device:
---------------------
device_uid, type_uid, ....

Session:
---------------------
device_uid, session_uid, TotalTime, StartTime, EndTime, SessionTime

Service:
---------------------
device_uid, service_uid, StartTime, EndTime, HoursAtService

Type:
---------------------
type_uid, Name, ServiceHours

mysql Join

来源：https://stackoverflow.com/questions/54020250/combining-multiple-sql-select-statements-into-one

3条答案

按热度按时间

68bkxrlz1#

你可以用下面的查询来实现你提到的事情，我也注意到了方括号中的关键字，因为服务和会话都是sql关键字。

SELECT 
    [Device].Type_uid AS DeviceTypeId, [Device].Device_uid AS DeviceId, [Session].TotalTime, [Service].HoursAtService, [Type].ServiceHours 
FROM 
    [Session] INNER JOIN [Device] ON [Session].Device_uid = [Device].Device_uid 
    [Service] INNER JOIN [Device] ON [Service].Device_uid = [Device].Device_uid
    [Type] INNER JOIN [Device] ON [Type].Type_uid = [Device].Type_uid
WHERE
    [Device].Type_uid = ${my_type_uid} AND [Device].Device_uid = ${my_uid}

赞(0）回复(0）举报 2021-06-17

9q78igpj2#

您可以将以上单个查询合并到一个查询中，如下面的mysql查询：

SELECT Session.uid, Session.TotalTime, Service.HoursAtService, ServiceHours.ServiceHours
FROM Session 
JOIN Service ON Service.udi = Session.uid
JOIN ServiceHours ON ServiceHours.type_uid = Session.uid
WHERE Session.uid = ${my_uid}
ORDER BY Session.StartTime DESC LIMIT 1;

更改相关表中的列名。

赞(0）回复(0）举报 2021-06-17

ncecgwcz3#

尝试以下操作：

SELECT ${my_uid} AS uid,
       (SELECT TotalTime FROM Session WHERE uid = ${my_uid} ORDER BY StartTime DESC LIMIT 1) AS TotalTime,
       (SELECT HoursAtService FROM Service WHERE udi = ${my_uid} ORDER BY StartTime DESC LIMIT 1) AS HoursAtService,
       (SELECT ServiceHours FROM Type WHERE type_uid = ${my_type_uid) AS ServiceHours

赞(0）回复(0）举报 2021-06-17

我来回答

将多个sql select语句组合成一个

3条答案

相关问题

热门标签

最新问答