关于一张表查询

[Amy_2008]

一张表包含里有 款号、工种

查询出所有该表款号、工种不能重复，并增加一列自动编号

[jacklinchen]

不重复: select distinct 款号、工种 from tb

[lgxyz]

多给些其他字段比较主键是什么?

[dawugui]

[Quote=引用楼主 Amy_2008 的帖子:]
一张表包含里有 款号、工种

查询出所有该表款号、工种不能重复，并增加一列自动编号
[/Quote]

select id = identity(int ,1,1) , distinct 款号,工种 into tmp from tb
select * from tmp

[子陌红尘]

select identity(int,1,1) as ID,a.* into # from (select distinct 款号,工种 from tb) a 

select * from #

[水族杰纶]

select distinct 款号,工种 ,identity(int,1,1)ID into Newtable from oldtable

[dawugui]

--使用临时表完成
select id = identity(int ,1,1) , distinct 款号,工种 into tmp from tb
select * from tmp

--直接查询
select n.* , id = (select count(1) from 
(
  select distinct 款号,工种 into tmp from tb
) m where 款号 < n.款号 or (款号 = n.款号 and 工种 < n.工种)) + 1
from 
(
  select distinct 款号,工种 into tmp from tb
) n

[wgzaaa]

意思不明确

[dawugui]

--使用临时表完成
select id = identity(int ,1,1) , distinct 款号,工种 into tmp from tb
select * from tmp

--直接查询
select n.* , id = (select count(1) from 
(
  select distinct 款号,工种 from tb
) m where 款号 < n.款号 or (款号 = n.款号 and 工种 < n.工种)) + 1
from 
(
  select distinct 款号,工种 from tb
) n

[zoujp_xyz]

select identity(int,1,1) as ID,a.* into #t from (select distinct 款号,工种 from tb) a

select * from #t

[等不到来世]

select distinct 款号,工种,自动编号=row_number() over(order by 款号,工种) from tb 

[fcuandy]

题意不明确