34,590
社区成员
发帖
与我相关
我的任务
分享
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)
--用此例的方法生成一个子表.
有一表
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 @tab
select 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 139
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 @tab
select 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 desc
px 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 行)
*/
--然后
select qid,cid,pid,ptid,pbid,ptName,pbName,pName,Model,
max(case px when 1 then comName else '' end) 'comName1',
max(case px when 1 then Price else 0 end) 'Price1',
max(case px when 2 then comName else '' end) 'comName2',
max(case px when 2 then Price else 0 end) 'Price2',
max(case px when 3 then comName else '' end) 'comName3',
max(case px when 3 then Price else 0 end) 'Price3'
from
(此处为上面的子查询) t
group by qid,cid,pid,ptid,pbid,ptName,pbName,pName,Model
select px=(select count(1) from quote where pid=a.pid and QuotePrice<a.QuotePrice)+1 , qID,cid,pid,QuotePrice,QuoteTime,AddTime,AddPerson,ModPerson
from quote as a
order by pid,QuotePrice
px qID cid pid QuotePrice
----------- ----------- ----------- ----------- -----------------------------------------------------
1 3 3 6 500.0
2 6 1 6 1000.0
3 7 2 6 8000.0
1 8 1 7 500.0
2 9 3 7 1000.0
2 4 2 7 1000.0
1 10 1 8 1000.0
2 5 3 8 1500.0
3 11 2 8 5000.0
(所影响的行数为 9 行)
--然后
select qid,cid,pid,ptid,pbid,ptName,pbName,pName,Model,
max(case px when 1 then comName else '' end) 'comName1',
max(case px when 1 then Price else 0 end) 'Price1',
max(case px when 2 then comName else '' end) 'comName2',
max(case px when 2 then Price else 0 end) 'Price2',
max(case px when 3 then comName else '' end) 'comName3',
max(case px when 3 then Price else 0 end) 'Price3'
from
(此处为上面的子查询) t
group by qid,cid,pid,ptid,pbid,ptName,pbName,pName,Model
select 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