请在SQL Server 2005中加入与集团其他两个请求的结果BY子句(Request joini

我经常发现自己一起做在SQL Server 2005采用以下的说法：

第1步：

create view view1 as 
select count(*) as delivery_count, clientid from deliveries group by clientid;

第2步：

create view view2 as 
select count(*) as action_count, clientid from routeactions group by clientid;

第三步：

select * from view1 inner join view2 on view1.clientid = view2.clientid

是否有可能获得相同的最终结果只有一个语句，避免意见的创造？

Answer 1:

当然，使用嵌套查询：

select *
from (select count(*) as delivery_count, clientid 
      from deliveries group by clientid) AS view1
inner join (select count(*) as action_count, clientid
            from routeactions group by clientid) AS view2
    on view1.clientid = view2.clientid

或用新的CTE语法，你可以有：

WITH view1 AS (
    select count(*) as delivery_count, clientid from deliveries group by clientid
), view2 AS (
    select count(*) as action_count, clientid from routeactions group by clientid
)
select * from view1 inner join view2 on view1.clientid = view2.clientid

Answer 2:

我想不出任何办法把我的头顶部，除非有某种routeactions和你没有提到交货之间的关系。 如果没有（FK的从一个到另一个，最有可能的），你不能没有一个或两个表扭曲数字的加入。

Answer 3:

SELECT
    clientid,
    (SELECT COUNT(*) FROM deliveries where clientid = clientIds.clientid) AS 'delivery_count',
    (SELECT COUNT(*) FROM routeactions WHERE clientid = clientIds.clientid)
AS 'action_count'
FROM
(
SELECT clientid FROM deliveries
UNION
SELECT clientid FROM routeactions
) clientIds

我认为，应该工作，有更容易的解决方案，如果你有一个客户表

文章来源: Request joining the results of two other requests with GROUP BY clause in SQL Server 2005