27,581
社区成员
发帖
与我相关
我的任务
分享一个简单,但不是很好弄的sql语句
某个产品属于两个category,比如产品101即属于类3又属于类5.
现在要从中选出不在类4,类5的产品.如何剔除101呢.
表products_to_categories
products_id categories_id
101 3
101 5
202 4
表products
products_id
101
102
103
104
我用语句
select p.products_id from products p,products_to_categories p2c where p.products_id=p2c.categories_id and p2c.categories_id not in(4,5)
目标是剔除产品101,但还是会选出101,因为它虽然属于类5,但也属于类3.
所以上面的语句没办法实现.
现在要从中选出不在类4,类5的产品.如何剔除101呢.
表products_to_categories
products_id categories_id
101 3
101 5
202 4
表products
products_id
101
102
103
104
我用语句
select p.products_id from products p,products_to_categories p2c where p.products_id=p2c.categories_id and p2c.categories_id not in(4,5)
目标是剔除产品101,但还是会选出101,因为它虽然属于类5,但也属于类3.
所以上面的语句没办法实现.
...全文
请发表友善的回复…
发表回复
zhao606 2005-12-01
- 打赏
- 举报
select p.pid from products p left join p_type pt
on p.pid=pt.pid
where ( 5 not in(select pt.tid from p_type pt where pt.pid=p.pid) )and (4 not in (select pt.tid from p_type pt where pt.pid=p.pid))
on p.pid=pt.pid
where ( 5 not in(select pt.tid from p_type pt where pt.pid=p.pid) )and (4 not in (select pt.tid from p_type pt where pt.pid=p.pid))
waterfirer 2005-11-18
- 打赏
- 举报
不用子查询，用minus行吗？
select products_id from products
minus
(select products_id from products_to_categories where categories_id in(4,5))
select products_id from products
minus
(select products_id from products_to_categories where categories_id in(4,5))
gyx999 2005-11-18
- 打赏
- 举报
谢谢回答,如果不用子查询,如何解决呢?其实我用的是mysql,只是这里人气比较旺才帖子这里:)
bugchen888 2005-11-18
- 打赏
- 举报
libin_ftsafe(子陌红尘)正解。
子陌红尘 2005-11-18
- 打赏
- 举报
select
p.products_id
from
products p,
products_to_categories p2c
where
p.products_id=p2c.categories_id
and
not exists(select 1 from products_to_categories where products_id=p.products_id and categories_id in(4,5))
p.products_id
from
products p,
products_to_categories p2c
where
p.products_id=p2c.categories_id
and
not exists(select 1 from products_to_categories where products_id=p.products_id and categories_id in(4,5))
elivehai 2005-11-18
- 打赏
- 举报
不错。。
