如何从多个表中查询并从另一个表中获取列值

Question

问题:我需要根据unitlog_table上的UnitNo获得用户名。

这是我的查询:

select distinct(avh.UnitNo), 
isnull((select count(UnitNo) from unitlog_table 
where UnitNo = avh.UnitNo 
and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00' 
and speed >= 41),0) as overord,
isnull((select count(UnitNo) from unitlog_table 
where UnitNo = avh.UnitNo 
and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00' 
and speed >= 71),0) as overex ,
isnull((select count(UnitNo) from unitlog_table 
where UnitNo = avh.UnitNo 
and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00' 
and speed >= 91),0) as overc,
isnull((select count(UnitNo) from unitlog_table 
where UnitNo = avh.UnitNo 
and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00' 
and speed >= 41 and speed >= 71 and speed >= 91), 0) as ttover,
(select distinct(username) 
from unit_table av 
inner join users_table ut on ut.UnitNo = av.UnitNo 
where UnitNo = avh.UnitNo) as username
from 
unitlog_table avh 
where 
avh.UnitNo in (select UnitNo from unit_table where userid = '1122')  
and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00'

这是我的表1 (unitlog_table):

UnitNo        timestamp           speed
----------------------------------------
unit1   2021-09-01 07:36:21.00    7.72
unit1   2021-09-01 07:36:30.00    9.58
unit1   2021-09-01 07:37:16.00    7.2
unit1   2021-09-01 07:37:37.00    18.32
unit1   2021-09-01 07:38:18.00    1.85
unit1   2021-09-01 07:38:27.00    13.32
unit1   2021-09-01 07:38:45.00    12.94
unit1   2021-09-01 07:47:39.00    8.34
unit1   2021-09-01 07:48:07.00    30.04
unit1   2021-09-01 07:48:24.00    31.825
unit1   2021-09-01 07:49:06.00    30.26
unit1   2021-09-01 07:49:24.00    33.875
unit1   2021-09-01 07:49:33.00    33.54
unit1   2021-09-01 07:50:21.00    37.235
unit1   2021-09-01 07:50:43.00    15.22
unit1   2021-09-01 07:50:51.00    13.435
unit1   2021-09-01 07:51:24.00    17.03
unit1   2021-09-01 07:52:17.00    16.915
unit1   2021-09-01 07:52:33.00    18.435
unit1   2021-09-01 07:52:54.00    16.805
unit1   2021-09-01 07:53:15.00    76.225
unit1   2021-09-01 07:53:29.00    24.375
unit1   2021-09-01 07:54:21.00    29.925
unit1   2021-09-01 07:55:14.00    17.39
unit1   2021-09-01 07:55:29.00    22.975
unit1   2021-09-01 07:56:19.00    8.015
unit1   2021-09-01 07:56:28.00    42.68
unit1   2021-09-01 07:56:57.00    24.33
unit1   2021-09-01 07:57:16.00    33.575
unit1   2021-09-01 07:57:25.00    13.215
unit1   2021-09-01 07:57:39.00    31.245
unit1   2021-09-01 07:58:16.00    90.705
unit1   2021-09-01 07:58:31.00    58.665
unit1   2021-09-01 07:58:47.00    44.06
unit1   2021-09-01 07:59:16.00    40.32
unit2   2021-09-01 07:57:25.00    13.215
unit2   2021-09-01 07:57:39.00    31.245
unit2   2021-09-01 07:58:16.00    60.705
unit2   2021-09-01 07:58:31.00    58.665
unit2   2021-09-01 07:58:47.00    44.06
unit2   2021-09-01 07:59:16.00    40.32
unit3   2021-09-01 07:57:25.00    43.215
unit3   2021-09-01 07:57:39.00    71.245
unit3   2021-09-01 07:58:16.00    60.705
unit3   2021-09-01 07:58:31.00    98.665
unit3   2021-09-01 07:58:47.00    44.06
unit3   2021-09-01 07:59:16.00    40.32

这是我的表2 (unit_table):

UnitNo  userid
--------------
unit1   1122
unit2   1122
unit3   1122
unit4   5577
unit5   5577
unit6   8888

这是我的表3 (users_table):

userid   username
--------------------
1122     Rolly
1122     Efren
1122     Mark   --  edited to match OP comment
5577     Bert
5577     Jay
8888     Eric

预期结果:username userid UnitNo overord overoverover over over over over第1单元2 12 5Efren 1122 Unit2 11 1 3

Answer 1

-- Please try this code and see if it works for you.
-- It doesnt match the result set you posted, but I beleive it is correct based on your sample data
SELECT
-- without a table that we can use to xref unitno to username we are forced to use CASE
-- this is not recommended and you should consider creating the necessary xref table
CASE WHEN avh.UnitNo = 'unit1' THEN 'Rolly' 
WHEN avh.UnitNo = 'unit2' THEN 'Efren' 
WHEN avh.UnitNo = 'unit3' THEN 'Mark' END AS Username,
-- ---------------------------------------------------------------------------------------
av.userid,
avh.UnitNo,
-- I consider that you need to reference upper limits to give you the requisite breaks -- as below
SUM(CASE WHEN speed >= 41 AND speed < 71 THEN 1 ELSE 0 END) AS overord,
SUM(CASE WHEN speed >= 71 AND speed < 91 THEN 1 ELSE 0 END) AS overex,
SUM(CASE WHEN speed >= 91 THEN 1 ELSE 0 END) AS overc,
SUM(CASE WHEN speed >= 41 THEN 1 ELSE 0 END) AS ttover
FROM 
unitlog_table AS avh
INNER JOIN 
unit_table AS av ON avh.UnitNo = av.UnitNo
WHERE 
--  removed BETWWEEN and addeed half open intervals....google is your friend
avh.timestamp >= '2021-09-01 00:00:00.00' AND avh.timestamp < '2021-09-01 08:00:00.00'
AND av.userid = 1122
GROUP BY
avh.UnitNo,
av.userid

<表类>用户名useridUnitNooverordoverexovercttovertbody><<tr>罗利1122第一3205艾夫伦1122unit23003标记1122叫3115

看起来您确实需要条件聚合。您只需要连接一些表，分组，然后有条件地聚合它们

条件speed >= 41 and speed >= 71 and speed >= 91似乎在逻辑上不正确。
条件between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00'似乎也是错误的。
始终使用half open intervals>= AND <rather than之间'>

select avh.UnitNo, 
count(case when speed >= 41 then 1 end) as overord,
count(case when speed >= 71 then 1 end) as overex ,
count(case when speed >= 91 then 1 end) as overc,
count(case when speed >= 41 and speed >= 71 and speed >= 91 then 1 end) as ttover,
ut.username
from 
unitlog_table avh 
inner join
unit_table unt on avh.UnitNo = unt.UnitNo
inner join
users_table ut on ut.userid = unt.userid
where 
ut.userid = '1122'
and timestamp between '2021-09-01 00:00:00.00' and '2021-09-01 08:00:00.00'
group by
avh.UnitNo;