egg数据统计之mysql数据库创建视图并可当表使用并查询

打开视图---->新建视图-----> 运行sql ----> 保存

统计地区的愿望数量

sql语句

select `user`.`id` AS `id`,count(`userplant`.`userid`) AS `amount`,`user`.`locationid` AS `locationid`,`user->location`.`name` AS `locationname` from ((`userplants` `userplant` left join `users` `user` on((`userplant`.`userid` = `user`.`id`))) left join `locations` `user->location` on((`user`.`locationid` = `user->location`.`id`))) group by `userplant`.`userid`

项目中新增model后就可正常查询

表必须要有id

module.exports = app => {
    const { STRING, INTEGER, DATE } = app.Sequelize;
   
    const LocationPlantStatistic = app.model.define('locationplantstatistic', {
      id: { type: INTEGER, allowNull:false,comment:'id',autoIncrement: true,primaryKey: true, },
      userid: { type: INTEGER, allowNull:false,comment:'用户id' },
      amount: { type: INTEGER, allowNull:false,comment:'数量' },
      locationid: { type: INTEGER, allowNull:false,comment:'地区id' },
      locationname: { type: STRING, allowNull:false,comment:'地区名称' },

      // 其他字段...
    }, {
      // 如果数据库视图是只读的，可以添加 freezeTableName 为 true
      freezeTableName: true,
      // 如果数据库视图的名称与模型名不同，可以通过 tableName 指定
      tableName: 'locationplant_statistic',
      timestamps: false, // 如果视图不包含时间戳字段，可以设置为 false
    });
   
    return LocationPlantStatistic;
  };

视图sql语句添加自增列

select row_number() OVER (ORDER BY `userplant`.`plantid` )  AS `id`,`userplant`.`locationid` AS `locationid`,`userplant`.`plantid` AS `plantid`,count(`userplant`.`userid`) AS `amount`,`plant`.`name` AS `plantname` from (`userplants` `userplant` left join `plants` `plant` on((`userplant`.`plantid` = `plant`.`id`))) group by `userplant`.`locationid`,`userplant`.`plantid`