MSSQL 4表联查，子查询，函数统计，分组条件查询综合应用实例

 1 SELECT 
 2  [UserName]
 3 ,[Yhxm]
 4 ,[Qx]
 5 ,[Phone]
 6 ,[Number] = COUNT([UserName])
 7 FROM 
 8 (SELECT 
 9      [UserName]   = A.[username] 
10     ,[Yhxm]          = B.[xm]          
11     ,[Qx]          = D.[P_Power]
12     ,[Phone]      = B.[dh] 
13     ,[DayCount]   = (SELECT COUNT(*) FROM [Caihuashun_FuJian_T] WHERE (SELECT (DATEDIFF(DAY,[F_bxsj],GETDATE())))   = 0) 
14     ,[MonthCount] = (SELECT COUNT(*) FROM [Caihuashun_FuJian_T] WHERE (SELECT (DATEDIFF(MONTH,[F_bxsj],GETDATE()))) = 0)  
15 FROM  
16 [Caihuashun_UserInfo_T] AS [A]
17 LEFT JOIN 
18 [Caihuashun_Vita_T] AS [B]
19 ON 
20 A.[username] = B.[username]
21 LEFT JOIN 
22 [Caihuashun_FuJian_T] AS [C]
23 ON 
24 C.[F_jxy] = A.[username]
25 LEFT JOIN 
26 [Caihuashun_Authority_T] AS [D]
27 ON 
28 D.[P_id] = A.[power]) 
29 AS [A]
30 GROUP BY [UserName],[Yhxm],[Qx],[Phone]