GoFrame ORM 组件支持Union/UnionAll操作，Union/UnionAll操作符用于连接两个以上的 SELECT 语句的结果组合到一个结果集合中，关于Union/UnionAll组合查询的相关介绍可以参考MySQL的官方文档介绍 https://dev.mysql.com/doc/refman/8.0/en/union.html 。我们可以通过链式操作或者方法操作来实现Union/UnionAll操作。

方法定义

// Union does "(SELECT xxx FROM xxx) UNION (SELECT xxx FROM xxx) ..." statement.
func (c *Core) Union(unions ...*Model) *Model

// UnionAll does "(SELECT xxx FROM xxx) UNION ALL (SELECT xxx FROM xxx) ..." statement.
func (c *Core) UnionAll(unions ...*Model) *Model

Union

使用Union操作符，多个 SELECT语句会删除重复的数据。

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

db.Union(
    db.Model("user").Where("id", 1),
    db.Model("user").Where("id", 2),
    db.Model("user").WhereIn("id", g.Slice{1, 2, 3}),
).OrderDesc("id").All()
// (SELECT * FROM `user` WHERE `id`=1) 
// UNION 
// (SELECT * FROM `user` WHERE `id`=2) 
// UNION 
// (SELECT * FROM `user` WHERE `id` IN (1,2,3) 
// ORDER BY `id` DESC) ORDER BY `id` DESC

也可以通过dao链式操作实现：

dao.User.Union(
    dao.User.Where(dao.User.Columns.Id, 1),
    dao.User.Where(dao.User.Columns.Id, 2),
    dao.User.WhereIn(dao.User.Columns.Id, g.Slice{1, 2, 3}),
).OrderDesc(dao.User.Columns.Id).All()
// (SELECT * FROM `user` WHERE `id`=1) 
// UNION 
// (SELECT * FROM `user` WHERE `id`=2) 
// UNION 
// (SELECT * FROM `user` WHERE `id` IN (1,2,3) 
// ORDER BY `id` DESC) ORDER BY `id` DESC

UnionAll

使用UnionAll操作符，多个 SELECT语句不会删除重复的数据。

db.UnionAll(
    db.Model("user").Where("id", 1),
    db.Model("user").Where("id", 2),
    db.Model(table).WhereIn("id", g.Slice{1, 2, 3}),
).OrderDesc("id").All()
// (SELECT * FROM `user` WHERE `id`=1) 
// UNION ALL 
// (SELECT * FROM `user` WHERE `id`=2) 
// UNION ALL 
// (SELECT * FROM `user` WHERE `id` IN (1,2,3) 
// ORDER BY `id` DESC) ORDER BY `id` DESC

也可以通过dao链式操作实现：

dao.User.UnionAll(
    dao.User.Where(dao.User.Columns.Id, 1),
    dao.User.Where(dao.User.Columns.Id, 2),
    dao.User.WhereIn(dao.User.Columns.Id, g.Slice{1, 2, 3}),
).OrderDesc(dao.User.Columns.Id).All()
// (SELECT * FROM `user` WHERE `id`=1) 
// UNION ALL 
// (SELECT * FROM `user` WHERE `id`=2) 
// UNION ALL 
// (SELECT * FROM `user` WHERE `id` IN (1,2,3) 
// ORDER BY `id` DESC) ORDER BY `id` DESC











Content Menu

  • No labels

2 Comments

  1. 阿峰

    批量查询用这个可以吗, 框架有自带批量查询方法吗

  2. kobe

    record, _ := g.DB().Union(
            g.DB().Model("article").Where("id", 64),
            g.DB().Model("article").Where("id", 65),
        ).OrderDesc("id").All()

        for _, v := range record {
            fmt.Println(v)
        }

    使用这个union方法可以获得结果集，同时还会打印这个错误，显示sql语句有问题You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1，但是日志里面的sql运行又没有问题(SELECT * FROM `article` WHERE `id`=64) UNION (SELECT * FROM `article` WHERE `id`=65) ORDER BY `id` DESC，请问这个是现象是什么原因。