Golang xorm MSSQL where query case

Xorm official Chinese document reference http://xorm.io/docs/

Take SQL Server as an example
First initialize the connection, etc...

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()

First, query case

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

or write your own SQL directly

Second, paging query

Mode one: Use the limit (int I,int j) method, i= the number of bars to take, j= the position of the beginning
MSSQL Although the results are correct, you can see the generated paging SQL is very messy, it is recommended that direct MSSQL paging directly in the form of two written in SQL. Other databases should be no problem, such as: MySQL
In fact, this article with the database version SQL2014 is supported: OFFSET 2 row FETCH NEXT ten row only, Xorm does not recognize the database version of the paging 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

Method Two: With the native SQL method, it is duly

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

Method Three: Using the native SQL + Limit method?? MSSQL was wrong sql& result

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)    

Mode four: Github.com/go-xorm/builder