在现有查询中实现“ istinct”选择

一尘不染

在现有查询中实现“ istinct”选择

sql

我有一个现有的，相当长的SQL查询。我想选择具有不同mt.ID的记录。我尝试在不同的地方插入“ SELECT
DISTINCT”，但没有成功。谁能告诉我应该去哪里？非常感谢。

SELECT *
FROM (select ROW_NUMBER() OVER(ORDER BY " + orderField + @") as RowNum,
              mt.ID as mt_ID,
              mt.title as mt_title,
              [...]
              st.title as st_title,
              [...]
    from  mttable as mt 
    inner join sttable as st on mt.ID =st.ID
    where NOT (st.field=0) AND where mt.title = @title" )
as DerivedTableName
WHERE RowNum between ((@pageIndex - 1) * @pageSize + 1) and @pageIndex*@pageSize

阅读 146

2021-05-16

共1个答案

一尘不染

问题是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.

2021-05-16