合并和优化两个查询结果sql server

Question

我有两个查询，请告诉我如何合并这两个查询，以及如何在GroupID基础上提炼这些query.Merge查询。

                      (SELECT     count(idlee.ObjectId) AS 'Count', idlee.GroupId, idlee.Name
                        FROM          (SELECT     CONVERT(int, Sum(idle.distance)) AS distance, idle.ObjectId, idle.GroupId, idle.Name
                                                FROM          (SELECT     Message.ObjectId, fn_GpsUtil_Distance(Message.x, Message.y, lead(Message.x)
                                                                                                OVER (partition BY Message.objectid
                                                                        ORDER BY Message.GpsTime), lead(Message.y) OVER (partition BY Message.objectid
                                                ORDER BY Message.GpsTime)) AS distance, [Group].GroupId, [Group].Name
                        FROM          [Group] INNER JOIN
                                               GroupObject ON [Group].GroupId = GroupObject.GroupId INNER JOIN
                                               Message ON GroupObject.ObjectId = Message.ObjectId INNER JOIN
                                               Object ON GroupObject.ObjectId = Object.ObjectId
                        WHERE      (Object.Enabled = 1) AND (Object.ClientId = 5) AND (Message.GpsTime >= GETDATE() - 1) AND 
                                               (Message.GpsTime <= GETDATE())) AS idle
     GROUP BY idle.ObjectId, idle.GroupId, idle.Name) AS idlee
     WHERE     idlee.distance < 10
    GROUP BY idlee.GroupId, idlee.Name) 

输出

  Count  GroupID  Group
      36    15  DC-1
      30    16  DC-2
      13    17  DC-3
      64    13  LC-1
      16    14  LC-2

我用来检索数据的第二个查询

        (SELECT     count(idlee.ObjectId) AS 'Count', idlee.GroupId, idlee.Name
  FROM          (SELECT     CONVERT(int, Sum(idle.distance)) AS distance, idle.ObjectId, idle.GroupId, idle.Name
                          FROM          (SELECT     Message.ObjectId, fn_GpsUtil_Distance(Message.x, Message.y, lead(Message.x) 
                                                                         OVER (partition BY Message.objectid
                                                  ORDER BY Message.GpsTime), lead(Message.y) OVER (partition BY Message.objectid
                          ORDER BY Message.GpsTime)) AS distance, [Group].GroupId, [Group].Name
  FROM          [Group] INNER JOIN
                         GroupObject ON [Group].GroupId = GroupObject.GroupId INNER JOIN
                         Message ON GroupObject.ObjectId = Message.ObjectId INNER JOIN
                         Object ON GroupObject.ObjectId = Object.ObjectId
  WHERE      (Object.Enabled = 1) AND (Object.ClientId = 5) AND (Message.GpsTime >= GETDATE() - 1) AND 
                         (Message.GpsTime <= GETDATE())) AS idle
 GROUP BY idle.ObjectId, idle.GroupId, idle.Name) AS idlee
 WHERE     idlee.distance >= 100 AND idlee.distance <= 300
 GROUP BY idlee.GroupId, idlee.Name) 



  Count  GroupID  Group
      40    15  DC-1
      50    16  DC-2
      20    17  DC-3
      64    13  LC-1
      16    14  LC-2

但我想要这样的输出在Group base上。

      GroupID  Group Count 0<10  Count 100 To 300
       15       DC-1   36             40
       16       DC-2   30             50
       17       DC-3   13             20
       13       LC-1   64             64
       14       LC-2   16             16

Answer 1

尝试使用UNION计算SQL，第一个this...join (idlee.ObjectId)为count1，0为count2，第二个为0为count1，count(idlee.ObjectId)为count2。

然后将整个SQL封装为一个临时表，并对Count1和Count2求和。

SELECT Temp.GroupID, Temp.Group, Sum(Temp.Count1) as CountLess10, Sum(Temp.Count2) as Count100300 FROM
(
                  (SELECT     count(idlee.ObjectId) AS count1, 0 as count2, idlee.GroupId as GroupID, idlee.Name as Group
                    FROM          (SELECT     CONVERT(int, Sum(idle.distance)) AS distance, idle.ObjectId, idle.GroupId, idle.Name
                                            FROM          (SELECT     Message.ObjectId, fn_GpsUtil_Distance(Message.x, Message.y, lead(Message.x)
                                                                                            OVER (partition BY Message.objectid
                                                                    ORDER BY Message.GpsTime), lead(Message.y) OVER (partition BY Message.objectid
                                            ORDER BY Message.GpsTime)) AS distance, [Group].GroupId, [Group].Name
                    FROM          [Group] INNER JOIN
                                           GroupObject ON [Group].GroupId = GroupObject.GroupId INNER JOIN
                                           Message ON GroupObject.ObjectId = Message.ObjectId INNER JOIN
                                           Object ON GroupObject.ObjectId = Object.ObjectId
                    WHERE      (Object.Enabled = 1) AND (Object.ClientId = 5) AND (Message.GpsTime >= GETDATE() - 1) AND 
                                           (Message.GpsTime <= GETDATE())) AS idle
 GROUP BY idle.ObjectId, idle.GroupId, idle.Name) AS idlee
 WHERE     idlee.distance < 10
 GROUP BY idlee.GroupId, idlee.Name)


UNION



    (SELECT     0 as count1, count(idlee.ObjectId) AS count2, idlee.GroupId as GroupID, idlee.Name as Name
  FROM          (SELECT     CONVERT(int, Sum(idle.distance)) AS distance, idle.ObjectId, idle.GroupId, idle.Name
                      FROM          (SELECT     Message.ObjectId, fn_GpsUtil_Distance(Message.x, Message.y, lead(Message.x) 
                                                                     OVER (partition BY Message.objectid
                                              ORDER BY Message.GpsTime), lead(Message.y) OVER (partition BY Message.objectid
                      ORDER BY Message.GpsTime)) AS distance, [Group].GroupId, [Group].Name
  FROM          [Group] INNER JOIN
                     GroupObject ON [Group].GroupId = GroupObject.GroupId INNER JOIN
                     Message ON GroupObject.ObjectId = Message.ObjectId INNER JOIN
                     Object ON GroupObject.ObjectId = Object.ObjectId
  WHERE      (Object.Enabled = 1) AND (Object.ClientId = 5) AND (Message.GpsTime >= GETDATE() - 1) AND 
                     (Message.GpsTime <= GETDATE())) AS idle
 GROUP BY idle.ObjectId, idle.GroupId, idle.Name) AS idlee
 WHERE     idlee.distance >= 100 AND idlee.distance <= 300
 GROUP BY idlee.GroupId, idlee.Name)    
 ) Temp Group By Temp.GroupID, Temp.Group

希望这能有所帮助。