表A
code properties
001 属性1
001 属性2
001 属性3
002 属性1
003 属性3
... .....
每一个code 有多少个属性不一定表B
autoID condition selectcondition
1 属性1 aaa
2 属性2 bbb
3 属性3 aaa
4 属性4 ccc通过输入selectcondition,查询出condition,再通过查询出的condition在a表中查询出完全符合条件的code
比如输入为aaa,那么最终要查询出的结果为001
code properties
001 属性1
001 属性2
001 属性3
002 属性1
003 属性3
... .....
每一个code 有多少个属性不一定表B
autoID condition selectcondition
1 属性1 aaa
2 属性2 bbb
3 属性3 aaa
4 属性4 ccc通过输入selectcondition,查询出condition,再通过查询出的condition在a表中查询出完全符合条件的code
比如输入为aaa,那么最终要查询出的结果为001
insert into ta select '001','属性1'
insert into ta select '001','属性2'
insert into ta select '001','属性3'
insert into ta select '002','属性1'
insert into ta select '003','属性3'create table tb(autoID int, condition varchar(10),properties varchar(10))
insert into tb select 1,'属性1','aaa'
insert into tb select 2,'属性2','bbb'
insert into tb select 3,'属性3','aaa'
insert into tb select 4,'属性4','ccc'--通过输入selectcondition,查询出condition,再通过查询出的condition在a表中查询出完全符合条件的code
--比如输入为aaa,那么最终要查询出的结果为001
select code from ta a where properties in(
select condition from tb where properties='aaa')
group by code
having count(1)=(select count(1) from tb where properties='aaa')
比如输入为aaa,那么最终要查询出的结果为001
--
貌似输入aaa 可以查出 001和002吧??
莫非是我理解错了?
code properties
001 属性1
001 属性2
001 属性3
002 属性1
003 属性3
insert into ta select '001','属性1'
insert into ta select '001','属性2'
insert into ta select '001','属性3'
insert into ta select '002','属性1'
insert into ta select '003','属性3'create table tb(autoID int, condition varchar(10),properties varchar(10))
insert into tb select 1,'属性1','aaa'
insert into tb select 2,'属性2','bbb'
insert into tb select 3,'属性3','aaa'
insert into tb select 4,'属性4','ccc'--通过输入selectcondition,查询出condition,再通过查询出的condition在a表中查询出完全符合条件的code
--比如输入为aaa,那么最终要查询出的结果为001
select code from ta a where not exists(
select 1 from tb where properties='aaa' and condition=a.properties
)001
declare @tb table ([code] nvarchar(5),[properties] nvarchar(3))
Insert into @tb
select '001','属性1' union all
select '001','属性2' union all
select '001','属性3' union all
select '002','属性1' union all
select '003','属性3'
--Select * from @tbdeclare @tb2 table ([autoID] int,[condition] nvarchar(3),[selectcondition] nvarchar(3))
Insert into @tb2
select 1,'属性1','aaa' union all
select 2,'属性2','bbb' union all
select 3,'属性3','aaa' union all
select 4,'属性4','ccc'
--Select * from @tb2select a.code from @tb a
where a.[properties] in
(select [condition] from @tb2 where [selectcondition] ='aaa')
group by a.code
having (count(1) = (select count(1) from @tb2 where [selectcondition] ='aaa'))
/*
code
-----
001
*/