mysql 视图示例

CREATE VIEW DATAINFO_VIEW  AS  SELECT COUNT(*) AS NNumber FROM `TbGoods` WHERE nShelves = 1 AND nStock >0 
             UNION ALL
             SELECT COUNT(*) AS NNumber FROM `TbGoods` WHERE nShelves = 1 AND nStock = 0
             UNION ALL
             SELECT COUNT(*) AS NNumber FROM `TbGoods` WHERE nShelves = 0
             UNION ALL
             SELECT COUNT(*) AS NNumber FROM `TbGoodsOrder` WHERE nState = 2
             UNION ALL
             SELECT COUNT(*) AS NNumber FROM `TbGoodsOrder` WHERE nState = 3
             UNION ALL
             SELECT COUNT(*) AS NNumber  FROM  `TbUser`
             
             
 SELECT * FROM `month_line_view`
 
 CREATE VIEW YESTERDAY_LINE_VIEW AS SELECT COUNT(*) AS NNumber FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d')
                      UNION ALL
                      SELECT COUNT(DISTINCT(fkUser)) AS NNumber FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d')
                      UNION ALL
                      SELECT SUM(IFNULL(CAST(sPayPrice AS DECIMAL (15, 2)),0)) AS NNumber FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d')
                      UNION ALL
                      SELECT SUM(d.nCount) AS NNumber FROM `TbOrderDetail` d WHERE fkGoodsOrder IN(SELECT pkCode FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') = DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -1 DAY),'%Y-%m-%d'))
                      
                      
     CREATE VIEW TODAY_LINE_VIEW AS          SELECT COUNT(*) AS NNumber FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') 
                      UNION ALL
                      SELECT COUNT(DISTINCT(fkUser)) AS NNumber FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') 
                      UNION ALL
                      SELECT SUM(IFNULL(CAST(sPayPrice AS DECIMAL (15, 2)),0)) AS NNumber FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d') 
                      UNION ALL
                      SELECT SUM(d.nCount) AS NNumber FROM `TbOrderDetail` d WHERE fkGoodsOrder IN(SELECT pkCode FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') = DATE_FORMAT(NOW(),'%Y-%m-%d'))   
                      
                      
                                    
     CREATE VIEW WEEK_LINE_VIEW AS        SELECT COUNT(*) AS NNumber, DATE_FORMAT(tPay,'%Y-%m-%d') AS TDate FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') >=  DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -6 DAY),'%Y-%m-%d')  AND  DATE_FORMAT(tPay,'%Y-%m-%d') <=  DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY  DATE_FORMAT(tPay,'%Y-%m-%d')
                     UNION ALL
                     SELECT COUNT(DISTINCT(fkUser)) AS NNumber ,DATE_FORMAT(tPay,'%Y-%m-%d') AS TDate FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2)  AND  DATE_FORMAT(tPay,'%Y-%m-%d') >=  DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -6 DAY),'%Y-%m-%d')  AND  DATE_FORMAT(tPay,'%Y-%m-%d') <=  DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY  DATE_FORMAT(tPay,'%Y-%m-%d')
                     UNION ALL
                     SELECT SUM(IFNULL(CAST(sPayPrice AS DECIMAL (15, 2)),0)) AS NNumber ,DATE_FORMAT(tPay,'%Y-%m-%d') AS TDate FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') >=  DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -6 DAY),'%Y-%m-%d')  AND  DATE_FORMAT(tPay,'%Y-%m-%d') <=  DATE_FORMAT(NOW(),'%Y-%m-%d') GROUP BY  DATE_FORMAT(tPay,'%Y-%m-%d')
                     UNION ALL
                     SELECT SUM(d.nCount) AS NNumber ,DATE_FORMAT(tPay,'%Y-%m-%d') AS TDate FROM `TbOrderDetail` d WHERE fkGoodsOrder IN(SELECT pkCode FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') >=  DATE_FORMAT(DATE_ADD(NOW(),INTERVAL -6 DAY),'%Y-%m-%d')  AND  DATE_FORMAT(tPay,'%Y-%m-%d') <=  DATE_FORMAT(NOW(),'%Y-%m-%d')) GROUP BY  DATE_FORMAT(tPay,'%Y-%m-%d') 
                           
                    
                                        
     CREATE VIEW MONTH_LINE_VIEW AS             SELECT COUNT(*) AS NNumber, DATE_FORMAT(tPay,'%Y-%m-%d') AS TDate FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') >=  DATE_FORMAT(CURDATE(), '%Y-%m-01')  AND  DATE_FORMAT(tPay,'%Y-%m-%d') <=  DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d') GROUP BY  DATE_FORMAT(tPay,'%Y-%m-%d')
                     UNION ALL
                     SELECT COUNT(DISTINCT(fkUser)) AS NNumber ,DATE_FORMAT(tPay,'%Y-%m-%d') AS TDate FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2)  AND  DATE_FORMAT(tPay,'%Y-%m-%d') >=  DATE_FORMAT(CURDATE(), '%Y-%m-01')  AND  DATE_FORMAT(tPay,'%Y-%m-%d') <=  DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d') GROUP BY  DATE_FORMAT(tPay,'%Y-%m-%d')
                     UNION ALL
                     SELECT SUM(IFNULL(CAST(sPayPrice AS DECIMAL (15, 2)),0)) AS NNumber ,DATE_FORMAT(tPay,'%Y-%m-%d') AS TDate FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') >=  DATE_FORMAT(CURDATE(), '%Y-%m-01')  AND  DATE_FORMAT(tPay,'%Y-%m-%d') <=  DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d') GROUP BY  DATE_FORMAT(tPay,'%Y-%m-%d')
                     UNION ALL
                     SELECT SUM(d.nCount) AS NNumber ,DATE_FORMAT(tPay,'%Y-%m-%d') AS TDate FROM `TbOrderDetail` d WHERE fkGoodsOrder IN(SELECT pkCode FROM  `TbGoodsOrder` WHERE (nState=1 OR nState=2) AND  DATE_FORMAT(tPay,'%Y-%m-%d') >=  DATE_FORMAT(CURDATE(), '%Y-%m-01')  AND  DATE_FORMAT(tPay,'%Y-%m-%d') <=  DATE_FORMAT( LAST_DAY(CURDATE()), '%Y-%m-%d') GROUP BY  DATE_FORMAT(tPay,'%Y-%m-%d')) GROUP BY  DATE_FORMAT(tPay,'%Y-%m-%d')