就是个分类排序吧.如果有某个字段能区分大小,看下例:
有一表
a b c
7 aa 153
9 aa 152
6 aa 120
8 aa 168
5 bb 159
7 bb 179
8 bb 149
9 bb 139
6 bb 169
对b列中的值来分类排序并分别加一序号,形成一新表
px a b c
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179
declare @tab table(a int,b varchar(2),c int)insert @tab values(7,'aa',153)
insert @tab values(9,'aa',152)
insert @tab values(6,'aa',120)
insert @tab values(8,'aa',168)
insert @tab values(5,'bb',159)
insert @tab values(7,'bb',179)
insert @tab values(8,'bb',149)
insert @tab values(9,'bb',139)
insert @tab values(6,'bb',169)select * from @tabselect px=(select count(1) from @tab where b=a.b and c<a.c)+1 , a,b,c from @tab a
order by b , c px a b c
----------- ----------- ---- -----------
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179(所影响的行数为 9 行)
在上面例中我们看到,以B分类排序,C是从小到大,如果C从大到小排序,即下面结果:
px a b c
1 8 aa 168
2 9 aa 153
3 7 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139declare @tab table(a int,b varchar(2),c int)insert @tab values(7,'aa',153)
insert @tab values(9,'aa',152)
insert @tab values(6,'aa',120)
insert @tab values(8,'aa',168)
insert @tab values(5,'bb',159)
insert @tab values(7,'bb',179)
insert @tab values(8,'bb',149)
insert @tab values(9,'bb',139)
insert @tab values(6,'bb',169)select * from @tabselect px=(select count(1) from @tab where b=a.b and c>a.c)+1 , a,b,c from @tab a
order by b , c descpx a b c
----------- ----------- ---- -----------
1 8 aa 168
2 7 aa 153
3 9 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139(所影响的行数为 9 行)--同时按两列分组
例如有一个表M 有A B C三列,其中值为
A B C
a1 b1 c1
a1 b1 c2
a1 b2 c1
a2 b2 c2
a2 b2 c3
a1 b2 c4
要求将其数据以A ,B列作为分组条件保存在别一个表N中,并区分不同组的明细资料(如加个区分列D)具体形式如下:
A B C D
a1 b1 c1 1
a1 b1 c2 1
a1 b2 c1 2
a2 b2 c2 3
a2 b2 c3 3
a1 b2 c4 2
帮忙想一下写法,想两天啦,还是搞不定,谢谢大家啦!
declare @M table(A varchar(4),B varchar(4),C varchar(4))
insert into @M select 'a1','b1','c1'
insert into @M select 'a1','b1','c2'
insert into @M select 'a1','b2','c1'
insert into @M select 'a2','b2','c2'
insert into @M select 'a2','b2','c3'
insert into @M select 'a1','b2','c4'select t.*,(select count(distinct A+'|'+B) from @M where A<t.A or (A=t.A and B<=t.B)) as D from @M t/*
A B C D
---- ---- ---- -----------
a1 b1 c1 1
a1 b1 c2 1
a1 b2 c1 2
a2 b2 c2 3
a2 b2 c3 3
a1 b2 c4 2
*/
create table m(A varchar(10),B varchar(10),C varchar(10))
insert into m values('a1', 'b1', 'c1')
insert into m values('a1', 'b1', 'c2')
insert into m values('a1', 'b2', 'c1')
insert into m values('a2', 'b2', 'c2')
insert into m values('a2', 'b2', 'c3')
insert into m values('a1', 'b2', 'c4')
go
select d = identity(int,1,1) , * into temp from (select distinct a,b from m) t
select m.*,temp.d from m,temp where m.a = temp.a and m.b = temp.b
drop table m,temp
/*
A B C d
---------- ---------- ---------- -----------
a1 b1 c1 1
a1 b1 c2 1
a1 b2 c1 2
a2 b2 c2 3
a2 b2 c3 3
a1 b2 c4 2(所影响的行数为 6 行)*/
---------------------------------------------------------------------
我有一个表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语句怎么写啊? 望大虾帮忙 use testgo
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 tauno type stat
----------- ----------- --------------------------------------------------------------
2659 807 TYPE
2658 805 CHIPSET1
2655 806 TYPE1
2661 808 CHIPSET
2654 802 WxHxD(mm)
2741 804 Weight(Kg/Pound)(所影响的行数为 6 行)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)')
goselect 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 aunodrop 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 行)
*/如果所有列都有相同的,必须使用临时表来完成.select px = identity(int,1,1) , * into temp from tb order by ....
有一表
a b c
7 aa 153
9 aa 152
6 aa 120
8 aa 168
5 bb 159
7 bb 179
8 bb 149
9 bb 139
6 bb 169
对b列中的值来分类排序并分别加一序号,形成一新表
px a b c
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179
declare @tab table(a int,b varchar(2),c int)insert @tab values(7,'aa',153)
insert @tab values(9,'aa',152)
insert @tab values(6,'aa',120)
insert @tab values(8,'aa',168)
insert @tab values(5,'bb',159)
insert @tab values(7,'bb',179)
insert @tab values(8,'bb',149)
insert @tab values(9,'bb',139)
insert @tab values(6,'bb',169)select * from @tabselect px=(select count(1) from @tab where b=a.b and c<a.c)+1 , a,b,c from @tab a
order by b , c px a b c
----------- ----------- ---- -----------
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179(所影响的行数为 9 行)
在上面例中我们看到,以B分类排序,C是从小到大,如果C从大到小排序,即下面结果:
px a b c
1 8 aa 168
2 9 aa 153
3 7 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139declare @tab table(a int,b varchar(2),c int)insert @tab values(7,'aa',153)
insert @tab values(9,'aa',152)
insert @tab values(6,'aa',120)
insert @tab values(8,'aa',168)
insert @tab values(5,'bb',159)
insert @tab values(7,'bb',179)
insert @tab values(8,'bb',149)
insert @tab values(9,'bb',139)
insert @tab values(6,'bb',169)select * from @tabselect px=(select count(1) from @tab where b=a.b and c>a.c)+1 , a,b,c from @tab a
order by b , c descpx a b c
----------- ----------- ---- -----------
1 8 aa 168
2 7 aa 153
3 9 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139(所影响的行数为 9 行)--同时按两列分组
例如有一个表M 有A B C三列,其中值为
A B C
a1 b1 c1
a1 b1 c2
a1 b2 c1
a2 b2 c2
a2 b2 c3
a1 b2 c4
要求将其数据以A ,B列作为分组条件保存在别一个表N中,并区分不同组的明细资料(如加个区分列D)具体形式如下:
A B C D
a1 b1 c1 1
a1 b1 c2 1
a1 b2 c1 2
a2 b2 c2 3
a2 b2 c3 3
a1 b2 c4 2
帮忙想一下写法,想两天啦,还是搞不定,谢谢大家啦!
declare @M table(A varchar(4),B varchar(4),C varchar(4))
insert into @M select 'a1','b1','c1'
insert into @M select 'a1','b1','c2'
insert into @M select 'a1','b2','c1'
insert into @M select 'a2','b2','c2'
insert into @M select 'a2','b2','c3'
insert into @M select 'a1','b2','c4'select t.*,(select count(distinct A+'|'+B) from @M where A<t.A or (A=t.A and B<=t.B)) as D from @M t/*
A B C D
---- ---- ---- -----------
a1 b1 c1 1
a1 b1 c2 1
a1 b2 c1 2
a2 b2 c2 3
a2 b2 c3 3
a1 b2 c4 2
*/
create table m(A varchar(10),B varchar(10),C varchar(10))
insert into m values('a1', 'b1', 'c1')
insert into m values('a1', 'b1', 'c2')
insert into m values('a1', 'b2', 'c1')
insert into m values('a2', 'b2', 'c2')
insert into m values('a2', 'b2', 'c3')
insert into m values('a1', 'b2', 'c4')
go
select d = identity(int,1,1) , * into temp from (select distinct a,b from m) t
select m.*,temp.d from m,temp where m.a = temp.a and m.b = temp.b
drop table m,temp
/*
A B C d
---------- ---------- ---------- -----------
a1 b1 c1 1
a1 b1 c2 1
a1 b2 c1 2
a2 b2 c2 3
a2 b2 c3 3
a1 b2 c4 2(所影响的行数为 6 行)*/
---------------------------------------------------------------------
我有一个表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语句怎么写啊? 望大虾帮忙 use testgo
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 tauno type stat
----------- ----------- --------------------------------------------------------------
2659 807 TYPE
2658 805 CHIPSET1
2655 806 TYPE1
2661 808 CHIPSET
2654 802 WxHxD(mm)
2741 804 Weight(Kg/Pound)(所影响的行数为 6 行)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)')
goselect 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 aunodrop 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 行)
*/如果所有列都有相同的,必须使用临时表来完成.select px = identity(int,1,1) , * into temp from tb order by ....
insert into gs select 'TB211','1.02.0137-2051-W','Production/Loc Body','28000','IDM.A000.014','EA',1,0,-1
insert into gs select 'TB211','1.02.0137-2051-W','Production/Loc Body','28000','IDM.A000.047','EA',4,0,-4
insert into gs select 'TB213','1.02.0137-2577-B','Production/Loc Body','10000','IDM.A000.047','EA',1,0,-1
insert into gs select 'TB213','1.02.0137-2577-B','Production/Loc Body','10000','IDM.A000.014','EA',1,0,-1
insert into gs select 'TB212','1.02.0137-2051-U','Production/Loc Body','15000','IDM.A000.024','EA',60,60,20
insert into gs select 'TB212','1.02.0137-2051-U','Production/Loc Body','15000','IDM.A000.014','EA',1,0,-0.6667
insert into gs select 'TB212','1.02.0137-2051-U','Production/Loc Body','15000','IDM.A000.047','EA',3,0,-2
select id=identity(int,1,1),* into #temp from gsselect 工单号=(select 'SOUT'+'11'+right('0000'+cast(count(distinct 工单号)+1 as varchar),4) from #temp where 工单号<>a.工单号 and id<a.id),
分录=(select count(1)+1 from #temp where 工单号=a.工单号 and id<a.id),
* from #temp a
select id=identity(int,1,1),* into #temp from gscreate index #t1_index on #t1(id,工单号)--加上这个select 工单号=(select 'SOUT'+'11'+right('0000'+cast(count(distinct 工单号)+1 as varchar),4) from #temp where 工单号<>a.工单号 and id<a.id),
分录=(select count(1)+1 from #temp where 工单号=a.工单号 and id<a.id),
* from #temp a
---换为:
create index #t1_index on #temp(id,工单号)--加上这个