怎么使得sql字段每行加1

[Mapleleaf123]

比如

title stype myid

AAA 1

BBB 1

测试 2

什么` 2

CCC 1

好的 2

执行什么语句后，结果如下

title stype myid

AAA 1 1

BBB 1 2

测试 2

什么` 2

CCC 1 3

好的 2

[jiangshun]

没看明白？

[jiangshun]

-----------------------------------------

--> 测试时间：2009-07-22
--> 我的淘宝：http://shop36766744.taobao.com/

--------------------------------------------------

if object_id('[tab]') is not null drop table [tab]
create table [tab]([title] varchar(5),[stype] int)
insert [tab]
select 'AAA',1 union all
select 'BBB',1 union all
select '测试',2 union all
select '什么`',2 union all
select 'CCC',1 union all
select '好的',2

select *,myid=(select count(1)+1 from tab where t.title>title) from [tab] t
 order by myid
/*
title stype       myid        
----- ----------- ----------- 
AAA   1           1
BBB   1           2
CCC   1           3
测试    2           4
好的    2           5
什么`   2           6

（所影响的行数为 6 行）


*/
drop table tab

[仙道彰]

看不懂楼主的数据，在说明一下吧

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(title varchar(19),  stype int,  myid int) 
insert tb (title , stype )SELECT
'AAA' ,   1      
UNION ALL SELECT 
'BBB'  ,  1 
UNION ALL SELECT 
'测试' , 2 
UNION ALL SELECT 
'什么',  2 
UNION ALL SELECT 
'CCC' ,   1 
UNION ALL SELECT 
'好的' , 2 
go
select title , stype,myid=ROW_NUMBER() over (order by stype) from tb 
go
/*------------
title               stype       myid
------------------- ----------- --------------------
AAA                 1           1
BBB                 1           2
CCC                 1           3
好的                  2           4
测试                  2           5
什么                  2           6

-------*/

[SQL77]

SELECT title , stype,  
myid=(SELEC COUNT(*) FROM TB T WHERE T.STYPE=T1.STYPE AND T.TITLE<=T1.TITLE)
FROM TB T1

WHERE TITLE LIKE'[A-Z]%'

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------
   ---希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(title varchar(19),  stype int,  myid int) 
insert tb (title , stype )SELECT
'AAA' ,   1      
UNION ALL SELECT 
'BBB'  ,  1 
UNION ALL SELECT 
'测试' , 2 
UNION ALL SELECT 
'什么',  2 
UNION ALL SELECT 
'CCC' ,   1 
UNION ALL SELECT 
'好的' , 2 
go
select title , stype,myid=case when stype=1 then rtrim(ROW_NUMBER() over (order by stype) )else '  ' end from tb 
go
/*------------
(6 行受影响)
title               stype       myid
------------------- ----------- ------------------------
AAA                 1           1
BBB                 1           2
CCC                 1           3
好的                  2             
测试                  2             
什么                  2             


-------*/

[SQL77]

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(title varchar(19),  stype int,  myid int) 
insert tb (title , stype )SELECT
'AAA' ,   1      
UNION ALL SELECT 
'BBB'  ,  1 
UNION ALL SELECT 
'测试' , 2 
UNION ALL SELECT 
'什么',  2 
UNION ALL SELECT 
'CCC' ,   1 
UNION ALL SELECT 
'好的' , 2 
go

SELECT A.title,A.stype,ISNULL(B.myid,'') MYID

FROM TB A LEFT JOIN 

(SELECT title , stype,  
myid=(SELECT COUNT(*) FROM TB T WHERE T.STYPE=T1.STYPE AND T.TITLE<=T1.TITLE)
FROM TB T1

WHERE TITLE LIKE'[A-Z]%') AS B

ON A.TITLE =B.TITLE AND A.STYPE=B.STYPE

title               stype       MYID        
------------------- ----------- ----------- 
AAA                 1           1
BBB                 1           2
测试                  2           0
什么                  2           0
CCC                 1           3
好的                  2           0

（所影响的行数为 6 行）

[youzhj]

感觉楼主这个结果是要判断表中第三列哪些是字母字符串吧？是字母的就给编号，不是字母的就跳过

[youzhj]

我觉得7楼的大哥给出了正确答案。[Quote=引用 7 楼 sql77 的回复:]
SQL codeIFOBJECT_ID('tb')ISNOTNULLDROPTABLE tbGOCREATETABLE tb(titlevarchar(19), stypeint, myidint)insert tb (title , stype )SELECT'AAA' ,1UNIONALLSELECT'BBB' ,1UNIONALLSELECT'测试' ,2UNIONALLSELECT'¡­
[/Quote]

