mysql - sql动态列的问题？

Question

如下表
province_name province_code city_name city_code country_name contry_code
北京 110000 北京 110000 东城 110101
天津 120000 天津 120000 和平 120101
河北 130000 石家庄 130100 长安 130102
河北 130000 唐山 130200 路南 130202
河北 130000 秦皇岛 130300 海港 130302
河北 130000 邯郸 130400 复兴 130404

在查询数据的时候
我想 如果city_name是天津或者北京地时候 把列 country_name和country_code的值赋值给 列city_name和city_code

Answer 1

A solution that is not very efficient. I want to see if there is a more efficient way. I will edit it after I think of it

create table ttd(
pname varchar(30),
pcode int,
cname varchar(30),
ccode int,
yname varchar(30),
ycode int
);

insert into ttd(pname, pcode, cname, ccode, yname, ycode) values 
('北京', 110000, '北京', 110000,'东城', 110101),
('天津',120000, '天津', 120000, '和平', 120101),
('河北', 130000, '石家庄', 130100, '长安', 130102),
('河北', 130000, '唐山', 130200, '路南', 130202),
('河北', 130000, '秦皇岛', 130300, '海港', 130302),
('河北', 130000, '邯郸', 130400, '复兴', 130404);


select pname, pcode, yname, ycode from ttd where pname in ('北京', '天津')
union
select pname, pcode, cname, ccode from ttd where pname not in ('北京', '天津')

Relatively speaking, this solution is more efficient than the above method

select pname, pcode, 
case pname
when '北京' then yname
when '天津' then yname
else cname
end as cname,
case pname
when '北京' then ycode
when '天津' then ycode
else ccode
end as ccode
from ttd

If it is mysql, you can still play like this, but you can’t try it in postgresql..

select pname, pcode, if(cname=pname, yname, cname) as cname, if (cname=pname, ycode, ccode) as ccode  from ttd 

Another idea is to replace cname=pname with null and then use isnull to determine whether to use yname or cname. Of course, this solution is not reliable. The replace function does not seem to support replacing a field...

Of course, the prerequisite for these two solutions is that your cname in Beijing and Tianjin must be the same as pname before you can use it

I’m just a little bored. In fact, there are many ways to implement this function. For example, you can return a separate cname=pname as b so that you can get b in the program? yname: cname Of course, it must be the same as the cname just mentioned. Same as pname

I have to leave.