create table test(value varchar)insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')四条数据我重复插了四次,所以表里现在一共有16条数据,现在我在查询的时候要将重复的数据去掉。
并且查询出来的数据的顺序仍然是
z
k
y
a我用select distinct * from test 查出来以后发现结果被自动排序了,
成为
a
k
y
z
了。
应该怎么写代码呢?谢谢。
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')四条数据我重复插了四次,所以表里现在一共有16条数据,现在我在查询的时候要将重复的数据去掉。
并且查询出来的数据的顺序仍然是
z
k
y
a我用select distinct * from test 查出来以后发现结果被自动排序了,
成为
a
k
y
z
了。
应该怎么写代码呢?谢谢。
--只能借助辅助列噢
select id=identity(int,1,1),* into # from test
delete test
insert into test select value from # a where not exists(select 1 from # where value=a.value and id>a.id)
select * from test
create table test(value varchar)insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')
select * from
(
select distinct value
from test
) t
order by case value when 'z' then 1 when 'k' then 2 when 'y' then 3 when 'a' then 4 enddrop table test/*
value
-----
z
k
y
a(所影响的行数为 4 行)
*/
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')insert into test values ('z')
insert into test values ('k')
insert into test values ('y')
insert into test values ('a')
go
;with china
as
(
select * ,px=row_number()over(order by getdate()) from test
)
select value from china a where px= (select min(px) from china where a.value=value)
godrop table test
go/*(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)(1 行受影响)
value
-----
z
k
y
a(4 行受影响)*/
select [value] from test group by [value] order by charindex([value],'zkya')
;with cte as
(
select [value],row_number() over(order by getdate()) as rn from test
)--1
--select [value] from cte c
--where rn = (select min(rn) from cte where [value]=c.[value])--2(效率略高)
select [value] from cte c
where not exists(select [value] from cte where [value]=c.[value] and rn < c.rn)
/*
z
k
y
a
*/
(
select value from test
)b
order by case value when 'z' then 1 when 'k' then 2 when 'y' then 3 when 'a' then 4 end
我用的是SQL SERVER2000,而且数据库里面的值是不定的。字段的值也不光就是一个字符。
只能肯定有三倍重复的数据。有办法做没。
select top 4 * from test
UNION ALL SELECT 'k'
UNION ALL SELECT 'y'
UNION ALL SELECT 'a'
UNION ALL SELECT 'z'
UNION ALL SELECT 'k'
UNION ALL SELECT 'y'
UNION ALL SELECT 'a'
UNION ALL SELECT 'z'
UNION ALL SELECT 'k'
UNION ALL SELECT 'y'
UNION ALL SELECT 'a'
UNION ALL SELECT 'z'
UNION ALL SELECT 'k'
UNION ALL SELECT 'y'
UNION ALL SELECT 'a'SELECT DISTINCT * FROM @test ORDER BY [name]
这个可以么???