sql - 在 INNER JOIN SQL Server 上获取 DISTINCT 记录
问题描述
我有两张桌子。INNER JOIN
我有通过子句将第一个表连接到第二个表的查询。结果表明存在冗余或重复的结果。在我下面的示例中,结果有 4 条记录。我想区分SdiID
并且我希望只获得两条记录,因为sdiID
. 我尝试DISTINCT sdiID
在选择后添加,但仍然得到 4 条记录。请帮忙。这是我的查询。
DECLARE @PageNumber AS INT, @RowspPage AS INT
SET @PageNumber = 1
SET @RowspPage = 20
SELECT totalCount = COUNT(*) OVER(), mailbox.ID as mailboxID,
sdi.ID as sdiID
FROM [SDI].dbo.UserDocumentLocationOutbox mailbox
INNER JOIN [SDI].dbo.SDITransaction
sdi on mailbox.SDITransactionID=sdi.ID
INNER JOIN [SYSDB].dbo.DocumentType doc on sdi.DocumentTypeID=doc.ID
where mailbox.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7'
and mailbox.UserProfileID = 'f9791614-8cc0-42e3-87d1-53709bc1e099'
and doc.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7'
and doc.Active=1 and doc.HideInMailbox=0
order by sdi.ProcessedDateTime desc
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY
这是结果
解决方案
with cte as (SELECT ROW_NUMBER() over(PARTITION BY sdi.ID order by sdi.ID) as rn,totalCount = COUNT(*) OVER(), mailbox.ID as mailboxID,
sdi.ID as sdiID
FROM [SDI].dbo.UserDocumentLocationOutbox mailbox
INNER JOIN [SDI].dbo.SDITransaction
sdi on mailbox.SDITransactionID=sdi.ID
INNER JOIN [SYSDB].dbo.DocumentType doc on sdi.DocumentTypeID=doc.ID
where mailbox.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7'
and mailbox.UserProfileID = 'f9791614-8cc0-42e3-87d1-53709bc1e099'
and doc.CommunityID = '9ff10c7a-37f5-4580-9163-6ada55194ca7'
and doc.Active=1 and doc.HideInMailbox=0
order by sdi.ProcessedDateTime desc
OFFSET ((@PageNumber - 1) * @RowspPage) ROWS FETCH NEXT @RowspPage ROWS ONLY)
select totalCount, mailboxID, sdiID
from cte
where rn < 2
推荐阅读
- tensorflow - MNIST - tf.estimator.DNNClassifier
- database - 将 Excel 数据保存到 Access 数据库
- c# - 健康滑块如果启用了语句线渲染器 Unity
- c++ - 这些标头详细信息是否正确编写?
- flutter - 有没有办法创建一个具有圆角半径、渐变和动态阴影的按钮?
- mysql - 计算已完成任务mysql中的百分比总数
- docker - 在 OpenLDAP 服务器中以纯文本形式存储用户密码
- c - 指向结构的指针数组
- python - SciKit Learn、Keras 或 Pytorch 的差异
- android - 迁移到 AndroidX 后,膨胀类 android.support.design.widget.AppBarLayout 时出错