java求和结果值与case语句重复
我使用posgresql作为数据库,java作为hibernate的编程语言。我的问题是这个问题:
select cast(sum(CASE WHEN p.nropack > 0 THEN p.nropack ELSE 0 END) as integer),
cast(sum(CASE WHEN p.nropack < 0 THEN p.nropack ELSE 0 END) as integer),
cast(p.fechareg as date)
from pronostico p
inner join aeropuerto a on (a.idaeropuerto=p.idaeropuerto)
inner join ciudad c on (a.idciudad=c.idciudad)
inner join pais ps on (ps.idpais=c.idpais)
inner join continente ct on (ct.idcontinente=ps.idcontinente)
where c.idciudad=105
group by cast (p.fechareg as date);
因此,我得到:
sum;sum;fechareg
30;-15;"2012-11-15"
但当我在程序中使用它时:
public ArrayList<RepKardex> listarKardex(int ciud){
ciud=105;
ArrayList<RepKardex> listaKardex = new ArrayList<>();
Session session = HibernateUtil.getSessionFactory().openSession();
Transaction tx = session.beginTransaction();
String q = "select cast(sum( case when p.nropack > 0 then p.nropack ELSE 0 end ) as integer), "
+ "cast(sum( case when p.nropack < 0 then p.nropack ELSE 0 end ) as integer), "
+ "cast(p.fechareg as date) "
+ "from Pronostico p "
+ "inner join Aeropuerto a on (p.idaeropuerto = a.idaeropuerto) "
+ "inner join Ciudad c on (a.idciudad = c.idciudad) "
+ "inner join Pais ps on (c.idpais = ps.idpais) "
+ "inner join Continente ct on (ct.idcontinente = ps.idcontinente) "
+ "where c.idciudad = :ciud "
+ "group by cast(p.fechareg as date) ";
Query query = session.createSQLQuery(q);
query.setInteger("ciud", ciud);
List lista = query.list();
Iterator iter = lista.iterator();
while (iter.hasNext()) {
Object[] row = (Object[]) iter.next();
if (row!=null){
System.out.println("entrantes : "+(Integer)row[0]);
System.out.println("salientes : "+(Integer)row[1]);
RepKardex rep = new RepKardex((int)row[0],(int)row[1],(Date)row[2]);
listaKardex.add(rep);
}
}
tx.commit();
session.close();
return listaKardex;
}
它打印
entrantes: 30
salida: 30
有人能帮我弄清楚为什么即使在查询中使用case语句,它也会重复正数吗?提前谢谢
# 1 楼答案
您可以使用^{} and ^{} 简化查询:
除此之外,它看起来还不错。我看第二列不可能返回正数。这里肯定出了什么问题
编辑:
Comment by @hvd帮助发现,客户机代码似乎被相同的列名(两个sum列都默认为
"sum"
)搞混了。显式列别名似乎可以解决这个问题