终于会用sql server中的With了，超简单，超方便！（原创总结）

最新推荐文章于 2024-08-25 19:21:51 发布

敦厚的曹操

最新推荐文章于 2024-08-25 19:21:51 发布

阅读量3.5k

点赞数 1

分类专栏： 1.【Sql Server】文章标签： sql server

本文链接：https://blog.csdn.net/dxnn520/article/details/7687324

版权

1.【Sql Server】专栏收录该内容

77 篇文章 4 订阅

订阅专栏

------ 没想到用with as 这么方便，就像操作临时表一样，白学sql server 了！

-- 1、With 不能嵌套
-- 2、With 之间要用逗号【,】隔开
-- 3、新建的With，一定要在下面引用，比如a，如没有【select * from a】，会报错。

with a as
(
select * from us_aaa
),
-- 下面是第二个 with
b as (
select * from a
)

select * from b

另一个实例

with a as
(
SELECT top 100000 substring(substring(Driver,2,20) , 1 , CHARINDEX (',',substring(Driver,2,20))-1) as Driver, busid, sum(days) as days,max(ActTime) as acttime
FROM MIS_RoadBill
WHERE busid>800 and busid<850 and (ActTime >= '2012-7-26') AND (ActTime <= '2012-08-25') and CHARINDEX (',',substring(Driver,2,20))>0 and substring(substring(Driver,2,100),1,CHARINDEX (',',substring(Driver,2,100))-1) in
(SELECT EmployeeName FROM Pub_Employee WHERE (Position LIKE '%司机%') AND (BelongsLine = '线'))
group by MIS_RoadBill.Driver,MIS_RoadBill.busid order by MIS_RoadBill.Driver,MIS_RoadBill.busid
)

select * from a