以下建立一个demo表和一些数据, 其中pid为产品编号, sid为分类编号
查询目的是为了查询属于不同分类的所有产品.
注意: mysql MySQL4.05以下版本不支持子查询, 但所有的子查询都可以使用
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 ¦
+------+
---------------------------------------------------------------
收藏，但 MySQL3.23 也不支持子查询的。
---------------------------------------------------------------
收藏，但 目前MySQL4.05 也不支持子查询的。
---------------------------------------------------------------
up!