SQL 如何在同一个表上内部连接两个选择查询
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15767448/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to inner join two select queries on same table
提问by TheChrisONeil
I am stuck... A 'data' table with columns 'value' and 'datatype' is populated with engine load and vehicle speed and each record is stamped with date, time, lat, long. I want to query for engine load over 10% while the vehicle is moving (e.g. speed > 0). I can create a query to select the engine load and I can create a query to select the vehicle speed but how do I create a query to select engine load when > 10% AND the Vehicle is moving where their date, time lat, and long are equal?
我被卡住了......一个带有“值”和“数据类型”列的“数据”表填充了发动机负载和车辆速度，每条记录都标有日期、时间、纬度、经度。我想在车辆移动时查询超过 10% 的发动机负载（例如速度 > 0）。我可以创建一个查询来选择发动机负载，我可以创建一个查询来选择车辆速度，但是当 > 10% 并且车辆正在移动到它们的日期、时间纬度和长度时，我如何创建一个查询来选择发动机负载是平等的？
This Query does not work, but it provides a jist of what I am trying to do. Can anyone help me create a query?
此查询不起作用，但它提供了我正在尝试执行的操作的 jist。谁能帮我创建一个查询？
tables
TName: data
PK datakey
value
fk1 dataeventkey
fk2 datatypenamekey
TName: datatypename
PK datatypenamekey
datatypename
TName: dataevent
PK dataeventkey
datetime
lat
long
SELECT
d1.datetime
FROM
(data INNER JOIN datatypename ON data.datatypenamekey = datatypename.datatypenamekey
INNER JOIN dataevent ON dataevent.dataeventkey = data.dataeventkey) d1
WHERE
( d1.datatypename = "Engine Load [%]" AND d1.value > 10 )
INNER JOIN
SELECT
d2.datetime
FROM
(data INNER JOIN datatypename ON data.datatypenamekey = datatypename.datatypenamekey
INNER JOIN dataevent ON dataevent.dataeventkey = data.dataeventkey) d2
WHERE
( d2.datatypename = "Vehicle Speed [mph]" AND d2.value > 0 )
ON d1.datetime = d2.datetime
回答by Phil Sandler
I'm not 100% sure I understand, but I think you just need to reference two instances of the same table. Kind of making some assumptions based on your SQL, but giving it a shot here:
我不是 100% 确定我理解，但我认为您只需要引用同一个表的两个实例。有点根据您的 SQL 做出一些假设，但在这里试一试：
SELECT
engineLoad.dateTime
FROM
(
SELECT
d.datakey,
de.datetime
FROM
data d
INNER JOIN datatypename dt ON data.datatypenamekey = dt.datatypenamekey
INNER JOIN dataevent de ON de.dataeventkey = d.dataeventkey
WHERE
d.value > 10 AND
dt.datatypename = "Engine Load [%]"
) engineLoad
INNER JOIN
(
SELECT
d.datakey,
de.datetime
FROM
data d
INNER JOIN datatypename dt ON data.datatypenamekey = dt.datatypenamekey
INNER JOIN dataevent de ON de.dataeventkey = d.dataeventkey
WHERE
d.value > 0 AND
dt.datatypename = "Vehicle Speed [mph]"
) vehicleSpeed
ON engineLoad.dataKey = vehicleSpeed.dataKey <==might need to remove this line
AND engineLoad.datetime = vehicleSpeed.datetime
EditLooks like you need to reference datatypename twice as well? Edited the above, so try again.
编辑看起来您还需要引用 datatypename 两次？编辑了上面的，所以再试一次。
