请求将其他两个请求的结果与SQL Server 2005中的GROUP BY子句一起加入

admin

请求将其他两个请求的结果与SQL Server 2005中的GROUP BY子句一起加入

sql

我经常发现自己在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

是否可以仅通过一条语句获得相同的最终结果，而避免创建视图？

阅读 154

2021-07-01

共1个答案

admin

当然，请使用嵌套查询：

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

2021-07-01