现有三个表
商品表A(A_ID,A_NAME),
销售部门表B(B_ID,B_NAME),
销售记录表C(C_ID,B_ID,A_ID,C_TIME)求:销售过所有商品的销售部门号,用一句SQL语句实现
商品表A(A_ID,A_NAME),
销售部门表B(B_ID,B_NAME),
销售记录表C(C_ID,B_ID,A_ID,C_TIME)求:销售过所有商品的销售部门号,用一句SQL语句实现
(select b.b_name from b where b.b_id=c.b_id),
(select a.b_name from a where a.a_id=c.a_id),
c.c_time
from cok?
FROM A,C
GROUP BY C.B_ID
HAVING count(distinct(C.A_ID))=COUNT(A.A_ID)
取这个部门销售的种类数量和总商品的种类数量比较
如果相等就是销售了所有的商品
就不用关联B表了
楼上的方法要将a.a_id也加上distinct,还要加连接条件才行
可以试试select t1.b_id
from
(select b_id,count(distinct a_id)c from c group by b_id)t1,
(select count(1)c from a)t2
where t1.c=t2.c
select b_id from (
select b_id,count(distinct a_id)c from c
group by b_id)
where c=(select count(1) from a)
from
(select count(1)as countA from A) A,C
where
A.countA = c.countC
group by C.B_ID
我的sql不怎么好,没有试过,大概思路是这样的,你跑跑看,可能稍微修改一下。