详解gorm联合查询 && (on conflict do update set)

1）内联接（inner join）：将两个表中存在联接关系的字段符合联接条件的记录形成记录集；

如：Select A.name,B.name from A inner join B on A.id = B.id   和

Select A.name,b.name from A,B where A.id = B.id 结果是一样的（内联结的inner关键字可忽略）

2）外联接（分为左外联接和右外联接）

左联接A、B表，结果包括A的全部记录和符合条件的B的记录；右联结A、B表和左连接B、A表的结果是一样的。即：

Select A.name ，B.name from  A left join B on A.id = B.id   和

Select A.name ,B.name from B right join A on A.id = B.id 执行后的结果是一样的。

3）外联结的高级用法：三表联合查询；

例如：select username, pwd, gname, tel from (t1 left join t2 on t1.t1_id = t2.t1_id) left join t3 on t1.t1_id = t3.t1_id

所以在golang语言中，联合查询只用left join足够了，内联合查询用where条件语句就可替代。

1）定义表结构

type GoSystemInfo struct {
    ID           int    `gorm:"primary_key"`
    SystemId     string `gorm:"column:systemId;not null;index:SystemId"`
    SystemName   string `gorm:"column:systemName;;default:'defaultNull'"`
}
type GoServiceInfo struct {
    ID           int    `gorm:"primary_key"`
    SystemId     string `gorm:"column:systemId;not null;index:SystemId"`
    ServiceId    string `gorm:"column:serviceId;index:ServiceId"`
    ServiceName  string `gorm:"column:serviceName;default:'defaultNull'"`
}

2)定义接收查询数据的表结构

type Rresult struct {
    SystemId    string `json:"systemId"`
    SystemName  string `json:"systemName"`
    ServiceId   string `json:"serviceId"`
    ServiceName string `json:"serviceName"`
}


var results []Result

3）两个表进行联合查询

使用orm：

db.Table("go_service_info").
Select("go_service_info.serviceId as service_id, 
go_service_info.serviceName as service_name, 
go_system_info.systemId as system_id, 
go_system_info.systemName as system_name").
Joins("left join go_system_info on 
go_service_info.systemId = go_system_info.systemId").
Scan(&results)

原生sql：

db.
Raw("SELECT a.serviceId as service_id, a.serviceName as service_name, 
b.systemId as system_id, b.systemName as system_name 
FROM go_service_info a LEFT JOIN go_system_info b 
ON a.systemId = b.systemId").Scan(&results)

备注：使用别名 as system_id，用于映射返回值结构体。

4）需要用到条件的联合查询（where条件）

使用orm：

db.Table("go_service_info").
Select("go_service_info.serviceId as service_id, 
go_service_info.serviceName as service_name, 
go_system_info.systemId as system_id, 
go_system_info.systemName as system_name").
Joins("left join go_system_info on 
go_service_info.systemId = go_system_info.systemId 
where go_service_info = ? and go_system_info.systemId = ?","xxxx","xxxxx").
Scan(&results)

原生sql：

db.
Raw("SELECT a.serviceId as service_id, a.serviceName as service_name, 
b.systemId as system_id, b.systemName as system_name 
FROM go_service_info a LEFT JOIN go_system_info b 
ON a.systemId = b.systemId 
where a.serviceId = ? and b.systemId = ?","xxxx","xxxxx").Scan(&results)

插入多个字段，但是只想更新某个字段的情况（on conflict）：

sql:

INSERT INTO table_name(uid, xxx_id,answer, times) VALUES (?,?,?,?) 
ON CONFLICT (uid, xxx_id)DO UPDATE SET 
answer = EXCLUDED.answer,times = EXCLUDED.times