在sql中选择一个到多个

Well i have two tables first for employees and the second for emails, i want to see the employees with all his emails

我有两个表格，一个是给员工的，另一个是发邮件的，我想看到所有的员工都发邮件

table employees:
id | name  | idE
 1 | name1 | 1
 2 | name2 | 2
 3 | name3 | 3

table email:
id | idE | email
 1 | 1   | [e-mail]
 2 | 1   | [e-mail]
 3 | 2   | [e-mail]
 4 | 3   | [e-mail]

and i have this query:

我有一个问题:

select e.id, e.name, m.email from employees as e inner join email as m on p.idE = m.idE

and this is the result:

结果是:

id | name  | email
 1 | name1 | [e-mail]
 1 | name1 | [e-mail]
 2 | name2 | [e-mail]
 3 | name3 | [e-mail]

i dont need employee 1 twice i just need both emails in the same row

我不需要员工1两次，我只需要同排的两封邮件

Hi guys i modified my query to: select em.name, STUFF( (select ',' + e.Email from tbl_Employees as m inner join tbl_Email as e on m.idE = e.idE for xml path('')) ,1,1,'') as emails from tbl_employees as em

大家好，我将查询修改为:select em.name, STUFF(select '，' + e。来自tbl_Employees的电子邮件作为m的内部连接tbl_Email作为m的e。idE = e。idE用于xml路径(“)，1,1，”)作为来自tbl_employees的电子邮件作为em

but now my problem is that i get this in each column of email:

但现在我的问题是，我在每一列邮件中都有:

[e-mail],[e-mail],[e-mail],[e-mail],

e1.com,e2.com,e2.com,e3.com,

how can i do it to get only the emails of each employee?

我怎么才能只收到每个员工的邮件呢?

instead of having every single email in each column

而不是每个专栏都有每一封邮件

for example:

例如:

name | email
name1 | [e-mail],[e-mail],
name2 | [e-mail],
name3 | [e-mail], 

pd: sorry if i have some mistakes im still learning english

对不起，如果我有什么错误，我还在学习英语

2 个解决方案

#1

Although what you're asking for is possible, and I'm sure you'll get several answers, your question is a good example of be careful what you wish for ! You're asking the DBMS to supply some presentation-level functionality. You're usually better off letting SQL find the data you need, and handling presentation issues in your application.

尽管你所要求的是可能的，我相信你会得到几个答案，你的问题是一个很好的例子，说明你要小心你的愿望!您要求DBMS提供一些表示级功能。通常最好让SQL找到所需的数据，并在应用程序中处理表示问题。

SELECT returns a table of information, and the value of each column in a row is normally "atomic" in the sense that it holds a single value that SQL can compare to other values. The tables in your database have the same property if they adhere to first normal form.

SELECT返回一个信息表，并且行中每个列的值通常是“原子的”，因为它包含SQL可以与其他值进行比较的单个值。如果数据库中的表遵循第一范式，那么它们具有相同的属性。

One of the virtues of SQL is that "everything is a table": you can stack SELECTs one atop the other.

SQL的优点之一是“一切都是一个表”:您可以将一个选择堆栈在另一个上。

select * from (select a, b from T where a > b) as Z

Each inner one produces another table that the next outer one can act on.

每个内部的一个生成下一个外部的一个可以操作的另一个表。

Functions like group_concat (as an example) produce a non-atomic column. It's only a short step from there to code like:

group_concat之类的函数(例如)生成一个非原子列。从这里到代码只有一步之遥:

select * from ( select group_concat(a) as a_list from T ) as Z
where a_list like '%Henry%'

The programmer is forced into search strings instead of direct comparison. The query becomes less precise and thus error-prone.

程序员被强制搜索字符串而不是直接比较。查询变得不那么精确，因而容易出错。

The fail-safe approach, in your example, is to read the results one by one from the DBMS, and concatenate a per-user address list by watching the idE column. It will keep your query simpler, and if you later decide to present them another way, you can do that without diving into the database logic.

在您的示例中，故障安全方法是从DBMS中逐个读取结果，并通过查看idE列连接每个用户的地址列表。它将使您的查询更简单，并且如果您稍后决定以另一种方式呈现它们，您可以在不深入数据库逻辑的情况下实现它。

#2

this is my query after the changes

这是我在更改后的查询

select em.name,
STUFF( 
  (select  ',' + e.Email from tbl_Employees as m inner join 
  tbl_Email as e on m.idE = e.idE 
  where em.idE = e.idE
  for xml path('')
) ,1,1,'') as emails 
from tbl_employees as em

and the results of this query

以及这个查询的结果

name | emails
name1 | [e-mail],[e-mail]
name2 | [e-mail]
name3 | [e-mail]

thank you for the answers!

谢谢你的回答!