RT,有一表Order,只有这一列ID varchar(50)
ID
20091111054316671
20091111054316671
20091111054316671
20091111030243077
20091111114761595
20091111103365213
20091111103365213
20091111101216502
如何去掉重复值,并保持原排序
ID
20091111054316671
20091111030243077
20091111114761595
20091111103365213
20091111103365213
20091111101216502
我用select distinct * from [Order] 结果排序乱了,请问有什么好办法吗?
ID
20091111054316671
20091111054316671
20091111054316671
20091111030243077
20091111114761595
20091111103365213
20091111103365213
20091111101216502
如何去掉重复值,并保持原排序
ID
20091111054316671
20091111030243077
20091111114761595
20091111103365213
20091111103365213
20091111101216502
我用select distinct * from [Order] 结果排序乱了,请问有什么好办法吗?
as
(select rid =row_number() over (order by getdate()),id from ta)
select id
from t as t0
where not exists(select 1 from t where id = t0.id and rid > t0.rid)
from (select row=row_number() over (order by getdate()),* from tb)K
order by row
SELECT @SQL=ISNULL(@SQL+',','')+LTRIM(ID) FROM TB SELECT DISTINCT * FROM TB ORDER BY CHARINDEX(','+LTRIM(ID)+',',','+@SQL',')
distinct col
from
(select id=row_number() over (order by getdate()),* from tb)t
order by
id
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(ID BIGINT)
Go
Insert into TB
select 20091111054316671 union all
select 20091111054316671 union all
select 20091111054316671 union all
select 20091111030243077 union all
select 20091111114761595 union all
select 20091111103365213 union all
select 20091111103365213 union all
select 20091111101216502
Go
--StartDECLARE @SQL NVARCHAR(4000)
SELECT @SQL=ISNULL(@SQL+',','')+LTRIM(ID) FROM TB SELECT DISTINCT ID FROM
(SELECT TOP 100 PERCENT ID FROM TB ORDER BY CHARINDEX(','+LTRIM(ID)+',',','+@SQL+','))AS T
--Result:
/*(所影响的行数为 8 行)ID
--------------------
20091111030243077
20091111054316671
20091111101216502
20091111103365213
20091111114761595(所影响的行数为 5 行)
*/
--End
select id = identity(int,1,1),dtime into #tmp from (
select '20091111054316671' as dtime union all
select '20091111054316671' union all
select '20091111054316671' union all
select '20091111030243077' union all
select '20091111114761595' union all
select '20091111103365213' union all
select '20091111103365213' union all
select '20091111101216502' ) Aselect * from #tmp A where id =(select max(id) from #tmp where dtime = A.dtime)
order by id=========
id dtime
----------- -----------------
3 20091111054316671
4 20091111030243077
5 20091111114761595
7 20091111103365213
8 20091111101216502(5 行受影响)
Go
Insert into TB
select 20091111054316671 union all
select 20091111054316671 union all
select 20091111054316671 union all
select 20091111030243077 union all
select 20091111114761595 union all
select 20091111103365213 union all
select 20091111103365213 union all
select 20091111101216502
Goselect * , px = identity(int,1,1) into tmp from tbselect t.* from tmp t where px = (select min(px) from tmp where id = t.id) order by t.pxdrop table tb , tmp/*
ID px
-------------------- -----------
20091111054316671 1
20091111030243077 4
20091111114761595 5
20091111103365213 6
20091111101216502 8(所影响的行数为 5 行)
*/
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(ID BIGINT)
Go
Insert into TB
select 20091111054316671 union all
select 20091111054316671 union all
select 20091111054316671 union all
select 20091111030243077 union all
select 20091111114761595 union all
select 20091111103365213 union all
select 20091111103365213 union all
select 20091111101216502
Go
--StartDECLARE @SQL NVARCHAR(4000)
SELECT @SQL=ISNULL(@SQL+',','')+LTRIM(ID) FROM TB SELECT DISTINCT ID,CHARINDEX(','+LTRIM(ID)+',',','+@SQL+',') FROM TB ORDER BY CHARINDEX(','+LTRIM(ID)+',',','+@SQL+',')
--Result:
/*
(所影响的行数为 8 行)ID
-------------------- -----------
20091111054316671 1
20091111030243077 55
20091111114761595 73
20091111103365213 91
20091111101216502 127(所影响的行数为 5 行)
*/
--End
-- Author:SQL77--RICHIE
-- Version:V1.001 Date:2008-05-15--转Flystone*/-- Test Data: TB
If object_id('TB') is not null
Drop table TB
Go
Create table TB(ID BIGINT)
Go
Insert into TB
select 20091111054316671 union all
select 20091111054316671 union all
select 20091111054316671 union all
select 20091111030243077 union all
select 20091111114761595 union all
select 20091111103365213 union all
select 20091111103365213 union all
select 20091111101216502
Go
--StartDECLARE @SQL NVARCHAR(4000)
SELECT @SQL=ISNULL(@SQL+',','')+LTRIM(ID) FROM TB SELECT ID FROM
(
SELECT DISTINCT TOP 100 PERCENT
ID,CHARINDEX(','+LTRIM(ID)+',',','+@SQL+',') AS NUM
FROM
TB
ORDER BY
CHARINDEX(','+LTRIM(ID)+',',','+@SQL+',')
)AS T--Result:
/*
(
(所影响的行数为 8 行)ID
--------------------
20091111054316671
20091111030243077
20091111114761595
20091111103365213
20091111101216502(所影响的行数为 5 行)(所影响的行数为 5 行)
*/
--End
ROW_NUMBER OVER先把之前的列排好就行了
SELECT ID = IDENTITY(INT, 1, 1) , DTIME INTO #TEMP FROM (
SELECT '20091111054316671' AS DTIME UNION ALL
SELECT '20091111054316671' UNION ALL
SELECT '20091111054316671' UNION ALL
SELECT '20091111030243077' UNION ALL
SELECT '20091111114761595' UNION ALL
SELECT '20091111103365213' UNION ALL
SELECT '20091111103365213' UNION ALL
SELECT '20091111101216502' ) ASELECT DTIME
FROM #TEMP
GROUP BY DTIME
ORDER BY MIN(ID)DROP TABLE #TEMP