Mysql的嵌套查询不是用subselect 方式实现的,而是用join语句 查找 table1 中找到所有的记录行,其 id 值没有出现在 table2 中 不是用 select table1.* from table1 where id not in (select id from table2) 而是用join语句 SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;
那我的这条SQL语句在 mysql中怎么实现呢?
mysql 3.22以下版本不支持子查询, 但所有的子查询都可以使用LEFT JOIN 或 and语句替代以下建立一个demo表和一些数据, 其中pid为产品编号, sid为分类编号 查询目的是为了查询属于不同分类的所有产品. 注意: mysql 3.22以下版本不支持子查询, 但所有的子查询都可以使用 LEFT JOIN 或 and语句替代. create table table1 ( pid char (10) not null , sid char (10) not null ); insert into table1 values ("apple","1"); insert into table1 values ("apple","2"); insert into table1 values ("apple","3"); insert into table1 values ("apple","4"); insert into table1 values ("apple","5"); insert into table1 values ("apple","6"); insert into table1 values ("pear","1"); insert into table1 values ("pear","3"); insert into table1 values ("pear","4"); insert into table1 values ("pear","7"); insert into table1 values ("orange","1"); insert into table1 values ("orange","2"); insert into table1 values ("orange","3"); 查询方法 -------- 设定分类有3类, 我们希望查询这些分类中分属以下类型的产品: sort 1 sort 2 sort 3 --------------------------- 1 -- 3 -- ( 2 | 4 ) 我们使用如下的SQL语句: select t1.pid from table1 as t1 LEFT JOIN table1 as t2 on t1.pid=t2.pid LEFT JOIN table1 as t3 on t1.pid=t3.pid where t1.sid=1 and t2.sid=3 and (t3.sid=2 or t3.sid=4) group by pid; 结果为: +-------+ |t1.pid | +-------+ |apple | |pear | |orange | +-------+ 如果希望查询分类如下: sort 1 sort 2 sort 3 -------------------------------------- ( 1 | 5 ) -- ( 3 | 6 | 7 ) -- 4 使用如下的SQL语句: select t1.pid from table1 as t1 LEFT JOIN table1 as t2 on t1.pid=t2.pid LEFT JOIN table1 as t3 on t1.pid=t3.pid where ( t1.sid=1 or t1.sid=5 ) and (t2.sid=3 or t2.sid=6 or t2.sid=7) and (t3.sid=2 or t3.sid=4) group by pid; 结果为: +------+ |t1.pid| +------+ |apple | |pear | +------+ 如果有更多的分类, 并且希望查询的分类如下: sort 1 sort 2 sort 3 sort 4 -------------------------------------- ( 1 ) -- ( 3 ) -- ( 2 | 4) -- ( 5 ) 我们使用如下语句: select t1.pid from table1 as t1 LEFT JOIN table1 as t2 on t1.pid=t2.pid LEFT JOIN table1 as t3 on t1.pid=t3.pid LEFT JOIN table1 as t4 on t1.pid=t4.pid where t1.sid=1 and t2.sid=3 and (t3.sid=2 or t3.sid=4) and t4.sid=5 group by pid; 结果为: +------+ |t1.pid| +------+ |apple | +------+
语句没错,安装高版本的mysql.至少4.1以上.
$sql="select * from $Table[zt] a,$Table[index] b where a.zhuiid='$id' and b.fenid='$fenid' and a.id=b.fenid order by b.date desc limit 0,5; //_________用重命名,也可JOIN语句!!!可共时关连多个数据库!!!
select * from project where project_id in (select distinct task_inproject from task where task_leader='$employee_name') 本身好象就有错误的,即使支持子查询,也得这样写吧? select * from project where project_id in (select distinct project_id from task where task_leader='$employee_name')
=================
可以用left join,或多表查询
查找 table1 中找到所有的记录行,其 id 值没有出现在 table2 中
不是用
select table1.* from table1 where id not in (select id from table2)
而是用join语句
SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL;
查询目的是为了查询属于不同分类的所有产品. 注意: mysql 3.22以下版本不支持子查询, 但所有的子查询都可以使用
LEFT JOIN 或 and语句替代. create table table1 (
pid char (10) not null ,
sid char (10) not null
); insert into table1 values ("apple","1");
insert into table1 values ("apple","2");
insert into table1 values ("apple","3");
insert into table1 values ("apple","4");
insert into table1 values ("apple","5");
insert into table1 values ("apple","6");
insert into table1 values ("pear","1");
insert into table1 values ("pear","3");
insert into table1 values ("pear","4");
insert into table1 values ("pear","7");
insert into table1 values ("orange","1");
insert into table1 values ("orange","2");
insert into table1 values ("orange","3"); 查询方法
-------- 设定分类有3类, 我们希望查询这些分类中分属以下类型的产品: sort 1 sort 2 sort 3
---------------------------
1 -- 3 -- ( 2 | 4 ) 我们使用如下的SQL语句: select t1.pid from table1 as t1
LEFT JOIN table1 as t2 on t1.pid=t2.pid
LEFT JOIN table1 as t3 on t1.pid=t3.pid
where t1.sid=1 and t2.sid=3 and (t3.sid=2 or t3.sid=4)
group by pid; 结果为: +-------+
|t1.pid |
+-------+
|apple |
|pear |
|orange |
+-------+
如果希望查询分类如下: sort 1 sort 2 sort 3
--------------------------------------
( 1 | 5 ) -- ( 3 | 6 | 7 ) -- 4 使用如下的SQL语句: select t1.pid from table1 as t1
LEFT JOIN table1 as t2 on t1.pid=t2.pid
LEFT JOIN table1 as t3 on t1.pid=t3.pid
where ( t1.sid=1 or t1.sid=5 )
and (t2.sid=3 or t2.sid=6 or t2.sid=7)
and (t3.sid=2 or t3.sid=4)
group by pid; 结果为: +------+
|t1.pid|
+------+
|apple |
|pear |
+------+ 如果有更多的分类, 并且希望查询的分类如下:
sort 1 sort 2 sort 3 sort 4
--------------------------------------
( 1 ) -- ( 3 ) -- ( 2 | 4) -- ( 5 ) 我们使用如下语句: select t1.pid from table1 as t1
LEFT JOIN table1 as t2 on t1.pid=t2.pid
LEFT JOIN table1 as t3 on t1.pid=t3.pid
LEFT JOIN table1 as t4 on t1.pid=t4.pid
where t1.sid=1
and t2.sid=3
and (t3.sid=2 or t3.sid=4)
and t4.sid=5
group by pid;
结果为: +------+
|t1.pid|
+------+
|apple |
+------+
//_________用重命名,也可JOIN语句!!!可共时关连多个数据库!!!
from project
where project_id in
(select distinct task_inproject
from task
where task_leader='$employee_name')
本身好象就有错误的,即使支持子查询,也得这样写吧?
select *
from project
where project_id in
(select distinct project_id
from task
where task_leader='$employee_name')