如何从视图中删除重复记录?我需要将它们保留在物理表上,但在视图中,我不想要重复项
这是我使用的查询:
CREATE VIEW myview
AS
SELECT DISTINCT *
FROM [roug].[dbo].[Table_1]
ORDER BY id
对于表:
id| name age
----------
c1 ann 12
u2 joe 15
c1 ann 12
c1 ann 12
u5 dev 13
u3 Jim 16
u3 Jim 16
您可以使用 DISTINCT 或 ROW_NUMBER() 像这样
create view myview as
WITH CTE
AS
(
SELECT
RN = ROW_NUMBER() OVER(PARTITION BY [Id],[Name],[Age] ORDER BY ID),
*
FROM [roug].[dbo].[Table_1]
)
SELECT
[Id],[Name],[Age]
FROM CTE
WHERE RN = 1
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句