如何在JAVA中查询两个数据相似的表并按表字段分组结果？

Question

我正在尝试在某个系统中执行分析。系统应该通过查询数据库中的两个或多个表来执行分析，然后显示按字段分组的总结果。

这是一个基于 Java 的系统，使用 SQL 作为其数据库。我试图使用联盟，联合所有，但它没有给出令人满意的结果。

下表1

id    itemID     ItemName     entryTime

1      GAR001     PhoneAG       ""

2      GAR004     PCLG          ""

3      GAR009     ELECT         ""    

...

下表2

id    itemID     ItemName     entryTime    issuedate

1      GAR001     PhoneAG       ""           ""

2      GAR001     PhoneAG       ""           ""

3      GAR009     ELECT         ""           ""    

...

我尝试使用以下

public ObservableList<PieChart.Data> getItemAnalysis(){

        ObservableList<PieChart.Data> data = FXCollections.observableArrayList();

        String st1 = "SELECT itemID, COUNT(itemID) FROM table1 GROUP BY itemID UNION ALL SELECT itemID, COUNT(itemID) FROM table2 GROUP BY itemID";

        System.out.println(st1);

        ResultSet rs = execQuery(st1);

        try{

            if(rs.next()){

                String itemId = rs.getString("itemID");

                int count = rs.getInt(2);

                data.add(new PieChart.Data(itemId + "(" + count + ")", count));

            }

        } catch (SQLException ex) {

            Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);

        }

        return data;

    }

结果返回时。它返回

GAR001(1),

GAR004(1),

GAR009(1),

GAR001(2),

GAR009(1).

我期望结果返回是

GAR001(3)

GAR004(1)

GAR009(2)

Answer 1

您正在两个分组查询之间应用 。相反，您应该使用原始数据，然后对其进行分组：union allunion allSELECT&nbsp; &nbsp;itemID, COUNT(*)FROM&nbsp; &nbsp; &nbsp;(SELECT itemID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM&nbsp; &nbsp;table1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; UNION ALL&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SELECT itemID&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; FROM&nbsp; &nbsp;table2) tGROUP BY itemID

Answer 2

您实际上需要编辑SQL语句，这就是您获得这些结果的原因。@Mureinik发布了正确的 SQL。我继续将其插入到您的代码中，以便为您提供一个应该有效的解决方案。public ObservableList<PieChart.Data> getItemAnalysis(){&nbsp; &nbsp; &nbsp; &nbsp; ObservableList<PieChart.Data> data = FXCollections.observableArrayList();&nbsp; &nbsp; &nbsp; &nbsp; sql = "SELECT itemID, COUNT(*) FROM (SELECT itemID FROM table1 UNION ALL SELECT itemID FROM table2) t GROUP BY itemID"&nbsp; &nbsp; &nbsp; &nbsp; System.out.println(sql);&nbsp; &nbsp; &nbsp; &nbsp; ResultSet rs = execQuery(sql);&nbsp; &nbsp; &nbsp; &nbsp; try{&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; if(rs.next()){&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; String itemId = rs.getString("itemID");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; int count = rs.getInt(2);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; data.add(new PieChart.Data(itemId + "(" + count + ")", count));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; } catch (SQLException ex) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Logger.getLogger(DatabaseHandler.class.getName()).log(Level.SEVERE, null, ex);&nbsp; &nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; &nbsp; return data;&nbsp; &nbsp; }

Answer 3

您应该使用子查询SELECT DISTINCT itemID , COUNT(*) AS COUNT&nbsp;&nbsp;FROM (SELECT itemID&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;FROM table1&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;UNION ALL&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;SELECT itemID&nbsp;&nbsp; &nbsp; &nbsp; &nbsp;FROM table2) as T&nbsp;&nbsp;GROUP BY itemID;