我可以将两行的结果合并为一吗? [英] Can I take the results from two rows and combine them into one?

问题描述

我在 mysql 数据库中有 2 行数据.除了一列之外，两行都相同.我正在寻找的是一个查询，它将生成具有两个不同值的单行.有人可以帮我吗?

I have 2 rows of data in a mysql database. Both rows are identical with the exception of one column. What I am looking for is a query that will produce a single row with both distinct values. Can someone please help me out?

数据的一个例子是:

1    Administrator    Test    2009-08-17 03:57:35
1    Miller           Test    2009-08-17 03:57:35

我需要的是这个:

1    Administrator    Miller    Test    2009-08-17 03:57:35

DISTINCT 不会给我.我需要添加或附加到结果集的附加列.

DISTINCT won't give it to me. I need the additional column added or appended to the result set.

这是我要使用的代码.

SELECT
t2.msg_id,
t3.lname AS sender,
t4.lname AS recipient
FROM
mail_message AS t1
Inner Join mailbox AS t2 ON t1.msg_seq = t2.msg_seq
Inner Join employee AS t3 ON t3.employee_id = t2.employee_id
INNER JOIN employee AS t4 ON t3.employee_id = t4.employee_id

推荐答案

好的.您可以使用如下技术JOIN 将表连接到其自身的副本上:

Sure. You can JOIN the table onto a copy of itself, with a technique something like this:

SELECT t1.*, t2.name
FROM the_table AS t1
INNER JOIN the_table AS t2 ON (t1.id = t2.id AND t1.test = t2.test AND t1.date_column = t2.date_column AND t1.name < t2.name)

这里我们任意命名表的一个副本为 t1 和另一个 t2 并连接除了名称之外相同的行.

Here we arbitrarily name one copy of the table t1 and the other t2 and join rows that are identical except for the name.

请注意，我不会简单地对 name 进行 != 测试，因为这会导致两个匹配的行:其中一个 where Administrator 在 t1 中，另一个 Administrator 在 t2 中.为了将一个相同的表与另一个区分开来，我断言 t1 将始终是具有较小"(即，按字母顺序更早)名称的表.

Note that I do not simply do a != test on the name, since that would result in two matching rows: one where Administrator is in t1 and the other where Administrator is in t2. To distinguish one identical table from the other, I assert that t1 will always be the one with a "smaller" (i.e., earlier alphabetically) name.

如果可以有三个或更多相同的行，这仍然有效，但会返回多个匹配项.例如，如果 A、B 和 C 都是名称相同的行，您将得到:

If there can be three or more identical rows, this will still work, but will return several matches. For example, if A, B, and C are all names in otherwise identical rows, you'd get:

A B
B C
A C

这篇关于我可以将两行的结果合并为一吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！