用Java排序SQL查询结果

Firstly my apologies for putting some large text here.

首先，我很抱歉在这里放了一些大段文字。

Below is the result by executing SQL query in Java but I want it to short it out using timestamp.

下面是在Java中执行SQL查询的结果，但我希望它使用时间戳缩短它。

For eg. TimeStamp in the below 1st line text is - 040501(HH:mm:ss) (like wise all data contains timestamp) (Sorting should be done by using timestamp parameters) (Each line given below is single row from database)

如。第一行文本中的时间戳为- 040501(HH:mm:ss)(就像wise all data contains TimeStamp)(排序应该使用时间戳参数)(下面给出的每一行都是来自数据库的一行)

I am executing 3 queries one by one in java.once i executed first query i am writing to text file and then executing 2nd query, writing to same text file.. likewise i am doing. In that case how to sort it out.

我正在用java一个一个地执行3个查询。一旦我执行了第一个查询，我将写入文本文件，然后执行第二个查询，写入相同的文本文件。同样我做。在那种情况下，如何解决它。

12010051104050131331GZM4         7000000           1    FCFR

120100511040501912828MP2        11590000           0    NOTY

120100511040501312938VF7          366140   .96808795    FGPC

120100511040501912828KA7         6580000           0    NOTY

120100511040501912828JH4          490000           0    NOTY

120100511160528912810PV4        83227500     1.03581    TRIB

120100511160538912795W31               0           1    BILL

120100511160540912828MP2       455784400           0    NOTY

120100511160545912795W31               0           1    BILL

  220100511 040501         2101000

  220100511 040501        51037707

  220100511 040502          700149

  220100511 040502         4289000

  220100511 060514        71616600

  220100511 060514       722453500

the result i would expect is...

我期望的结果是……

 12010051104050131331GZM4         7000000           1    FCFR

 120100511040501912828MP2        11590000           0    NOTY

 120100511040501312938VF7          366140   .96808795    FGPC

 120100511040501912828MP2        11590000           0    NOTY

 120100511040501912828JH4          490000           0    NOTY

  20100511040501         2101000

  20100511040501        51037707

  20100511040502         4289000

  20100511040502          700149

  20100511060514       722453500

  20100511060514        71616600

  20100511160528912810PV4        83227500     1.03581    TRIB

  20100511160538912795W31               0           1    BILL

  20100511160540912828MP2       455784400           0    NOTY

  20100511160545912795W31               0           1    BILL

Please help me out guys. i am fighting for this very long time. Thanks for your help in advance.

请帮帮我，伙计们。我为此奋斗了很长一段时间。提前谢谢你的帮助。

4 个解决方案

#1

Ideally, the table would have a TIMESTAMP column that you can use to ORDER BY in the SQL query; that would be much better than sorting in Java.

理想情况下，表应该有一个时间戳列，您可以在SQL查询中使用该列进行排序;这比用Java进行排序要好得多。

It's not obvious to me what the structure of your table is right now, what the columns are, etc. If they aren't normalized properly, then your best course of action would be to do that first, instead of keeping it like a mess that it is and make queries miserable.

对我来说，现在表的结构是什么、列是什么等等都不明显。如果它们没有被正确地规范化，那么最好的做法是先这样做，而不是把它弄得一团糟，让查询变得很糟糕。

#2

Best option is to use ORDER BY in the SQL query. Because, you access one element in a result set at once. So, get all the elements and write to text file with out doing any operations.

最好的选择是在SQL查询中使用ORDER BY。因为，您一次访问结果集中的一个元素。因此，获取所有的元素并写入文本文件，而不进行任何操作。

#3

So you data is a single column which contains a string with data as well as timestamp?

所以你的数据是一个列包含有数据和时间戳的字符串?

Is it fixed width? I mean eg: does timestamp always start at 10th character and end at 15th position?

固定宽度吗?我的意思是时间戳总是从第10个字符开始，到第15个字符结束吗?

This looks like output from some legacy Mainframe Application, they usually have fixed width formats..

这看起来像一些遗留大型机应用程序的输出，它们通常有固定的宽度格式。

Assuming that's the case, you can write a small wrapper class and make objects of that class from the resultset of your queries. Your wrapper class should also implement comparable (where you would do implement the compare method based on the extracted timestamp).

假设是这样，您可以编写一个小包装器类，并从查询的resultset中创建该类的对象。您的包装器类也应该实现可比(在这里，您将实现基于提取的时间戳的比较方法)。

Then all you need to do is put all your objects in any java collection like List, and do a collection.sort().

然后，您只需将所有对象放入任何java集合(如List)中，并执行collection.sort()。

#4

Whether you do the sort in SQL or Java, first you must be able to reliably identify and extract the timestamp. The format with which you've displayed the data rows suggest that they could be treated as fixed length records, so you could read them a record at a time into a class that might implement the Comparable interface, in which you might use a substring to slice out the timestamp text, create real dates or timestamps from that text and compare them. Then you could use the standard libraries to sort either the rows in a collection or array. Personally, I'd still do the sort in SQL, via a similar mechanism, assuming you are getting all your data from a single database and are using a fairly sophisticated DB you could create a single result by unioning your 3 queries together, do a calculated field for the timestamp, then order by that calculated field. Some DBs don't allow direct sorting by calculated fields, but then you can use it as an inline view, like "select field, calcfield from (big select with calculated field) order by calcfield". hth

无论使用SQL还是Java进行排序，首先必须能够可靠地识别和提取时间戳。你的格式显示数据行表明,他们可以被视为固定长度的记录,所以你可以阅读记录一次到一个可能实现类似接口的类,你可能使用一个字符串切出时间戳的文本,从文本创建真正的日期或时间戳和比较。然后可以使用标准库对集合或数组中的行进行排序。就我个人而言,我仍然在SQL中,通过一个类似的机制,假设你得到所有数据从一个数据库,并使用一个相当复杂的数据库可以通过联盟3查询创建一个结果在一起,做一个计算时间戳字段,然后命令计算字段。有些DBs不允许通过计算字段进行直接排序，但是您可以将它用作内联视图，如“select field, calcfield from (big select with computed field) by calcfield”。hth

#1