求存储过程高手帮忙！只会写sql的人伤不起.

fuxiuhua 2012-05-24 11:25:59

这个用sql写效率太慢求高手转换成存储过程谢谢。。。



 string year = Convert.ToDateTime(DateTime.Now.ToString()).Year.ToString();

 string monthe = Convert.ToDateTime(DateTime.Now.ToString()).Month.ToString();

        

        string sql = "select distinct Region_Code as 区域,City_Name as 城市,Sales_Name as 销售代表,Account_Name as 客户名称,Shop_Name as 店铺名称,Shop_Code as 店铺Code, ";

        sql += "'" + (monthe) + "月'=(select sum(View_Store.sale_Amount) from  View_Store  where convert(varchar(10),View_Store.Report_Date,23) between '" + (year + "-01" + "-1") + "' and '" + (year + "-1") + "'  and View_Store.Shop_Code=vf.Shop_Code) ,";



        for (int i = 1; i < Convert.ToInt32(monthe) + 1; i++)

        {

            sql += "'" + i + "月'=(select View_Store.sale_Amount from  View_Store  where year(View_Store.Report_Date)=" + year + " and month(View_Store.Report_Date)=" + i + "  and View_Store.Shop_Code=vf.Shop_Code), ";

            sql += "'" + i + "月同期对比'=cast(cast((isnull((select View_Store.sale_Amount from  View_Store  where year(View_Store.Report_Date)=" + Convert.ToInt32(Convert.ToInt32(year) - 1) + " and month(View_Store.Report_Date)=" + i + " and View_Store.Shop_Code=vf.Shop_Code) ,null)/ isnull((select View_Store.sale_Amount from  View_Store  where year(View_Store.Report_Date)=" + year + " and month(View_Store.Report_Date)=" + i + " and View_Store.Shop_Code=vf.Shop_Code) ,null)-1) as float) as varchar) +'%',";

        }



                for (int j = 1; j <= Convert.ToInt32(DateTime.Now.Day); j++)

                {

                    sql += " '" + (j) + "'=(select sum(sale_Amount) from  Sell_Salesout INNER JOIN  dbo.MST_Employee_Info ON dbo.Sell_Salesout.Employee_Code = dbo.MST_Employee_Info.Staff_Code INNER JOIN dbo.MST_Shop ON dbo.MST_Employee_Info.Shop_Code = dbo.MST_Shop.Shop_Code  where year(Sell_Salesout.Report_Date)=" + year + " and month(Sell_Salesout.Report_Date)=" + monthe + " and  Day(Sell_Salesout.Report_Date)=" + j + " and vf.Shop_Code=dbo.MST_Shop.Shop_Code) , ";

                }



         

       

        sql = sql.Remove(sql.LastIndexOf(","));

        sql += "   from View_SMSStore as vf  ";