问题是sttable
每个mttable
记录可能有多个记录。因此,您只需要DISTINCT
一个即可GROUP BY
。
我会为内部选择尝试以下内容:
SELECT ROW_NUMBER() OVER(ORDER BY " + orderField + @") AS RowNum,
mt.ID AS mt_ID,
mt.title AS mt_title,
[...]
MAX(st.title) AS st_title,
-- Other aggregates (MAX, MIN, AVERAGE, ...) for all other columns
-- from sttable, whatever is appropriate.
[...]
FROM mttable AS mt
INNER JOIN sttable AS st on mt.ID =st.ID
WHERE st.field <> 0 AND mt.title = @title
GROUP BY mt.ID,
mt.title
-- Group by everything else from mttable.