日期:2014-05-18  浏览次数:20549 次

求一条这样的SQL...
有以下数据:
services b_flag b_PortCode POL POD b_id PortCode
xx 0 0 HK 0 14
xx 1 0 Guam 1 19
xx 0 19 Busan 0 21
xx 0 19 Tacoma 0 16
xx 1 0 Newark 2 26
xx 0 26 GOGO 0 15
..
..
要得到这样的结果:查出b_flag=1对应的PortCode和b_id,然后给b_PortCode=相对应的PortCode
的POL或者POD的前面加上"*",如果相对应的b_id=1加一个*,为2时加两个*,依次类推..
例如,上面的数据应该输出这样的结果:

services b_flag b_PortCode POL POD b_id PortCode
xx 0 0 HK 0 14
xx 1 0 Guam 1 19
xx 0 19 *Busan 0 21
xx 0 19 *Tacoma 0 16
xx 1 0 Newark 2 26
xx 0 26 **GOGO 0 15
..
..
用一条语句应该怎样写?

------解决方案--------------------
SQL code
create table tb(services varchar(10),b_flag int,b_PortCode int,POL_POD varchar(20),b_id int,PortCode int)
insert into tb values('xx',       0,      0 ,         'HK'     ,   0,     14) 
insert into tb values('xx',       1,      0 ,         'Guam'   ,   1,     19) 
insert into tb values('xx',       0,      19,         'Busan' ,   0,     21) 
insert into tb values('xx',       0,      19,         'Tacoma',   0,     16) 
insert into tb values('xx',       1,      0 ,         'Newark' ,   2,     26) 
insert into tb values('xx',       0,      26,         'GOGO' ,   0,     15)
go

update tb
set POL_POD = left('**********',t.b_id) + a.POL_POD
from tb a,(select * from tb where b_flag = 1) t
where a.b_portcode = t.portcode

select * from tb

drop table tb

/*
services   b_flag      b_PortCode  POL_POD              b_id        PortCode    
---------- ----------- ----------- -------------------- ----------- ----------- 
xx         0           0           HK                   0           14
xx         1           0           Guam                 1           19
xx         0           19          *Busan               0           21
xx         0           19          *Tacoma              0           16
xx         1           0           Newark               2           26
xx         0           26          **GOGO               0           15

(所影响的行数为 6 行)
*/