你能帮我如何在这个查询中使用DISTINCT吗？
DECLARE @ReadData NVARCHAR(MAX);
SET @ReadData = 'SELECT ROW_NUMBER() OVER (ORDER BY RM.ReceiverDate) AS [S. No.]
,DISTINCT RM.ReceiverId
,RM.ReceiverDateas [Receiver Date]
,VM.VendorName as [Vendor Name]
,VM.VendorID
FROM Receiver RM
LEFT OUTER JOIN VendorMaster VM on VM.VendorID = RM.VendorId
WHERE ['+ @ColumnName + '] LIKE
CASE WHEN ' + @Filter+ ' = 0 THEN ''%' + @Value + '%'''+
' WHEN ' + @Filter + ' = 1 THEN '''+ @Value + '%'''+
' WHEN ' + @Filter + ' = 2 THEN ''%' + @Value +''' END
AND VM.IsActive = 1
AND RM.VendorID = CASE WHEN ' + CONVERT(VARCHAR, @VendorID) + ' = ''-1'' THEN RM.VendorID
ELSE '+ CONVERT(VARCHAR, @VendorID) +' END
AND (RM.ReceiverDate BETWEEN ' + CHAR(39) + CONVERT(VARCHAR(23), @FromDate, 126) + CHAR(39) +' AND ' + CHAR(39) + CONVERT(VARCHAR(23), @ToDate, 126) + CHAR(39) +')
ORDER BY RM.ReceiverDate '
我的问题是：如何将DISTINCT添加到ReceiverId？
我得到这个错误：
关键字"DISTINCT"附近的语法不正确。
在后面的C#代码中
with cte
as
(
Select ROW_NUMBER() OVER (PARTITION BY ReceiverId ORDER BY ReceiverId ) AS dupCount, *
from(
SELECT ROW_NUMBER() OVER (ORDER BY ReceivedDate) AS [S. No.]
,RM.ReceiverId
,RM.ReceiverName'
,VM.VendorName
, XYZ columns .....
From Receiver RM
LEFT OUTER JOIN VendorMaster VM on VM.VendorID = RM.VendorId
) tab
)
select S. No.
,ReceiverId
,ReceiverName
,VendorName
, XYZ columns
from cte
where dupcount=1;
尝试使用cte。
DECLARE @ReadData NVARCHAR(MAX);
set @ReadData = 'SELECT *
,ROW_NUMBER() OVER (ORDER BY ReceivedDate) AS [S. No.]
FROM (SELECT DISTINCT ReceiverId,
ReceivedDate
,ReceiverName
, XYZ columns .....
From Receiver .........
INNER JOIN .......)'
不能在同一级别上使用row_number和distinct。