golang xorm MSSQL where查詢案例

xorm官方中文文檔 參考 http://xorm.io/docs/

以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