如何在Access DB 2013中加入2个选择查询 [英] How to join 2 select query in Access DB 2013

问题描述

嗨
如何从同一个表中加入2个select语句，每个select语句都有我传入where sub condition的条件。



示例：

Hi How to join 2 select statement from same table,each select statement have subqery that i pass in where condition.

Example:

UserId  PDate             PName   PWeight BMI    Intake
1     05/08/2015 15:58    Aravind 56     26.63   630
1     06/08/2015 15:59    Aravind 55     35.77   870
1     08/08/2015 16:01    Aravind 44     18.08   690
1     10/08/2015 16:02    Aravind 54     30.07   240
1     12/08/2015 16:03    Aravind 66     31.39   170
1     14/08/2015 16:04    Aravind 56     26.63   1180
1     16/08/2015 16:19    Aravind 34     16.17   180

First查询和结果是

First query and result is

select
  UserId as FUserID,PName AS FPName,PWeight as FVW,BMI as FVBMI,Intake as FVIN ,PDate AS FPDate
from
  BodyMeasurements where PDate=(select
  min(PDate)
from
  BodyMeasurements
group by UserId)


FUserID	 FPName	    FVW	FVBMI	FVIN	FPDate
1	     Aravind	56	26.63	630	    05/08/2015 3:58:39 PM

第二次查询和结果是

Second query and result is

select
  UserId as LUserID,PName AS LPName,PWeight as LVW,BMI as LVBMI,Intake as LVIN,PDate AS LPDate
from
  BodyMeasurements where PDate=(select
  Max(PDate) 
from
  BodyMeasurements
group by UserId)


LUserIS	LPName	 LVW	LVBMI	LVIN	LPDate
1	    Aravind	 34	    16.17	180	    16/08/2015 4:19:39 PM

所以最后我需要使用join或union将这两个查询作为单个查询加入。如何实现这个。

如果我使用工会或工会，我可以获得2行结果，但我需要获得不同名称的单行，

像FUserID表单第一次查询和来自第二个查询的LUserID



Thanx

So finally i need to join both query as single query using join or union.How achieve this.
If i use union or union all i can get result in 2 rows,but i need to get single row with different names,
like FUserID form first query and LUserID from 2nd query

Thanx

推荐答案

如果你想得到<的最小值和最大值对于sigle用户，code> PDate ，试试这个：

If you want to get min and max value of PDate for sigle user, try this:

SELECT UserId, MIN(PDate) AS LPDate, MAX(PDate) AS FPDate
FROM BodyMeasurements 
GROUP UserId

< br $> b $ b

使用 UNION ALL [ ^ ]：

Using UNION ALL[^]:

SELECT UserId, PName, PWeight, BMI, Intake, MIN(PDate)
FROM BodyMeasurements
GROUP BY UserId, PName, PWeight, BMI, Intake
UNION ALL
SELECT UserId, PName, PWeight, BMI, Intake, MAX(PDate)
FROM BodyMeasurements
GROUP BY UserId, PName, PWeight, BMI, Intake

可以使用加入 [ ^ ]，但我需要提醒您：MS Access在有多个连接时喜欢括号。请参阅：如何：使用Access SQL执行联接 [ ^ ]





关于OP的评论......

It is possible to get data using Joins[^], but i need to warn you: MS Access likes brackets when there's more than one join. Please see: How to: Perform Joins Using Access SQL[^]


As to the OP's comments...

SELECT t1.*, t2.*
FROM (
     -- your first query
    (
     -- your second query
    ) AS t2 ON t1. UserId = t2.UserId

这篇关于如何在Access DB 2013中加入2个选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！