首页 新闻 搜索 专区 学院

asp.net mvc 如何统计多个sql条件下的数据并显示在layui的数据表格中

0
悬赏园豆:20 [待解决问题]

接口代码如下,期待大佬指点一番(这只是其中一个sql查询,还需添加其他sql语句查询结果)
public static string ListPageJsonLeWanOrderUserQuery(int page, int limit)
{
var start = limit * page - limit + 1;//根据分页的页面去选择数据的开始因素
var end = limit * page;//获得分页的最后因素

        string sql = "select  * from  (select  ROW_NUMBER()  over(order by province asc) as fy, SUBSTRING ( CONVERT ( VARCHAR ( 20 ), InsertTime, 20 ), 1, 10 ) AS QueryDate,	COUNT ( * ) AS NeworderCount, province  from [VIPRight].[dbo].[LeWanUserOrderInfo] where InsertTime > '2022-01-01' 	AND InsertTime <= getdate() GROUP BY SUBSTRING ( CONVERT ( VARCHAR ( 20 ), InsertTime, 20 ), 1, 10 ),Province ) A  where(fy between " + start + " and " + end + ") ORDER BY QueryDate";
        string sqlcount = "SELECT SUBSTRING	( CONVERT ( VARCHAR ( 20 ), InsertTime, 20 ), 1, 10 ) AS QueryDate,	COUNT ( * ) AS NeworderCount,	province FROM	VIPRight.[dbo].[LeWanUserOrderInfo] WHERE	InsertTime > '2022-01-01' 	AND InsertTime <= getdate() GROUP BY SUBSTRING ( CONVERT ( VARCHAR ( 20 ), InsertTime, 20 ), 1, 10 ),Province ORDER BY	QueryDate ";


        DataTable dt = DBHelper.ExecuteDataTable(sql);
        //DataTable dt2 = DBHelper.ExecuteDataTable(sql2);
        List<Management.Model.LeWanOrderUserQueryModel> lwouq = new List<Management.Model.LeWanOrderUserQueryModel>();
        {
            if (dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string QueryDate = dt.Rows[i]["QueryDate"].ToString();
                    if(QueryDate)
                    string NeworderCount = dt.Rows[i]["NeworderCount"].ToString();
                    string province = dt.Rows[i]["province"].ToString();
                    lwouq.Add(new Management.Model.LeWanOrderUserQueryModel()
                    {
                        QueryDate = QueryDate,
                        NeworderCount = NeworderCount,
                        province = province
                    });
                }
                
            }
        }
        
        DataTable dtcount = DBHelper.ExecuteDataTable(sqlcount);
        int count = (int)dtcount.Rows.Count; string ks = "{";
        string zhon = @"""code"":0,""msg"":"""",""count"":" + count + "";
        string data = @", ""data"":" + Newtonsoft.Json.JsonConvert.SerializeObject(lwouq) + "";
        string ke = "}";
        string json = "";
        json = ks + zhon + data + ke;
        return json;
    }
耳东259的主页 耳东259 | 初学一级 | 园豆:182
提问于:2022-11-29 18:14
< >
分享
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册