select a.* from (select * from 部门表 cross join 商品表) a left join 库存表 on a.dep=b.dep and a.id=b.id where a.dep is null
select * from ( select * from 部门表,商品表 ) a where not exists(select * from 库存表 where dep=a.dep and id=a.id)
--------------部門表----------- Create Table dept(dep varchar(1)) insert into Dept select 'A' union select 'B' union select 'C' -----------商品表------------- Create Table kk(id int) insert into kk select 1 union select 2 union select 3 ---------庫存表--------------- create table stock(dep varchar(1),id int) insert into stock select 'A' , 1 union select 'A' , 2 union select 'A' , 3 union select 'B' , 1 union select 'B' , 2 union select 'B' , 3 union select 'C', 2 union select 'C', 3 -----------實現語句------------------------ select * from (select * from dept cross join kk ) a where not exists(select * from stock where dep=a.dep and id=a.id) --------------輸出語句--------------------- dep id ----- ----- C 1
写了一段,测试过没问题,就是每个部门都要单独写单独测,比较麻烦!select id from 商品表 where dep not in ( select id from 库存表 where (dep = A) and (id = 库存表.id) )
就用這條就行了,不要那麼麻煩的select * from (select * from 部门表 cross join 商品表 ) a where not exists(select * from 库存表 where dep=a.dep and id=a.id)
select * from ( select * from 部门表,商品表 ) a where not exists(select * from 库存表 where dep=a.dep and id=a.id)
(select * from 部门表 cross join 商品表) a
left join 库存表 on a.dep=b.dep and a.id=b.id
where a.dep is null
(
select * from 部门表,商品表
) a
where not exists(select * from 库存表 where dep=a.dep and id=a.id)
Create Table dept(dep varchar(1))
insert into Dept
select 'A' union
select 'B' union
select 'C'
-----------商品表-------------
Create Table kk(id int)
insert into kk
select 1 union select 2 union select 3
---------庫存表---------------
create table stock(dep varchar(1),id int)
insert into stock
select 'A' , 1 union
select 'A' , 2 union
select 'A' , 3 union
select 'B' , 1 union
select 'B' , 2 union
select 'B' , 3 union
select 'C', 2 union
select 'C', 3
-----------實現語句------------------------
select * from
(select * from dept cross join kk ) a
where not exists(select * from stock where dep=a.dep and id=a.id)
--------------輸出語句---------------------
dep id
----- -----
C 1
from 商品表
where dep not in
(
select id
from 库存表
where (dep = A) and (id = 库存表.id)
)
(select * from 部门表 cross join 商品表 ) a
where not exists(select * from 库存表 where dep=a.dep and id=a.id)
(
select * from 部门表,商品表
) a
where not exists(select * from 库存表 where dep=a.dep and id=a.id)