怎么创建视图

Question

select g_Name,Count(checkName1)Num from CheckMo where checkName1<>'无'  and checkTime between '2012-10-10' and '2012-10-20' group by g_Name
select g_Name,Count(checkName1)Num1,Count(checkName2)Num2 from CheckMo where checkName1<>'无' and checkName2<>'无' and checkTime between '2012-10-10' and '2012-10-20' group by g_Nameselect g_Name,Count(checkName2)Num2 from CheckMo where checkName1='无' and checkName2<>'无' and checkTime between '2012-10-10' and '2012-10-20' group by g_Name这三句合并为一句，该怎么创建视图

Answer 1

CREATE VIEW xxx
 AS
     SELECT  g_name ,
             SUM(num) num ,
             SUM(num1) num1 ,
             SUM(num2) num2
     FROM    ( SELECT    g_Name ,
                         COUNT(checkName1) Num ,
                         0 AS Num1 ,
                         0 AS Num2
               FROM      CheckMo
               WHERE     checkName1 <> '无'
                         AND checkTime BETWEEN '2012-10-10' AND '2012-10-20'
               GROUP BY  g_Name
               UNION ALL
               SELECT    g_Name ,
                         0 AS Num ,
                         COUNT(checkName1) Num1 ,
                         COUNT(checkName2) Num2
               FROM      CheckMo
               WHERE     checkName1 <> '无'
                         AND checkName2 <> '无'
                         AND checkTime BETWEEN '2012-10-10' AND '2012-10-20'
               GROUP BY  g_Name
               UNION ALL
               SELECT    g_Name ,
                         0 AS Num ,
                         0 AS Num1 ,
                         COUNT(checkName2) Num2
               FROM      CheckMo
               WHERE     checkName1 = '无'
                         AND checkName2 <> '无'
                         AND checkTime BETWEEN '2012-10-10' AND '2012-10-20'
               GROUP BY  g_Name
             ) a
     GROUP BY g_name