如何在SQL表中分离数据 [英] How to separate data in SQL table

问题描述

我有一个带数据记录的.csv文件，每行包含组织部门.我想将它们导入MySQL数据库并在Web应用程序中显示它们.数据记录的样本摘要如下所示:

| Company A | Department aaa |
| Company A | Department bbb |
| Company A | Department ccc |
| Company B | Department ddd |
| Company B | Department eee |
| Company C | Department fff |
| Company C | Department ggg |
| Company C | Department hhh |
| Company D | Department iii |
| Company E | Department jjj |
| Company E | Department kkk |

我当然想给每个公司展示一次相关部门，这使它看起来或多或少都是这样的:

| Company A | Department aaa |
|           | Department bbb |
|           | Department ccc |
| Company B | Department ddd |
|           | Department eee |
| Company C | Department fff |
|           | Department ggg |
|           | Department hhh |
| Company D | Department iii |
| Company E | Department jjj |
|           | Department kkk |

我的问题是，串联这些数据记录的最佳方法是什么?您将使用适当的编程语言(例如PHP，Ruby，Perl等)来执行此操作，还是使用SQL查询在数据库中执行此操作?如果选择SQL，您是否会使用存储过程，临时表甚至在导入接口上使用SQL?如果是SQL，它将是什么样?谢谢！

解决方案

您必须将关联公司及其部门存储在数据库的每一行上.由于没有对数据库中的行进行隐式排序"的事情，因此，行无法将其公司与前"行相同(前"没有意义).

在查询结果集中的行上删除多余的公司名称是不值得的.即使看起来多余，只要让它们出现在每一行上即可.

然后在您的应用程序中，当您展示数据时，您要做就有机会按显示顺序对其进行处理.因此，您可以执行以下操作(伪代码):

query 'SELECT Company, Department FROM MyTable ORDER BY Company, Department'

prev_company = ''

while row = fetch
do
    if row.company == prev_company
        display ''
    else
        display row.company
        prev_company = row.company

    display row.department
done

I have a .csv file with data records, each line containing departments of organisations. I would like to import them into a MySQL database and show them in a web application. A sample abstract of data records looks like this:

| Company A | Department aaa |
| Company A | Department bbb |
| Company A | Department ccc |
| Company B | Department ddd |
| Company B | Department eee |
| Company C | Department fff |
| Company C | Department ggg |
| Company C | Department hhh |
| Company D | Department iii |
| Company E | Department jjj |
| Company E | Department kkk |

Of course I want to show each company once with its related departments attached, which makes it look this way, more or less:

| Company A | Department aaa |
|           | Department bbb |
|           | Department ccc |
| Company B | Department ddd |
|           | Department eee |
| Company C | Department fff |
|           | Department ggg |
|           | Department hhh |
| Company D | Department iii |
| Company E | Department jjj |
|           | Department kkk |

My question is, what is the best way to concatenate these data records? Would you do it using the appropriate programing language (ie PHP, Ruby, Perl, whatever) or would you do it in the database using SQL query? If SQL is the choice, would you do it using stored procedures, temp tables, or even at the import interface? What would it look like if it was SQL? Thank you!

解决方案

You must store the associated company with its department on every row in the database. Since there is no such thing as implicit "order" to rows in the database, there's no way for a row to treat its company as the same as on the row "before" (there is no meaning to "before").

It's not worth the trouble to blank out the redundant company name on rows of a query result set. Just let them appear on every row even though it looks redundant.

Then in your application, as you are presenting the data, you do have an opportunity to process them in order as you display them. So you'd do something like this (pseudocode):

query 'SELECT Company, Department FROM MyTable ORDER BY Company, Department'

prev_company = ''

while row = fetch
do
    if row.company == prev_company
        display ''
    else
        display row.company
        prev_company = row.company

    display row.department
done

这篇关于如何在SQL表中分离数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！