不符合第一范式但还是能完成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 机箱