golang xorm MSSQL where查询案例

以sqlserver为例

先初始化连接等...

engine, err := xorm.NewEngine("mssql", "server=127.0.0.1;user id=sa;password=123456;database=dbname")

//控制台打印SQL语句

engine.ShowSQL(true)

if err != nil {

    fmt.Println(err)

}

defer engine.Close()

一、查询案例

ids := []model.MsIdcaid{}   //实体定义的话自己写

engine.Cols("Id", "Address").Where("id in(2,3,4,5,6)").OrderBy("id desc,address asc").Find(&ids)

//[SQL] SELECT "Id", "Address" FROM "cdsgus" WHERE (id in(2,3,4,5,6)) ORDER BY id desc,address asc

或者直接自己写SQL

engine.SQL("SELECT Address from cdsgus where id in (2,3,4,6) order by id desc ").Find(&ids)

//[SQL] SELECT Address from cdsgus where id in (2,3,4,6) order by id desc

二、分页查询

方式一：用Limit(int i,int j) 方法， i=要取的条数， j=开始的位置

MSSQL 虽然执行的结果正确，可以看到生成的分页SQL很乱，建议直接MSSQL分页直接用方式二写在SQL里。其他数据库应该是没有问题，如：mysql

其实本文用数据库的版本SQL2014 是支持：OFFSET 2 ROW FETCH NEXT 10 ROW ONLY的写法的，xorm并未识别数据库的版本调整分页SQL

engine.Cols("Id", "Name").Where("id in(2,3,4,5,6)").OrderBy("id desc,address asc").Limit(10, 2).Find(&ids)

//[[SQL] SELECT  TOP 10 "Id", "Name" FROM "cdsgus" WHERE (id in(2,3,4,5,6)) AND (id NOT IN (SELECT TOP 2 id FROM "cdsgus" WHERE (id in(2,3,4,5,6)) ORDER BY id desc,address asc)) ORDER BY id desc,address asc

方式二：用原生的SQL方法，很妥

engine.SQL("SELECT Id,Name from cdsgus where id in (2,3,4,5,6) order by id desc OFFSET 2 ROW  FETCH NEXT 10 ROW ONLY").Find(&ids)

//[SQL] SELECT Id,Name from cdsgus where id in (2,3,4,5,6) order by id desc OFFSET 2 ROW FETCH NEXT 10 ROW ONLY

方式三：用原生的SQL + Limit 方法？？MSSQL居然是错误SQL&结果

engine.Sql("SELECT Id,Name from cdsgus where id in (2,3,4,5,6) ").OrderBy("id").Limit(10, 2).Find(&ids)

//[SQL] SELECT Id,Name from cdsgus where id in (2,3,4,5,6)

data, _ := engine.Sql("SELECT Id,Name from cdsgus where id in (2,3,4,5,6) ").OrderBy("id").Limit(10, 2).Query()

//[SQL] SELECT Id,Name from cdsgus where id in (2,3,4,5,6)

方式四： github.com/go-xorm/builder

巴特西

golang xorm MSSQL where查询案例

一、查询案例

二、分页查询

最新文章

热门文章