我有这样的看法:
TABLE_TYPE
(code,name)
(1,'Computer')
(2,'Television')
(3,'Radio')
TABLE_THING
(code,name,type=TABLE_TYPE:code)
(-9999, 'ThingFirst',1)
(1,'Thing1',1)
(2,'Thing2',2)
(3,'Thing3',2)
(4,'Thing4',3)
(5,'Thing5',1)
VIEW_THINGS
(code,name,thingtype)
(-9999, 'ThingFirst','Computer')
(1,'Thing1','Computer')
(2,'Thing2','Television')
(3,'Thing3','Television')
(4,'Thing4','Radio')
(5,'Thing5','Computer')
查询:
select * from VIEW_THINGS where code in (-9999,1,2,3,4,5)
结果是:
(-9999, 'ThingFirst',NULL)
结果应为:
(-9999, 'ThingFirst','Computer')
(1,'Thing1','Computer')
(2,'Thing2','Television')
(3,'Thing3','Television')
(4,'Thing4','Radio')
(5,'Thing5','Computer')
怎么了????
提示1:当我从IN子句中退出负值(-9999)时,我得到以下信息:
(1,'Thing1','Computer')
(2,'Thing2','Television')
(3,'Thing3','Television')
(4,'Thing4','Radio')
(5,'Thing5','Computer')
Tip2:如果我从总视图结果中进行选择,我将获得正确的数据:
select * from (select * from VIEW_THINGS as T) where code in (-9999,1,2,3,4,5)
结果:
(-9999, 'ThingFirst','Computer')
(1,'Thing1','Computer')
(2,'Thing2','Television')
(3,'Thing3','Television')
(4,'Thing4','Radio')
(5,'Thing5','Computer')
最佳答案
完整的测试脚本:
create table TABLE_TYPE(code int,name varchar(20));
insert table_type values
(1,'Computer'),
(2,'Television'),
(3,'Radio');
create table TABLE_THING(code int,name varchar(20),type int);
insert table_thing values
(-9999, 'ThingFirst',1),
(1,'Thing1',1),
(2,'Thing2',2),
(3,'Thing3',2),
(4,'Thing4',3),
(5,'Thing5',1);
create table VIEW_THINGS(code int, name varchar(20),thingtype varchar(20));
insert view_things values
(-9999, 'ThingFirst','Computer'),
(1,'Thing1','Computer'),
(2,'Thing2','Television'),
(3,'Thing3','Television'),
(4,'Thing4','Radio'),
(5,'Thing5','Computer');
select * from VIEW_THINGS where code in (-9999,1,2,3,4,5);
输出量
"code";"name";"thingtype"
"-9999";"ThingFirst";"Computer"
"1";"Thing1";"Computer"
"2";"Thing2";"Television"
"3";"Thing3";"Television"
"4";"Thing4";"Radio"
"5";"Thing5";"Computer"
既然可以了,那么我会认为VIEW_THINGS是连接两个表的视图。让我们来看看
create view view_things2
as
select t.code, t.name, y.name thingtype
from table_thing t
inner join table_type y on y.code = t.type
;
select * from VIEW_THINGS2 where code in (-9999,1,2,3,4,5);
输出:如上
这是我们所能提供的信息。
关于php - 这个MySQL IN子句中的负值怎么办?,我们在Stack Overflow上找到一个类似的问题:https://stackoverflow.com/questions/5163641/