我正在嘗試使用 gorm 處理嵌套查詢，但我很難弄清楚這一點。查詢：SELECT smId AS 'slimeId', slStatus, slPPIV, slHighRiskSituation, (SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on'AND rnSmId = smId) AS 'holdingOn',(SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId) AS 'inSystem', smSuspectedCorona,IF(smStatusClosed != 0, 1, 0) as statusClosed, smSurge, (SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = smId) as 'ruinsOnHold',(SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = smId)as 'ruinsInSystem' FROM slimes WHERE slId != 0 GROUP BY slId HAVING slId > 0所以我可以完成一些非?；A的工作，問題是當嵌套出現時我不知道在那種情況下該怎么做var data []SlimeResponsedb := service.gormdbdb = db.Select("smId as slimeId", "slStatus", "slPPIV", "slHighRiskSituation")db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)db = db.Table("slimes").Find(&data)嘗試使用Where方法但效果不一樣Joins但無法正常工作
1 回答
MMTTMM
TA貢獻1869條經驗 獲得超4個贊
您可以使用subQuery
var data []SlimeResponse
db := service.gormdb
subQueryHoldingOn := db.
Select("count(rnIg)").
Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
Table("ruins")
subQueryInSystem := db.
Select("count(rnIg)").
Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
Table("ruins")
groupSubQueryHoldingOn := db.
Select("GROUP_CONCAT(rnIg)").
Where("rnSquidStatus = 'In system' AND rnSmId = ?", smId).
Table("ruins")
groupSubQueryInSystem := db.
Select("GROUP_CONCAT(rnIg)").
Where("rnSquidStatus = 'Holding on' AND rnSmId = ?", smId).
Table("ruins")
db = db.Select("smId as slimeId, slStatus, slPPIV, slHighRiskSituation, (?) as holdingOn, (?) as inSystem, smSuspectedCorona, IF(smStatusClosed != 0, 1, 0) as statusClosed, smSurge, (?) as ruinsOnHold, (?) as ruinsInSystem", subQueryHoldingOn, subQueryInSystem, groupSubQueryHoldingOn, groupSubQueryInSystem)
db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
db = db.Table("slimes").Find(&data)
或者
可以直接傳入select部分Select
var data []SlimeResponse
db := service.gormdb
db = db.Select(
`
smId AS 'slimeId',
slStatus,
slPPIV,
slHighRiskSituation,
(
SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
) AS 'holdingOn',
(
SELECT count(rnIg) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
) AS 'inSystem',
smSuspectedCorona,
IF(smStatusClosed != 0, 1, 0) as statusClosed,
smSurge,
(
SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'Holding on' AND rnSmId = ?
) as 'ruinsOnHold',
(
SELECT GROUP_CONCAT(rnName) FROM ruins WHERE rnSquidStatus = 'In system' AND rnSmId = ?
) as 'ruinsInSystem'
`, smId, smId, smId, smId
)
db = db.Where("smId != ?", 0).Group("smId ").Having("smId > ?", 0)
db = db.Table("slimes").Find(&data)
- 1 回答
- 0 關注
- 225 瀏覽
添加回答
舉報
0/150
提交
取消