count(*)的写法 返回

liftlei 发布于2018/1/16

悬赏：5 飞吻

收藏

热忱回答（9）

• fate stay night VIP0

  2018/1/16

  select a.*,c.count(*) from a,(select * from b) c 你这个SQL可以缩写成

  
  

  select a.*,b.count(*) from a,b 

  0 回复
• fate stay night VIP0

  2018/1/16

  var list5 = db.Queryable<Student, School>((st, sc) => true)
  .Select<ViewModelStudent>
  ((st,sc)=>new ViewModelStudent{ Id=SqlFunc.GetSelfAndAutoFill(st.Id),Count=SqlFunc.AggregateCount(sc.Id)})
  .ToList();

  SQL是

  SELECT st.*, COUNT(`sc`.`Id`) AS `Count`  FROM `STudent` st  ,`School`  sc  WHERE ( 1 = 1 )

  
  

  0 回复
• liftlei VIP0

  2018/1/16

  @fate stay night：

  如果添加条件呢？ 

  如： select a.*,c.count(*) from a,(select * from b where bid=a.id) c

  0 回复
• liftlei VIP0

  2018/1/16

  @fate stay night：另外，文档没见到有  GetSelfAndAutoFill 这个，作用是什么？ 谢谢！

  0 回复
• liftlei VIP0

  2018/1/16

  

  0 回复
• liftlei VIP0

  2018/1/16

  最终效果是要这样的：

  SELECT

  a.*, 

  (select count(*) from areahit where AreaId=a.ID ) as hitcount,

    (select count(*) from areaforward where AreaId=a.ID ) as forwardcount

  FROM

  areas AS a

  
  

  0 回复
• liftlei VIP0

  2018/1/16

  搞定了！

  var data = db.Queryable<DbModels.areas>()

              .WhereIF(!string.IsNullOrEmpty(areaname), ar => ar.AreaName.Contains(areaname))

              .WhereIF(!string.IsNullOrEmpty(areacode), ar => ar.AreaCode.Contains(areacode))

              .Select(s => new ViewModels.Business.AreaViewModel

              {

                  ID = s.ID,

                  AreaName = s.AreaName,

                  AreaCode = s.AreaCode,

                  BelongArea = s.BelongArea,

                  BdMapPoint = s.BdMapPoint,

                  CreateTime = s.CreateTime,

                  CreatedBy = s.CreatedBy,

                  HitCount = SqlFunc.Subqueryable<DbModels.areahit>().Where(q => q.AreaId == s.ID).Select(i => SqlFunc.AggregateCount(i.ID)),

                  ForwardCount= SqlFunc.Subqueryable<DbModels.areaforward>().Where(q => q.AreaId == s.ID).Select(i => SqlFunc.AggregateCount(i.ID))

              }).ToPageList(pageIndex, pageSize, ref count);

  
  

  0 回复
• fate stay night VIP0

  2018/1/17

  

  0 回复
• fate stay night VIP0

  2018/1/17
  var list5 = db.Queryable<Student, School>((st, sc) => st.scid=sc.id)

  .Select<ViewModelStudent>

  ((st,sc)=>new ViewModelStudent{ Id=SqlFunc.GetSelfAndAutoFill(st.Id),Count=SqlFunc.AggregateCount(sc.Id)})

  .ToList();

  你写复杂了其实这样就可以了

  0 回复