mysql - 使用 mysql 和 java 对所有查询求和并联合

Question

我有一个包含以下字段的表Service_type和consumer_feedback。例如:

Service_ID  consumer_feedback
31           1
32           -1
33            1
31            1
32            1.

我想通过java代码找到每个Service_ID的consumer_feedback总和

ResultSet res = st.executeQuery("SELECT SUM(consumer_feedback) 
                                   FROM  consumer1 
                                  where Service_ID=31 
                                 union  
                                 SELECT SUM(consumer_feedback)  
                                   FROM consumer1 
                                  where Service_ID=32 
                                 union  
                                 SELECT SUM(consumer_feedback)  
                                   FROM consumer1 
                                 where Service_ID=33") ;
    while (res.next()) {
      int c1 = res.getInt(1);           
      sum1 = sum1 + c1;        
    }

    System.out.println("Sum of column "   +sum1);

    while (res.next()) {
      int c2 = res.getInt(1);           
      sum2 = sum2 + c2;                  
    }

    System.out.println("Sum of column "   +sum2);

    while (res.next()) {
      int c3 = res.getInt(1);
      sum3 = sum3 + c3;
    }

    System.out.println("Sum of column "   +sum3);
}

但是此代码适用于 2 个 Service_ID，而不适用于 3 个 Service_ID。请帮助我

Answer 1

您的查询应该是

select service_id,sum(consumer_feedback) from consumer1 group by service_id

这将消除建立工会的需要。