select x.员工ID,a.劳保用品ID,a.劳保用品名称
from
(select distinct 员工ID from b)x,a
where a.劳保用品IDnot in(select 劳保用品IDfrom b where b.员工ID=x.员工ID)
from
(select distinct 员工ID from b)x,a
where a.劳保用品IDnot in(select 劳保用品IDfrom b where b.员工ID=x.员工ID)
解决方案 »
- freetds 存储过程返回值的问题
- 有关恢复增量备份的问题
- 如何用SQL语句,在一个表中若指定的字段值存在相同,则删除一条记录?
- 急,sql中用select语句描述出insert,update,delete的问题请高手帮帮我啊,我先谢谢了
- sql里能同时用内连接和左连接吗?
- 这个触发器怎么写?
- 表名字符串拆分动态更新表的存储过程!高难问题!
- 请教各位:应聘工作面试时他们主要问哪些方面的问题?我该如何提前做好准备?
- 为什么我的SQL server可以启动,但SQL server Agent不能启动呢?
- 如何在两台机子间用modem传送文件
- 如何求一个日期型字段的平均值?
- MSSQL中关于帐户、数据库用户、角色之间的关系
from
(select distinct 员工ID from b)x,a
where a.劳保用品ID not in (select 劳保用品ID from b where b.员工ID=x.员工ID)
if object_id('tbl_t') is null
print 'not exists'
else
drop table tbl_t
create table tbl_t
(
v_id varchar(20),
v_nm varchar(20)
)
insert into tbl_t
select
'001' , '口罩'
union select
'002' , '洗衣粉'
union select
'003' , '护目镜'
union select
'004' , '防护鞋'
create table tbl_t1
(
v_eid varchar(20),
v_id varchar(20),
v_nm varchar(20)
)
insert into tbl_t1
select
'1001' , '001' , '口罩'
union select
'1001' , '002' , '洗衣粉'
union select
'1002' , '002' , '洗衣粉'
union select
'1002' , '003' , '护目镜'
select distinct t2.* from tbl_t1 t left join (select * from (select distinct v_eid from tbl_t1) t1 cross join tbl_t) t2
on t.v_eid=t2.v_eid and t.v_id<>t2.v_id where t2.v_id not in(select v_id from tbl_t1 where v_eid=t2.v_eid)select * from tbl_t a cross join tbl_t1 b on a.v_id<>b.v_id
select
--删除表
drop table tbl_t
drop table tbl_t1--结果:
/*
v_eid v_id v_nm
-------------------- -------------------- --------------------
1001 003 护目镜
1001 004 防护鞋
1002 001 口罩
1002 004 防护鞋(所影响的行数为 4 行)
*/
select distinct b.v_eid,a.v_id,a.v_nm from tbl_t a cross join tbl_t1 b where a.v_id not in(select v_id from tbl_t1 where v_eid=b.v_eid)v_eid v_id v_nm
-------------------- -------------------- --------------------
1001 003 护目镜
1001 004 防护鞋
1002 001 口罩
1002 004 防护鞋(所影响的行数为 4 行)
from (select 员工id
from b
group by 员工id) t,a
where not exists (select 1 from b where 员工id = t.员工id and 劳保用品id = a.id)
order by 员工id
select *
from (select v_eid
from tbl_t1
group by v_eid) t,tbl_t
where not exists (select 1 from tbl_t1 where v_eid = t.v_eid and v_id = tbl_t.v_id)
order by v_eid
结果;
v_eid v_id v_nm
-------------------- -------------------- --------------------
1001 003 护目镜
1001 004 防护鞋
1002 001 口罩
1002 004 防护鞋(所影响的行数为 4 行)