开发者

Need Help With Hibernate/SQL Query Count Logic

开发者 https://www.devze.com 2022-12-24 02:06 出处:网络
I have the following query: SELECT DISTINCT w.name, count(*) FROM widgets AS w JOIN w.entity AS e JOIN e.article AS a

I have the following query:

SELECT DISTINCT w.name, count(*) FROM widgets AS w
JOIN w.entity AS e
JOIN e.article AS a
JOIN a.document AS d
WHERE d.id IN (<document ids>)
GROUP BY w.name

The problem is that count(*) returns the number of widget-entity associations. Instead, what I'm looking for is the number of unique widget names, per article or document.

Here's an example:

Let's say that I have two entities: cat and dog.

I also have two distinct widgets that share the same name, foo.

Each of the foo widgets is associated with one of the entities (one with cat, the other with dog).

Both cat and dog are associated with the article animals.

I'd like the count returned by this query to be开发者_StackOverflow中文版 only 1 (because the widget name foo is only found once in the article animals), instead of 2.

I've tried placing other things in the count clause, like count(d.id), with no luck.


Aha, I figured it out!

Since I'm using MSSQL in the backend, I can do this: count(DISTINCT a.id)

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号