mysql - 从两个表中选择相同的列值

Question

我正在做一个关于 struts 的项目，其中我想比较两个表的行值，并希望获得匹配的列作为结果集，并且我想从结果集中删除所有匹配的列。我正在做以下编码，但我总是得到 3 作为 columnsNumber。请帮我得到准确的结果。

public class ResultDAO {


 public int correctAnswer() throws Exception {

     int columnsNumber=0;

     Connection con=null;       
     PreparedStatement pstmt = null;

     ResultSet rs = null;
     String query="SELECT DISTINCT q1,q2,q3 FROM test WHERE (q1,q2,q3)  IN (SELECT q1,q2,q3 FROM result)ORDER BY q1,q2,q3 ASC";
     try {

         con=DatabaseConnection.getConnection();

         pstmt = con.prepareStatement(query);

         rs = pstmt.executeQuery();

         ResultSetMetaData rsmd = (ResultSetMetaData) rs.getMetaData();

         columnsNumber = rsmd.getColumnCount();
         System.out.println(columnsNumber);


    } 
     catch (Exception e) {
         System.out.println("exception in DAO");
    }
    return columnsNumber;


 }
}

表1:测试

列:id、q1、q2、q3 值:1、A、C、C

表2:结果

列:id、q1、q2、q3 值:1、A、B、C

我正在使用这段代码，但得到的值为 null

公共(public)类 ResultDAO {

 public String correctAnswer() throws Exception {

     String columnsNumber = null;

     Connection con=null;       
     PreparedStatement pstmt = null;

     ResultSet rs = null;
     String query="SELECT test.id,IF (test.q1 = result.q1, test.q1, NULL) as q1,IF (test.q2 = result.q2, test.q2, NULL) as q2,IF (test.q3 = result.q3, test.q3, NULL) as q3,(test.q1 = result.q1) + (test.q2 = result.q2) + (test.q3 = result.q3) as matchedColumns FROM test INNER JOIN result USING (id) ORDER BY q1,q2,q3";
     try {

         con=DatabaseConnection.getConnection();

         pstmt = con.prepareStatement(query);

         rs = pstmt.executeQuery();

         System.out.println(rs.next());
         while(rs.next()){
        columnsNumber=rs.getString("matchedColumns");


         }
         System.out.println(columnsNumber);


    } 
     catch (Exception e) {
         System.out.println("exception in DAO");
    }
    return columnsNumber;


 }

Answer 1

请试试这个sqlFiddle

SELECT test.id,
       IF (test.q1 = result.q1, test.q1, NULL) as q1,
       IF (test.q2 = result.q2, test.q2, NULL) as q2,
       IF (test.q3 = result.q3, test.q3, NULL) as q3,
       (test.q1 = result.q1) + (test.q2 = result.q2) + (test.q3 = result.q3) as matchedColumns
FROM test
INNER JOIN result
USING (id)
ORDER BY q1,q2,q3

编辑:要获得不匹配的列，只需将 = 符号更改为 != 并添加另一个名为 unmatchedColumns sqlFiddle 的已创建列

SELECT test.id,
       IF (test.q1 = result.q1, test.q1, NULL) as q1,
       IF (test.q2 = result.q2, test.q2, NULL) as q2,
       IF (test.q3 = result.q3, test.q3, NULL) as q3,
       (test.q1 = result.q1) + (test.q2 = result.q2) + (test.q3 = result.q3) as matchedColumns,
       (test.q1 != result.q1) + (test.q2 != result.q2) + (test.q3 != result.q3) as unmatchedColumns
FROM test
INNER JOIN result
USING (id)
ORDER BY q1,q2,q3