不符合第一范式但还是能完成2表间的查询解决方法

不符合第一范式但还是能完成2表间的查询

SQL code

create table T1(  orderID int not null,  typer  varchar(18) not null,)create table T2(  typer int not null,  name varchar(6) not null)insert into T1(orderID,typer) values(001,'1;2;3')insert into T1(orderID,typer) values(002,'2;3;4')insert into T2(typer,name) values(1,'CPU')insert into T2(typer,name) values(2,'主板')insert into T2(typer,name) values(3,'机箱')insert into T2(typer,name) values(4,'键盘')insert into T2(typer,name) values(5,'鼠标')select name from T2 where T2.typer in(convert(int,(select typer from T1 where T1.orderID=001)))

SQL code

create table T1(  orderID int not null,  typer  varchar(18) not null,)create table T2(  typer int not null,  name varchar(6) not null)insert into T1(orderID,typer) values(001,'1;2;3')insert into T1(orderID,typer) values(002,'2;3;4')insert into T2(typer,name) values(1,'CPU')insert into T2(typer,name) values(2,'主板')insert into T2(typer,name) values(3,'机箱')insert into T2(typer,name) values(4,'键盘')insert into T2(typer,name) values(5,'鼠标')select t1.*,t2.* from t1 , t2 where t1.orderID='001'and charindex(';'+cast(t2.typer as varchar) + ';' , ';' + cast(t1.typer as varchar) + ';') > 0/*orderID     typer              typer       name   ----------- ------------------ ----------- ------ 1           1;2;3              1           CPU1           1;2;3              2           主板1           1;2;3              3           机箱（所影响的行数为 3 行）*/drop table t1 , t2
[解决办法]
SQL code
create table t1(    id varchar(3),    typeid varchar(20))insert into t1select '001','1;2;3' union allselect '002','2;3;4'create table t2(    typeid int,    name varchar(10))insert into t2select 1,'CPU' union allselect 2,'主板' union allselect 3,'机箱' union allselect 4,'键盘' union allselect 5,'鼠标'select * from t1select * from t2select * from t2 where CHARINDEX(cast(typeid as varchar),(select typeid from t1 where id='001'))>0-----------------------typeid    name1    CPU2    主板3    机箱