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
.
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)
精彩评论