使用多列对不同的select进行计数 [英] Count rows on distinct select with multiple columns

问题描述

我有一个MsSQL，看起来像这样：

$ b从
（从dbo.InvoiceTypeBean
中选择不同的supplierName，supplierNr
）中选择count（*）作为$（
$ b pre $ b $ b

这会返回我想要使用纯SQL的数据。

但是我在hibernate中需要使用条件和/或detachedcriteria：

分离标准部分：

  DetachedCriteria dCriteria = DetachedCriteria.forClass（clazz）; 
 ProjectionList p = Projections.projectionList（）; 
 p.add（Projections.property（supplierName））; 
 p.add（Projections.property（supplierNr））; 
 dCriteria.setProjection（Projections.distinct（p））; 
  

问题在于附加到条件：

  Criteria criteria = session.createCriteria（clazz）; 
 ....一些atachement 
 criteria.setProjection（Projections.rowCount（））; 
 int count =（（Number）criteria.uniqueResult（））。intValue（）; 
  

我真的需要使用Criteria和/或DetachedCriteria的解决方案，因为查询是动态构建的，使用它们构建更好的解决方案。

解决方案

好的找到了解决方案，我真正需要的只是在多列上使用不同的输出行计数已经被指定更好，我知道）。

感兴趣的解决方案：

  session.beginTransaction（）; 
 Criteria criteria = session.createCriteria（clazz）; 
 
 ProjectionList p = Projections.projectionList（）; 
 p.add（Projections.property（supplierName））; 
 p.add（Projections.property（supplierNr））; 
 criteria.setProjection（Projections.distinct（p））; 
 
 criteria.setResultTransformer（Transformers.TO_LIST）; 
 
 ScrollableResults results = criteria.scroll（）; 
 results.last（）; 
 count = results.getRowNumber（）+ 1; 
 results.close（）; 
 session.getTransaction（）。commit（）; 
  

其中有趣的部分是ScrollableResult并且没有使用DetachedCriteria。

I have been searching for some time but not found enough answers to do this myself.

I have an MsSQL that looks like this:

select count(*) from 
   ( select distinct supplierName, supplierNr
   from dbo.InvoiceTypeBean
   ) as a

This returns what I want using pure SQL

But i need this in hibernate using criteria and/or detachedcriteria:

The detached criteria part:

DetachedCriteria dCriteria = DetachedCriteria.forClass(clazz);
ProjectionList p = Projections.projectionList();
p.add(Projections.property("supplierName"));
p.add(Projections.property("supplierNr"));
dCriteria.setProjection(Projections.distinct(p));

The problem is attaching it to the criteria:

Criteria criteria = session.createCriteria(clazz);
.... Some atachement
criteria.setProjection(Projections.rowCount());
int count = ((Number) criteria.uniqueResult()).intValue();

I really need the solution to use Criteria and/or DetachedCriteria as the queries are being build dynamically, and a greater solution is build using them.

解决方案

Well found the solution, what I really needed was just the row count of the output when using distinct on multiple columns (could have been specified better I know).

The solution for interested:

session.beginTransaction();
Criteria criteria = session.createCriteria(clazz);

ProjectionList p = Projections.projectionList();
p.add(Projections.property("supplierName"));
p.add(Projections.property("supplierNr"));
criteria.setProjection(Projections.distinct(p));

criteria.setResultTransformer(Transformers.TO_LIST);

ScrollableResults results = criteria.scroll();
results.last();
count = results.getRowNumber() + 1;
results.close();
session.getTransaction().commit();

Where the interesting part is the scrollableresult and no use of DetachedCriteria.

这篇关于使用多列对不同的select进行计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！