问题

所以我有一个类似这样的 SQL 查询：

SELECT accounts.id, accounts.username, accounts.password, 
      accounts.created, accounts.last_logged_in, accounts.access,
      banned.reason, banned.expires,
      player.x, player.y, player.zone
FROM accounts
LEFT JOIN banned
ON accounts.id = banned.account_Id
INNER JOIN player
ON accounts.id = player.account_Id
WHERE accounts.username = username

如果我想在 Go 中将其存储在一个结构体中，我通常会这样做：

type Account struct {
    Id           int
    Username     string
    Password     string
    Email        string
    Created      time.Time
    LastLoggedIn time.Time
    AccessLevel  int
    Location     struct {
        Zone string
    }
    Banned []*Banned
}

type Banned struct {
    Reason  string
    Expires time.Time
}
reply := new(Account)

stmt, err := this.Database.Prepare("CALL findUser(?)")
    defer stmt.Close()

if err != nil {
    logger.ERROR.Println(err)
    return err
}

err = stmt.QueryRow(args).Scan(&reply.Id, &reply.Username ... 你明白我的意思)

然而，这样做不起作用，因为 scan 会期望每个参数都有一个值，而我们在 banned 上进行了左连接！由于用户可能有 0 到 N 个封禁记录，有什么最好的解决方法呢？

非常感谢
Zidsal

<details>
<summary>英文:</summary>

so I have sql that looks like this

    SELECT accounts.id, accounts.username, accounts.password, 
          accounts.created, accounts.last_logged_in, accounts.access,
          banned.reason, banned.expires,
          player.x, player.y, player.zone
    FROM accounts
    LEFT JOIN banned
    ON accounts.id = banned.account_Id
    INNER JOIN player
    ON accounts.id = player.account_Id
    WHERE accounts.username = username

If I wanted to store this in a struct in go I would normally do this:

    type Account struct {
    	Id           int
    	Username     string
    	Password     string
    	Email        string
    	Created      time.Time
    	LastLoggedIn time.Time
    	AccessLevel  int
    	Location     struct {
    		Zone string
    	}
    	Banned []*Banned
    }
    
    type Banned struct {
    	Reason  string
    	Expires time.Time
    }
    reply := new(Account)
    
    stmt, err := this.Database.Prepare((&quot;CALL findUser(?)&quot;))
    	defer stmt.Close()
    
    if err != nil {
    	logger.ERROR.Println(err)
    	return err
    }
    
    err = stmt.QueryRow(args).Scan(&amp;reply.Id, &amp;reply.Username ... you get the idea)

however this is not going work because scan is going to expect a value for every argument and we have left joined onto banned! As the user could have 0 - N bans what’s the best way to tackle this?

Many thanks
Zidsal

</details>


# 答案1
**得分**: 2

我觉得你的例子或问题可能并不是你真正想描述的，因为它们并没有真正解决同一个问题。

根据提供的例子，你有两种不同的类型需要扫描（一个是`Account`，一个是左连接的`Banned`），它们将在结果的每一行中重复出现。所以你只需要在扫描`Account`结构体的同时创建一个新的`Banned`结构体，并将其用于扫描数值，然后将其添加到`Account.Banned`切片中。循环处理每一行，就完成了。

根据你的问题，我认为你的SQL查询可能有些问题：你有多个*账户*，每个账户都有多个*封禁记录*，你希望每一行结果中只有一个账户，并包含该账户的所有封禁记录。为了实现这个目标，你需要在查询中使用`GROUP BY`语句，以便每个账户只有一行结果，然后最聪明的方法是使用`GROUP_CONCAT`将每个封禁记录合并到一个属性中，然后你可以相应地解析它。以下是一个简化的示例：

      SELECT accounts.id, GROUP_CONCAT(banned.id SEPARATOR ',') as bans
      FROM accounts
      LEFT JOIN banned
      ON accounts.id = banned.account_Id
      WHERE accounts.username = username
      GROUP BY accounts.id

你只需要将`bans`列扫描为一个字符串，然后按逗号分割它，等等。在你的情况下，解析会更加复杂，因为你需要在一个列中获取两个值，但原理是相同的。

<details>
<summary>英文:</summary>

I feel that either your example or question isn&#39;t what you really wanted to describe, since they don&#39;t really ask for the same problem.

Reading the provided example, you have two different types to scan into (an `Account`, left join a `Banned`), that will be repeated for each row of the result. So you just have to create a new `Banned` struct at the same time that your `Account` struct and use it to scan the values, then add it to the `Account.Banned` slice. Loop for each row, and you&#39;re done.

Reading your question, I think that your sql query is somewhat wrong: you have multiples *accounts* that each have several *bans*, and you would like to have one account by result row with every ban in it. To do that, you will need to tweak your query with a `GROUP BY` statement to get one row by account, then the smartest way would be to do a `GROUP_CONCAT` to get every ban into one attribute that you could then parse accordingly. Example (overly simplified to better expose principle):

      SELECT accounts.id, GROUP_CONCAT(banned.id SEPARATOR &#39;,&#39;) as bans
      FROM accounts
      LEFT JOIN banned
      ON accounts.id = banned.account_Id
      WHERE accounts.username = username
      GROUP BY accounts.id

You just have to scan the `bans` column to a string, split it around `,`, etc. In your case, the parsing will be more complex, as you need 2 values in one column, but the principle is the same.

</details>