[jimwoo]

if object_id('[tab]') is not null drop table [tab]
create table [tab]([title] varchar(5),[stype] int)
insert [tab]
select 'AAA',1 union all
select 'BBB',1 union all
select '测试',2 union all
select '什么`',2 union all
select 'CCC',1 union all
select '好的',2

--SQL2000
select *, (select count(1) from tab where stype = a.stype and title <= a.title) as myid
  from tab a order by stype, title


--SQL2005
select *, ROW_NUMBER() over(partition by stype order by title desc) as myid from tab


drop table tab
/*
title stype       myid        
----- ----------- ----------- 
AAA   1           1
BBB   1           2
CCC   1           3
测试    2           1
好的    2           2
什么`   2           3

（所影响的行数为 6 行）

title stype       myid                 
----- ----------- -------------------- 
CCC   1           1
BBB   1           2
AAA   1           3
什么`   2           1
好的    2           2
测试    2           3

（所影响的行数为 6 行）
*/

[仙道彰]

[Quote=引用 7 楼 sql77 的回复:]
SQL codeIFOBJECT_ID('tb')ISNOTNULLDROPTABLE tbGOCREATETABLE tb(titlevarchar(19), stypeint, myidint)insert tb (title , stype )SELECT'AAA' ,1UNIONALLSELECT'BBB' ,1UNIONALLSELECT'测试' ,2UNIONALLSELECT'¡­
[/Quote]

学习，这你都看出来了，厉害

[Mapleleaf123]

谢谢大家，SELECT结果正确，直接UPDATE 了保留在数据库里面，以方面以后查询应该怎么写呢

[jimwoo]

if object_id('[tab]') is not null drop table [tab]
create table [tab]([title] varchar(5),[stype] int, myid int)
insert [tab]
select 'AAA',1, null union all
select 'BBB',1, null union all
select '测试',2, null union all
select '什么`',2, null union all
select 'CCC',1, null union all
select '好的',2, null


--SQL2000
update tab set myid = t.id from tab
  join (select *, (select count(1) from tab where stype = a.stype and title <= a.title) as id from tab a) t
  on tab.title = t.title and tab.stype = t.stype
select * from tab

--SQL2005
update tab set myid = t.id from tab
  join (select *, ROW_NUMBER() over(partition by stype order by title) as id from tab a) t
  on tab.title = t.title and tab.stype = t.stype
  

select * from tab order by stype, title
drop table tab

/*
title stype       myid        
----- ----------- ----------- 
AAA   1           1
BBB   1           2
测试    2           1
什么`   2           3
CCC   1           3
好的    2           2

（所影响的行数为 6 行）


（所影响的行数为 6 行）

title stype       myid        
----- ----------- ----------- 
AAA   1           1
BBB   1           2
CCC   1           3
测试    2           1
好的    2           2
什么`   2           3

（所影响的行数为 6 行）
*/

[上善若水_厚德载物]

IF OBJECT_ID('tb') IS NOT NULL
  DROP TABLE tb
GO
CREATE TABLE tb(title varchar(19),  stype int,  myid int) 
insert tb (title , stype )SELECT
'AAA' ,   1      
UNION ALL SELECT 
'BBB'  ,  1 
UNION ALL SELECT 
'测试' , 2 
UNION ALL SELECT 
'什么',  2 
UNION ALL SELECT 
'CCC' ,   1 
UNION ALL SELECT 
'好的' , 2 


--select
SELECT title , stype,  
(SELECt COUNT(*) FROM TB T WHERE T.STYPE=T1.STYPE AND T.TITLE<=T1.TITLE and TITLE LIKE'[A-Z]%') as myid
FROM TB T1 order by title

--update
update tb set myid = t.id 
from tb
  join 
(select *, (select count(1) from tb where stype = a.stype and title <= a.title and TITLE LIKE'[A-Z]%') as id from tb a) t
  on tb.title = t.title and tb.stype = t.stype

select * from tb order by title

/**
title               stype       myid        
------------------- ----------- ----------- 
AAA                 1           1
BBB                 1           2
CCC                 1           3
测试                  2           0
好的                  2           0
什么                  2           0

（所影响的行数为 6 行）


（所影响的行数为 6 行）

title               stype       myid        
------------------- ----------- ----------- 
AAA                 1           1
BBB                 1           2
CCC                 1           3
测试                  2           0
好的                  2           0
什么                  2           0

（所影响的行数为 6 行）

**/