表设计:
根据小区,楼栋,房屋分组统计,flag=1取最大值作为地板,flag=2取最大值作为窗帘型号
EF:
var sql = from a in commdb.TB_HOUSE_EQUIP.AsNoTracking()
where (a.XQMC == model.XQMC || model.XQMC == null)
&& (a.LDBH == model.LDBH || model.LDBH == null)
group a by new { a.XQMC, a.LDBH, a.FWHLAST } into g
select new
{
XQMC = g.Key.XQMC,
LDBH = g.Key.LDBH,
FWHLAST = g.Key.FWHLAST,
Floor = g.Where(a => a.FLAG == 1).Max(a => a.FLOORCURTAIN),//地板
Curtain = g.Where(a => a.FLAG == 2).Max(a => a.FLOORCURTAIN)//窗帘
};
if (pageindex == 1)
recordCount = sql.Count();//记录总条数
QueryList = sql.OrderBy(a => a.XQMC).ThenBy(a=>a.LDBH).ThenBy(a=>a.FWHLAST).Skip((pageindex - 1) * pagesize).Take(pagesize).ToArray();
SQL语句:
select a.xqmc,a.ldbh,a.fwhlast,max(case when flag=1 then floorcurtain else null end) db,max(case when flag=2 then floorcurtain else null end) cl from tb_house_equip a
group by a.xqmc,a.ldbh,a.fwhlast