求一个SQL的写法

Question

实例如下:有两张表 怎么把它结合成一张想要的表
表一:设备表
  CID    CNAME
  1      设备1
  2      设备2
  3      设备3
  4      设备4
表二: 其中CID为上表设备表的ID  AID   CID 
  1      1   
  2      1    
  3      3    
  4      4   
  5      4    
  6      4     
想要用SQL语句写成(就是设备被使用的几次)  CID   NUM
  1     2
  2     0
  3     1
  4     3

Answer 1

SELECT CID,ISNULL((SELECT COUNT(1) FROM TB WHERE CID=A.CID),0) AS RN FROM TA A

Answer 2

WITH 设备表  (  CID   , CNAME)
 AS
 (SELECT   1 ,     '设备1'
 UNION ALL 
  SELECT   2    ,  '设备2'
   UNION ALL 
  SELECT   3    ,  '设备3'
   UNION ALL 
  SELECT   4    ,  '设备4'),表二 (  AID,   CID )
  AS (
  SELECT  1 ,     1   
   union all SELECT 2  ,    1    
   union all SELECT 3  ,    3    
  union all SELECT  4  ,    4   
  union all SELECT  5  ,    4    
  union all SELECT  6   ,   4     )
  
  SELECT a.cid,ISNULL(COUNT(b.CID),0)num 
  FROM 设备表 a LEFT JOIN  表二 b ON a.CID=b.CID
  GROUP BY a.cid
  
  /*
  cid         num
 ----------- -----------
 1           2
 2           0
 3           1
 4           3
 
 (4 行受影响)
 
 
  */