日期:2014-05-18  浏览次数:20469 次

求存储过程高手帮忙!只会写sql的人伤不起.
这个用sql写效率太慢 求高手转换成存储过程 谢谢。。。

C# code

 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  ";







------解决方案--------------------
谁知道你那是什么意思啊???
------解决方案--------------------
你都不写实现什么功能怎么写?
------解决方案--------------------
把表字段,测试数据及要达到的目的贴出来
------解决方案--------------------
建议写出你的表 字段 测试数据 需求的结果