如何使用 UNION 子句结果作为表格 - How to use UNION clause result as table

How to use UNION clause result as table

propertyid  userid  shareddatetime
32          129     2015-02-03 11:09:33
33          129     2015-02-03 11:28:20
30          128     2015-02-03 11:33:02
32          128     2015-02-02 13:30:44
33          127     2015-02-02 13:40:44
30          128     2015-02-03 11:31:54

以上结果来自查询：

SELECT propertyid,userid,shareddatetime FROM `groupsharing` gs WHERE gs.groupid IN( SELECT `groupnum` FROM `groupuser` WHERE user = 129 ) AND gs.shareddatetime = (SELECT MIN(shareddatetime) FROM groupsharing gs2 WHERE gs.propertyid = gs2.propertyid )
     UNION 
    SELECT property,user,shareddatetime FROM sharing WHERE `shareduser` = '129' and `shareddatetime` = ( select min( `shareddatetime` ) from sharing s2 where s2.`shareduser` = sharing.`shareduser` and s2.`property` = sharing.`property` )

现在，如何从上述结果中选择具有最小共享日期时间的属性 id？

我的预期结果应如下：

  propertyid  userid  shareddatetime
      32        128     2015-02-02 13:30:44
      33        127     2015-02-02 13:40:44
      30        128     2015-02-03 11:31:54

提前致谢

你可以试试这个：

Select a.propertyid, a.userid, min(a.shareddatetime) 
  from (SELECT propertyid,userid,shareddatetime 
           FROM `groupsharing` gs WHERE gs.groupid IN(SELECT `groupnum` FROM `groupuser` WHERE user = 129 ) AND gs.shareddatetime = (SELECT MIN(shareddatetime) FROM groupsharing gs2 WHERE gs.propertyid = gs2.propertyid )
        UNION 
        SELECT property,user,shareddatetime 
           FROM sharing WHERE `shareduser` = '129' and `shareddatetime` = ( select min( `shareddatetime` ) from sharing s2 where s2.`shareduser` = sharing.`shareduser` and s2.`property` = sharing.`property` )
        ) as a group by a.propertyid, a.userid;

上面的答案如下

选择属性 ID、用户 ID、共享日期时间 FROM （选择属性 ID，用户 ID，共享日期时间 FROM groupsharing gs 其中 gs.groupid in（从groupuser中选择 groupnum 其中 user = 129 ）和 gs.shareddatetime = （从组共享中选择最小（共享日期时间）

gs2 其中 gs.propertyid = gs2.propertyid ）联合选择属性，用户，共享日期时间从共享中shareduser = '129' 和 shareddatetime = （从共享 S2 中选择 MIN（ shareddatetime ），其中 S2.shareduser = 共享。shareduser和 S2。property = 共享。property ）） u 其中 u. shareddatetime = （从（选择属性ID，用户ID，共享日期时间从 groupsharing gs 中选择最小（共享日期时间）其中 gs.groupid IN（从groupuser中选择groupnum 其中用户 = 129 ）和 gs.shareddatetime = （从组共享中选择最小（共享日期时间） GS2 其中 gs.propertyid = gs2.propertyid ）联合选择属性，用户，共享日期时间从共享中 shareduser = '129' 和 shareddatetime = （从共享 S2 中选择 MIN（ shareddatetime ）其中 S2.shareduser = 共享。shareduser和 S2。property = 共享。property ）） UI WHERE u.propertyid = ui.propertyid）