这是一个有效的SQL查询吗?

huangapple go评论82阅读模式
英文:

Is this a valid SQL query

问题

以下是要翻译的内容:

// 第一种方式
db.Table("tbl1 AS A").Select("A.field1").Joins("JOIN tbl2 AS B ON A.field2 = B.field2").Find(&result)

// 第二种方式
db.Table("tbl1 AS A").Select("field1").Joins("JOIN tbl2 AS B ON A.field2 = B.field2").Find(&result)

假设这是有效的,你会如何使用 Golang 和 GORM 进行转换?

英文:
SELECT A.field1 FROM tbl1 AS A,tbl2 AS B WHERE A.field2=B.field2;

Or this the correct way

SELECT field1 FROM tbl1 AS A,tbl2 AS B WHERE A.field2=B.field2;

Assuming this is valid, how would you convert it into golang using gorm?

答案1

得分: 4

实际上这取决于情况。如果field1只存在于tbl1或tbl2中,那么两种写法都可以。否则,第一种写法是正确的,可以消除歧义。

顺便说一句,与其使用这种旧式的内连接写法,你应该这样写:

SELECT A.field1 
FROM tbl1 AS A
INNER JOIN tbl2 AS B ON A.field2=B.field2;

使用gorm的话,可以这样写:

db.Joins("Tbl2").Find(&tbl1)

如果你说你只了解这个SQL,可以使用原始的SQL语句来实现:

var field1 string

rows, err := db.Raw("SELECT A.field1 FROM tbl1 AS A INNER JOIN tbl2 AS B ON A.field2=B.field2 WHERE B.Field3 = ?", "something").Rows()
defer rows.Close()
for rows.Next() {
  rows.Scan(&field1)

  // ...
}
英文:

Actually it depends. If field1 only exists in tbl1 or tbl2 then both would work. Otherwise first one is correct and removes ambiguity.

BTW, instead of using such old style inner join you should write that as:

SELECT A.field1 
FROM tbl1 AS A
inner join tbl2 AS B ON A.field2=B.field2;

With gorm:

db.Joins("Tbl2").Find(&tbl1)

EDIT? Since you are saying you don't know anything other than this SQL, you could use raw SQL as such:

var field1 string

rows, err := db.Raw("SELECT A.field1 FROM tbl1 AS A inner join tbl2 AS B on A.field2=B.field2 where b.Field3 = ?", "something").Rows()
defer rows.Close()
for rows.Next() {
  rows.Scan(&field1)

  // ...
}

答案2

得分: 0

type Tbl1 struct {
....
}

var Fields []struct {
   Field1 string
}

-- gorm

err := gorm.Model(&Tbl1{}).
   Joins("tbl2 ON tbl1.field2 = tbl2.field2").
   Select("tbl1.field1").
   Scan(&Fields).Error
if err!=nil {
 ...
}

参考:https://gorm.io/docs/query.html

英文:
type Tbl1 struct {
....
}

var Fields []struct {
   Field1 string
}

-- gorm

err := gorm.Model(&Tbl1{}).
   Joins("tbl2 ON tbl1.field2 = tbl2.field2").
   Select("tbl1.field1").
   Scan(&Fields).Error
if err!=nil {
 ...
}

Reference: https://gorm.io/docs/query.html

答案3

得分: 0

模型

type tbl1 struct {
	field1 string
	field2 string
}

type tbl2 struct {
	field1 string
	field2 string
}

SQL

SELECT A.field1 FROM tbl1 A INNER JOIN tbl2 B ON A.field2 = B.field2

Gorm

db.
	Model(tbl1{}).
	Select("A.field1").
	Joins("INNER JOIN tbl2 B ON A.field2 = B.field2").
	Scan(&tbl1{})
英文:

Models

type tbl1 struct {
	field1 string
	field2 string
}

type tbl2 struct {
	field1 string
	field2 string
}

SQL

SELECT A.field1 FROM tbl1 A INNER JOIN tbl2 B ON A.field2 = B.field2

Gorm

db.
	Model(tbl1{}).
	Select("A.field1").
	Joins("INNER JOIN tbl2 B ON A.field2 = B.field2").
	Scan(&tbl1{})

huangapple
  • 本文由 发表于 2022年9月19日 21:47:58
  • 转载请务必保留本文链接:https://go.coder-hub.com/73774433.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定