Create table tb(a int) insert tb select 1 union all select 1 union all select 2 union all select 1 union all select 1 union all select 1 union all select 1select distinct a into #t from tb truncate table tb insert tb select a from #t select * from tb
select distinct a from tb应该是这个吧,吓我?
表 T 列 a显示: select t.a from t group t.a 更新: update
select distinct a from 表基础..
--还可以 select a from tb group by a order by a
--还可以 select a from tb group by a order by a
select distinct a from tb 或者 select a from tb group by a
jia_guijun 的方法很好嘛!就是这样的
sql中的Northwind数据库的例子 select employeeid,max(territoryid) from employeeterritories group by employeeid 或者 select distinct employeeid from employeeterritories
2005里的 create table test (a int) insert into test select 1 union all select 1 union all select 2 union all select 1 union all select 1 union all select 1 union all select 1 select distinct a from test with ste as ( select row_number() over(partition by a order by a) as id,a from test ) select a into tt from ste where id<=1drop table tt
--可以先把数据查询到一个新表TT中,然后删掉原表,重命名新表TT即可 --如16楼,2005用公用表达式就行 WITH CET AS ( SELECT ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS ID,A FROM TEST ) SELECT A INTO TT FROM STE WHERE ID=1
--创建测试表 create table tab(col varchar(2))--插入测试数据 insert tab select 'a' union all select 'b' union all select '1' union all select '1' union all select '1' union all select '1' union all select '1' union all select '1' union all select '1' union all select '1' union all select '1' union all select '1' --期待结果 --col --a --b --1--过滤数据到临时表 select distinct col into tab1 from tab --删除原表数据 truncate table tab --数据导回到原表 insert tab select col from tab1 --查询结果 select col from tab --删除测试环境 drop table tab,tab1
select distinct a from [Table]
insert tb select 1 union all
select 1 union all
select 2 union all
select 1 union all
select 1 union all
select 1 union all
select 1select distinct a into #t from tb
truncate table tb
insert tb select a from #t
select * from tb
更新: update
select a from tb group by a order by a
select a from tb group by a order by a
或者
select a from tb group by a
select employeeid,max(territoryid) from employeeterritories group by employeeid
或者
select distinct employeeid from employeeterritories
create table test (a int)
insert into test select
1 union all
select
1 union all
select
2 union all
select
1 union all
select
1
union all
select
1 union all
select
1
select distinct a from test
with ste
as
(
select row_number() over(partition by a order by a) as id,a from test
)
select a into tt from ste where id<=1drop table tt
--如16楼,2005用公用表达式就行
WITH CET
AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY A ORDER BY A) AS ID,A FROM TEST
)
SELECT A INTO TT FROM STE WHERE ID=1
第二步:将原表数据清除;
第三步:临时表信息插入到原表中。
create table tab(col varchar(2))--插入测试数据
insert tab
select 'a' union all
select 'b' union all
select '1' union all
select '1' union all
select '1' union all
select '1' union all
select '1' union all
select '1' union all
select '1' union all
select '1' union all
select '1' union all
select '1'
--期待结果
--col
--a
--b
--1--过滤数据到临时表
select distinct col
into tab1
from tab
--删除原表数据
truncate table tab
--数据导回到原表
insert tab
select col
from tab1
--查询结果
select col from tab
--删除测试环境
drop table tab,tab1
(12 行受影响)(3 行受影响)(3 行受影响)
col
----
1
a
b(3 行受影响)
a为列名也可以创建View啊!