求一条这样的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
..
..
用一条语句应该怎样写?
[解决办法]
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)goupdate tbset POL_POD = left('**********',t.b_id) + a.POL_PODfrom tb a,(select * from tb where b_flag = 1) twhere a.b_portcode = t.portcodeselect * from tbdrop table tb/*services b_flag b_PortCode POL_POD b_id PortCode ---------- ----------- ----------- -------------------- ----------- ----------- xx 0 0 HK 0 14xx 1 0 Guam 1 19xx 0 19 *Busan 0 21xx 0 19 *Tacoma 0 16xx 1 0 Newark 2 26xx 0 26 **GOGO 0 15（所影响的行数为 6 行）*/