MySQL需要帮助构造查询:将多个表合并为一行 [英] MySQL Need help constructing query: join multiple tables into single row

问题描述

请原谅我是否已经提出并回答了这个问题，我已经搜索并发现了一些看起来很相似的东西，但是我对于SQL的新手知识太多，无法适应他们的需求.如果我不使用正确的术语，也请原谅我，我知道当有人问一个问题并且他们甚至不知道能够问他们需要什么时，这可能会很烦人.

Forgive me if this question has been asked and answered, I've searched and found a few that look similar but I'm too much of a novice with SQL to adapt them to my needs. Also forgive me if I don't use the correct terminology, I know it can be annoying when someone asks a question and they don't even know enough to be able to ask for what they need.

<第二次编辑3月22日:>

好的，我不了解源数据的实际外观，这就是为什么我无法获得想要的东西的原因.感谢@goran推动我将真实的源表发布到此处(我仍然不打算发布该表，因为我太懒了，为了便于查看和保护无辜者，不愿对其进行适当的编辑，但是我应该至少在询问之前将其打印出来).下面是新修订的示例表，但是我仍然没有弄清楚如何实现将所有内容放入一行的最终目标.

Ok, I didn't understand what the source data actually looked like, and that is why I couldn't get what I wanted. Thanks to @goran for pushing me to get the real source tables to post here (which I'm still not going to post because I'm too lazy to redact them as appropriate for easy viewing and protection of the innocent, but I should have at least printed them out before asking). Below are newly revised example tables, but I still haven't figure out how to achieve the end goal of getting everything into a single row.

table_one:

id  name          total
5   John Doe      20

table_two:

id  product_id    price
5   51            17

table_three:

id  text       number
5   Subtotal   17
5   Tax        3
5   Total      20

我正在寻找的东西是这样的:

What I'm looking for is something like this:

id name     total product_id price text     number text number text   number
5  John Doe 20    51         17    Subtotal 17     Tax  3      Total  20

我不再确定以下信息的有效性，但我暂时将其保留在这里，希望它不会使新读者对该问题感到困惑.

I'm no longer certain how valid the information below is, but I'm leaving it here for the moment, hoping it won't confuse new readers of the question too much.

</EDIT 3月22日

我正在帮助一个朋友收集一些数据，并且需要执行查询以使每条记录只显示一行，但是我却得到了多行.这是我现在正在查询的示例(简化了，希望不会太多):

I'm helping a friend gather some data, and need to perform a query that results in a single row per record, but instead I get multiple rows. Here is an example of what I'm querying right now (simplified, hopefully not too much):

SELECT * FROM `table_one` AS t1 
INNER JOIN `table_two` AS t2 ON t1.id = t2.id 
INNER JOIN `table_three` AS t3 ON t1.id = t3.id 
WHERE 1

结果是:

id  text       number
5   Subtotal   17
5   Tax        3
5   Total      20

我需要创建一个查询，其结果类似于以下内容:

What I need is to create a query that results in something more like this:

id  text       number  text  number  text   number
5   subtotal   17      Tax   3       Total  20

任何帮助/指导将不胜感激.

Any assistance/guidance would be much appreciated.

谢谢！

-杰德

推荐答案

我认为您在某处确实简化了太多. 您引用的查询将完全返回您想要的内容. 这是一个示例(从单个表中选择两次给出的情况与您所拥有的类似)

I think you did simplify it too much somewhere. The query you quote would return exactly what you want already. Here's an example (selecting from single table twice gives similar situation as what you have)

mysql> select * from test t1 join test t2 on t1.a = t2.a LIMIT 1,5;
+------+------+------+------+
| a    | b    | a    | b    |
+------+------+------+------+
|    1 |    2 |    1 |    1 | 
|    1 |    1 |    1 |    2 | 
|    1 |    2 |    1 |    2 | 
|    2 |    2 |    2 |    2 | 
|    2 |    2 |    2 |    2 | 
+------+------+------+------+
5 rows in set (0.00 sec)

Mysql可以使用相同的标签来标记结果集列. 我想您原来的查询在选择部分中选择了t1.*.

Mysql has no problem to label the result set columns with same labels. I guess that your original query had select t1.* in select part.

如果您要引用名称不明确的各个字段，则会得到

If you want to refer to individual fields whose names are ambiguous you'll get

mysql> select a from test t1 join test t2 on t1.a = t2.a LIMIT 1,5;
ERROR 1052 (23000): Column 'a' in field list is ambiguous

您必须准确指定所需的内容(列别名是可选的，您也可以执行t1.，t2.).

And you have to specify exactly what you want (column aliases are optional, you can do t1., t2. as well)

mysql> select t1.a first, t2.a second from test t1 join test t2 on t1.a = t2.a LIMIT 1,5;
+-------+--------+
| first | second |
+-------+--------+
|     1 |      1 | 
|     1 |      1 | 
|     1 |      1 | 
|     2 |      2 | 
|     2 |      2 | 
+-------+--------+
5 rows in set (0.00 sec)

编辑22MAR 更改示例数据后，您似乎想要将一张表中的几行变成一张. 这是一个特殊的解决方案(假设您将始终具有税"，总计"和小计"行，并且您仅对这些行感兴趣).

Edit 22MAR After a change in the sample data it seems that you want to turn several rows from one table into one. Here is a particular solution (assuming that you'll always have Tax, Total and Subtotal rows and that you are only interested in these rows).

SELECT t1.id, t1.name, t2.product_id, t2.price, t3a.number subtotal, t3b.number total, t3c.number tax
FROM `table_one` AS t1 
INNER JOIN `table_two` AS t2 ON t1.id = t2.id 
INNER JOIN `table_three` AS t3a ON t1.id = t3a.id and t3a.text = "Subtotal"
INNER JOIN `table_three` AS t3b on t3a.id = t3b.id and t3b.text = "Total"
INNER JOIN `table_three` AS t3c on t3b.id = t3c.id and t3c.text = "Tax"

(如果需要，还可以在选择部分中选择常量"Tax"，"Total"和"Subtotal"，并为其指定一些列名称)

(if you want you can also select constants "Tax", "Total" and "Subtotal" in the select part and give them some column names)

尚不清楚的是表中ID之间的关系-它们是table_one还是table_two的主键.当然，当table_one和table_two中有多行时，这可能会影响结果.

One thing that remains unclear is the relationship between id's in tables - are they primary key of table_one or table_two. That can influence the results, of course, when you will have multiple rows in table_one and table_two.

这篇关于MySQL需要帮助构造查询:将多个表合并为一行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！