sql server 公共表表达式的学习

不用写超级多的inner join  ，取别名的时候很痛苦 

用处：可以查询临时的结果集

          可以和数据表进行关联

         可以用来递归查询（当时没怎么看懂）

         用来达到和isnull一样的用法

实际应用的例子：

和isnull 用法对比

首先是isnull的写法

SELECT m.sysno ,
ISNULL((SELECT  COUNT(so.SysNo) FROM IPP3..SO_Master so
INNER JOIN IPP3..SO_CheckShipping sp
ON  so.SysNo=sp.SOSysNo 
WHERE so.IsMobilePhone IN (5,15)
 AND so.Status IN (0,1,4,5,7,41,45)
 AND so.OutZoneTime >= '2018-05-11 00:00:00' AND so.OutZoneTime< '2018-05-18 00:00:00'  AND sp.MerchantSysNo=m.SysNo
 GROUP BY sp.MerchantSysNo
),0)
 FROM ipp3..Vendor m
WHERE  m.sysno IN 
(642,
791,
1097,
) 
GROUP BY  m.sysno

然后是CTE（表表达式的用法）

WITH  A AS 
 (SELECT sp.MerchantSysNo, COUNT(so.SysNo) AS S FROM IPP3..SO_Master so
INNER JOIN IPP3..SO_CheckShipping sp
ON  so.SysNo=sp.SOSysNo 
WHERE so.IsMobilePhone IN (5,15)
 AND so.Status IN (0,1,4,5,7,41,45)
 AND so.OutZoneTime >= '2018-05-11 00:00:00' AND so.OutZoneTime< '2018-05-18 00:00:00' 
 GROUP BY sp.MerchantSysNo)

 SELECT V.SysNo,A.S FROM IPP3..Vendor V
LEFT JOIN A
 ON V.SysNo=A.MerchantSysNo
 WHERE V.SysNo  IN 
(642,
791,
1097,
) 

https://www.lanhusoft.com/Article/115.html