从goframe v1.16版本开始，ORM组件新增了便捷的子查询特性，目前支持常见的三种语法的子查询：Where子查询、Having子查询及From子查询。

Where子查询

我们可以在Where条件中使用子查询语句，示例：

// 获取默认配置的数据库对象(配置名称为"default")
db := g.DB()

db.Model("orders").Where("amount > ?", db.Model("orders").Fields("AVG(amount)")).Scan(&orders)
// SELECT * FROM "orders" WHERE amount > (SELECT AVG(amount) FROM "orders")

Having子查询

我们可以在Having条件中使用子查询语句，示例：

subQuery := db.Model("users").Fields("AVG(age)").WhereLike("name", "name%")
db.Model("users").Fields("AVG(age) as avgage").Group("name").Having("AVG(age) > ?", subQuery).Scan(&results)
// SELECT AVG(age) as avgage FROM `users` GROUP BY `name` HAVING AVG(age) > (SELECT AVG(age) FROM `users` WHERE name LIKE "name%")

From子查询

我们可以在使用Model方法创建模型时使用子查询语句，示例：

db.Model("? as u", db.Model("users").Fields("name", "age")).Where("age", 18).Scan(&users)
// SELECT * FROM (SELECT `name`,`age` FROM `users`) as u WHERE `age` = 18

subQuery1 := db.Model("users").Fields("name")
subQuery2 := db.Model("pets").Fields("name")
db.Model("? as u, ? as p", subQuery1, subQuery2).Scan(&users)
// SELECT * FROM (SELECT `name` FROM `users`) as u, (SELECT `name` FROM `pets`) as p











Content Menu

  • No labels

3 Comments

  1. xx

    有没EXISTS的子查询？比如像这个查询某日新增付费用户数的sql语句

    SELECT COUNT(1)  FROM payment_order p WHERE created_at between '2021-06-09 00:00:00' and '2021-06-09 23:59:59' AND `status`=1 AND  NOT EXISTS (SELECT DISTINCT uid from payment_order pp WHERE created_at < "2021-06-09 00:00:00" AND p.uid=pp.uid )

    1. 郭强

      暂时没有快速方法，你可以这样：

      subQuery := g.Model("payment_order", "pp").Distinct().Fields("uid").WhereLT("created_at", "2021-06-09 00:00:00").Where("p.uid", "pp.uid")
g.Model("payment_order", "p").
	WhereBetween("created_at", "2021-06-09 00:00:00", "2021-06-09 23:59:59").
	Where("status", 1).
	Where("NOT EXISTS ?", subQuery).
	Count()

      1. xx

        谢谢大佬已经可以了，之前用dao.PaymentOrder.Where一直子查询报错，用g.Model("paument_order"，“p”).Where就可以了；

        不过大佬写的WhereLT函数好像1.6还没有