select
b.softwareName,c.computer_id
from
(select distinct softwareName from A) b,
(select distinct computer_id from A) c
where
not exists(select 1 from A where softwareName=b.softwareName and computer_id=c.computer_id)
and
b.softwareName in('软件1','软件3')
b.softwareName,c.computer_id
from
(select distinct softwareName from A) b,
(select distinct computer_id from A) c
where
not exists(select 1 from A where softwareName=b.softwareName and computer_id=c.computer_id)
and
b.softwareName in('软件1','软件3')
解决方案 »
- 查询前50%的数据怎么弄?
- 如何切换为完整日志恢复模式(sql2k、2k5)
- 如何提高SQL运行效率(二): 如何以最快的速度判断一个SQL选择查询语句是否正确?
- 100分求两个联合查询的句子,会者应该很简单,在线等
- 编程时利用怎样利用sqlserver 读取oracle数据库的数据?
- 关于top 和 distinct 的使用问题
- 各位大虾,请教如果一个SQL语句涉及到两个不同的数据库表的话,那么执行这个SQL语句的conn该选择那个?
- 如何更写一个更新表的SQL语句
- 关于用提取两张关联表不重复的数据问题
- 请问在SQL SERVER中怎样使用语句得到所有的数据库名
- 大家帮帮忙!提示:当没有用 EXISTS 引入子查询时,在选择列表中只能指定一个表达式
- 为什么插入汉字变成?
create table A(computer_id varchar(20),softwareName varchar(20))
insert into A select '电脑1','软件1'
insert into A select '电脑1','软件2'
insert into A select '电脑2','软件1'
insert into A select '电脑2','软件3'
insert into A select '电脑3','软件4'
--执行查询
select
b.softwareName,c.computer_id
from
(select distinct softwareName from A) b,
(select distinct computer_id from A) c
where
not exists(select 1 from A where softwareName=b.softwareName and computer_id=c.computer_id)
and
b.softwareName in('软件1','软件3')--输出结果
/*
softwareName computer_id
------------ -----------
软件1 电脑3
软件3 电脑1
软件3 电脑3
*/--删除测试数据
drop table A
,t2.softwareName
from (select distinct computer_id from A) t1
join (select distinct softwareName from A)t2 on 1>0
left join A on t1.computer_id=A.computer_id
and t2.softwareName=A.softwareName
where A.softwareName is null
and
t2.softwareName in ('软件1','软件3')
declare @a table(computer_id varchar(20),softwareName varchar(20))
insert into @a
select '电脑1','软件1' union all
select '电脑1','软件2' union all
select '电脑2','软件1' union all
select '电脑2','软件3' union all
select '电脑3','软件4'--处理
select *
from (select *
from (select distinct computer_id from @a) a,
(select '软件1' as softwarename union all select '软件3') b
) new
where not exists(select 1 from @a where computer_id=new.computer_id and softwarename=new.softwarename)/*
结果
------------------------------
电脑1 软件3
电脑3 软件1
电脑3 软件3
*/
(
select tpA.computerId,tpB.softwarename
from
(select distinct computerId from a) tpA,
(select '软件1' as softwarename
union all
select '软件2' as softwarename ) tpB
) Tp
where not exist ... 不想写了..