如何在同一个表上内连接两个select查询

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?

我卡住了…“数据”表中列有“值”和“数据类型”，其中填充了发动机负载和车辆速度，每条记录都带有日期、时间、lat和long。我想查询引擎负载超过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?

这个查询不起作用，但是它提供了我正在尝试做的事情的第一步。有人能帮我创建一个查询吗?

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

1 个解决方案

#1

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:

我不能百分之百地确定我理解，但我认为您只需要引用同一个表的两个实例。基于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

Edit Looks like you need to reference datatypename twice as well? Edited the above, so try again.

编辑看起来您还需要引用两次数据类型名称?编辑了上面的内容，请再次尝试。

#1