我正在嘗試在某個系統(tǒng)中執(zhí)行分析。系統(tǒng)應(yīng)該通過查詢數(shù)據(jù)庫中的兩個或多個表來執(zhí)行分析，然后顯示按字段分組的總結(jié)果。這是一個基于 Java 的系統(tǒng)，使用 SQL 作為其數(shù)據(jù)庫。我試圖使用聯(lián)盟，聯(lián)合所有，但它沒有給出令人滿意的結(jié)果。下表1id itemID ItemName entryTime1 GAR001 PhoneAG ""2 GAR004 PCLG ""3 GAR009 ELECT "" ...下表2id itemID ItemName entryTime issuedate1 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; }結(jié)果返回時。它返回GAR001(1),GAR004(1),GAR009(1),GAR001(2),GAR009(1).我期望結(jié)果返回是GAR001(3)GAR004(1)GAR009(2)
3 回答
冉冉說
TA貢獻(xiàn)1877條經(jīng)驗 獲得超1個贊
您正在兩個分組查詢之間應(yīng)用 。相反，您應(yīng)該使用原始數(shù)據(jù)，然后對其進(jìn)行分組：union allunion all
SELECT itemID, COUNT(*)
FROM (SELECT itemID
FROM table1
UNION ALL
SELECT itemID
FROM table2) t
GROUP BY itemID
絕地?zé)o雙
TA貢獻(xiàn)1946條經(jīng)驗 獲得超4個贊
您實際上需要編輯SQL語句，這就是您獲得這些結(jié)果的原因。@Mureinik發(fā)布了正確的 SQL。我繼續(xù)將其插入到您的代碼中，以便為您提供一個應(yīng)該有效的解決方案。
public ObservableList<PieChart.Data> getItemAnalysis(){
ObservableList<PieChart.Data> data = FXCollections.observableArrayList();
sql = "SELECT itemID, COUNT(*) FROM (SELECT itemID FROM table1 UNION ALL SELECT itemID FROM table2) t GROUP BY itemID"
System.out.println(sql);
ResultSet rs = execQuery(sql);
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;
}
郎朗坤
TA貢獻(xiàn)1921條經(jīng)驗 獲得超9個贊
您應(yīng)該使用子查詢
SELECT DISTINCT itemID , COUNT(*) AS COUNT
FROM (SELECT itemID
FROM table1
UNION ALL
SELECT itemID
FROM table2) as T
GROUP BY itemID;
添加回答
舉報
0/150
提交
取消