求一SQL语句,
求一SQL语句,在线等。。。。
我有一个表tbl,有3个字段
auno int
type int
stat varchar
现在表中数据是这样的:
auno type stat
2659 807 TYPE
2658 805 CHIPSET
2655 806 TYPE
2661 808 CHIPSET
2654 802 WxHxD(mm)
2741 804 Weight(Kg/Pound)
我想要实现这种效果:当stat中内容跟前面有重复时, stat内容的后面加1
我想要得到这种查询结果:
auno type stat
2659 807 TYPE
2658 805 CHIPSET
2655 806 TYPE1
2661 808 CHIPSET1
2654 802 WxHxD(mm)
2741 804 Weight(Kg/Pound)
这样的sql语句怎么写啊? 望大虾帮忙。。。
------解决方案--------------------
我有一个表tbl,有3个字段
auno int
type int
stat varchar
现在表中数据是这样的:
auno type stat
2659 807 TYPE
2658 805 CHIPSET
2655 806 TYPE
2661 808 CHIPSET
2654 802 WxHxD(mm)
2741 804 Weight(Kg/Pound)
我想要实现这种效果:当stat中内容跟前面有重复时, stat内容的后面加1
我想要得到这种查询结果:
auno type stat
2659 807 TYPE
2658 805 CHIPSET
2655 806 TYPE1
2661 808 CHIPSET1
2654 802 WxHxD(mm)
2741 804 Weight(Kg/Pound)
这样的sql语句怎么写啊? 望大虾帮忙。。。
------解决方案--------------------
- SQL code
use test go declare @t table (auno int, type int, stat nvarchar(50)) insert @t select 2659, 807, 'TYPE' insert @t select 2658, 805, 'CHIPSET' insert @t select 2655, 806, 'TYPE' insert @t select 2661, 808, 'CHIPSET' insert @t select 2654, 802, 'WxHxD(mm)' insert @t select 2741, 804, 'Weight(Kg/Pound)' select auno,type, [stat]=stat+ case when (select count(1) from @t where stat=t.stat and auno>t.auno)=0 then '' else rtrim((select count(1) from @t where stat=t.stat and auno>t.auno)) end from @t t auno type stat ----------- ----------- -------------------------- 2659 807 TYPE 2658 805 CHIPSET1 2655 806 TYPE1 2661 808 CHIPSET 2654 802 WxHxD(mm) 2741 804 Weight(Kg/Pound) (所影响的行数为 6 行)
------解决方案--------------------
- SQL code
create table tb(auno int ,type int ,stat varchar(20) ) insert into tb values(2659, 807, 'TYPE') insert into tb values(2658, 805, 'CHIPSET') insert into tb values(2655, 806, 'TYPE') insert into tb values(2661, 808, 'CHIPSET') insert into tb values(2654, 802, 'WxHxD(mm)') insert into tb values(2741, 804, 'Weight(Kg/Pound)') go select auno,type,stat = case when px > 1 then stat+'1' else stat end from ( select px=(select count(1) from tb where stat=a.stat and auno<a.auno)+1 , * from tb a ) t order by auno drop table tb /* auno type stat ----------- ----------- --------------------- 2654 802 WxHxD(mm) 2655 806 TYPE 2658 805 CHIPSET 2659 807 TYPE1 2661 808 CHIPSET1 2741 804 Weight(Kg/Pound) (所影响的行数为 6 行) */
------解决方案--------------------
- SQL code
create table t3(auno int,type int,stat varchar(50)) insert into t3 select 2659, 807, 'TYPE' insert into t3 select 2658, 805 ,'CHIPSET' insert into t3 select 2655, 806, 'TYPE' insert into t3 select 2661, 808, 'CHIPSET' insert into t3 select 2654 ,802, 'WxHxD(mm)' insert into t3 select 2741, 804, 'Weight(Kg/Pound)' select auno,type,stat + replace(right(cast((select count(1) from t3 where auno<a.auno and a.stat=t3.stat) as varchar),1),'0','') stat from t3 a
------解决方案--------------------
- SQL code
use test go declare @t table (auno int, type int, stat nvarchar(50)) insert @t select 2659, 807, 'TYPE' insert @t select 2658, 805, 'CHIPSET' insert @t select 2655, 806, 'TYPE' insert @t select 2661, 808, 'CHIPSET' insert @t select 2654, 802, 'WxHxD(mm)' insert @t select 2741, 804, 'Weight(Kg/Pound)' select t1.auno, t1.type, [stat]=t1.stat+case when t2.con=0 then '' else rtrim(t2.con) end from @t t1 left join (select auno,con=(select count(1) from @t where stat=t.stat and auno>t.auno) from @t t)t2 on t1.auno=t2.auno auno type stat ----------- ----------- -------------------------- 2659 807 TYPE 2658 805 CHIPSET1 2655 806 TYPE1 2661 808 CHIPSET 2654 802 WxHxD(mm) 2741 804 Weight(Kg/Pound) (所影响的行数为 6 行)