select productname from table1 a,table2 b where a.productid=b.productid and color='红色' and color='绿色'
select productname from table1 awhere exists(select * from table2 where productid=a.productid and color ='红色') and exists(select * from table2 where productid=a.productid and color ='绿色')
select * from table1 a where exists( select 1 from table1 where productid=a.productid and color='红色' ) and exists( select 1 from table1 where productid=a.productid and color='绿色' )
一对多 , 你肯定要现考虑把table2里面的数据变成1条记录。 什么行转列什么的
select a.* from table1 a where (select count(distinct color)) from table2 b where a.productid=b.productid and b.color in('绿色','红色'))>=2
.. 看来我写错了 应该是: select productname from table1 a where exists(select * from table2 where productid=a.productid and color ='红色') and exists(select * from table2 where productid=a.productid and color ='绿色')
Select productid,productname From table1 a Where productid In (select productid from table2 where color='红色' Or cname='绿色') Group By productid Having Count(pid)=2)
-- ========================================= -- -----------t_mac 小编------------------- --------------------希望有天成为大虾---- -- =========================================IF OBJECT_ID('tb1') IS NOT NULL DROP TABLE tb1 GO CREATE TABLE tb1( productid int, productname varchar(10)) go insert tb1 SELECT 1,'a' UNION ALL SELECT 2,'b' go IF OBJECT_ID('tb2') IS NOT NULL DROP TABLE tb2 GO CREATE TABLE tb2(pid int, productid int, color varchar(10)) go insert tb2 SELECT 1,1,'红色' UNION ALL SELECT 2,1,'绿色' UNION ALL SELECT 3,1,'红色' go select productname from tb1 a where exists(select * from tb2 where productid=a.productid and color ='红色') and exists(select * from tb2 where productid=a.productid and color ='绿色') go productname ----------- a
SELECT productname FROM TABLE1 WHERE productid IN (SELECT productid FROM table2 WHERE color IN('红色','绿色') GROUP BY productid HAVING COUNT(*)>=2)
create table table1(productid int ,productname varchar(10)) insert into table1 select 1,'aa' union all select 2,'bb' union all select 3,'cc' union all select 4,'dd'create table table2(pid int,productid int ,color varchar(10)) insert into table2 select 1,2,'红色' union all select 2,2,'绿色' union all select 3,2,'黑色'union all select 4,1,'绿色'union all select 5,3,'绿色' union all select 6,3,'白色' union all select 7,3,'黄色'union all select 8,4,'红色' union all select 9,4,'绿色' ------------------------------------ Select productid,productname From table1 a Where productid In (select productid from table2 where color='红色' Or color='绿色' Group By productid Having Count(pid)=2 ) /* productid productname ----------- ----------- 2 bb 4 dd(2 行受影响) */
HOHO思路一致我不测试了,呵呵
create table table1(productid int ,productname varchar(10)) insert into table1 select 1,'aa' union all select 2,'bb' union all select 3,'cc' union all select 4,'dd'create table table2(pid int,productid int ,color varchar(10)) insert into table2 select 1,2,'红色' union all select 2,2,'绿色' union all select 3,2,'黑色'union all select 4,1,'绿色'union all select 5,3,'绿色' union all select 6,3,'白色' union all select 7,3,'黄色'union all select 8,4,'红色' union all select 9,4,'绿色' ------------------------------------ --方法一 Select productid,productname From table1 a Where productid In (select productid from table2 where color='红色' or color='绿色' Group By productid Having Count(pid)=2 ) --方法二 select * from table1 a where exists (select 1 from table2 where productid=a.productid and color='红色' ) and exists (select 1 from table2 where productid=a.productid and color='绿色' )drop table table1,table2 /* productid productname ----------- ----------- 2 bb 4 dd(2 行受影响) */
select t1.productname from table1 as t1 inner join table2 as t2 on t1.productid=t2.productid where t2.color='red' intersect select t1.productname from table1 as t1 inner join table2 as t2 on t1.productid=t2.productid where t2.color='green' 或者 select productname from table1 where productid in (select t1.productid from table2 as t1 inner join table2 as t2 on t1.productid=t2.productid where t1.color='red' and t2.color='green')
select productname from table1 a,table2 b where a.productid=b.productid and b.color in('红色' ,'绿色')
select * from table1 a,table2 b where a.productid=b.productid and b.color in('红色','绿色')
select productname from table1 a,table2 b where a.productid=b.productid and color='红色' and color='绿色'
where a.productid=b.productid and color='红色' and color='绿色'
from table1 awhere exists(select * from table2 where productid=a.productid and color ='红色')
and exists(select * from table2 where productid=a.productid and color ='绿色')
where exists(
select 1 from table1 where productid=a.productid and color='红色'
)
and exists(
select 1 from table1 where productid=a.productid and color='绿色'
)
a.*
from
table1 a
where
(select count(distinct color)) from table2 b where a.productid=b.productid and b.color in('绿色','红色'))>=2
看来我写错了
应该是:
select
productname
from
table1 a
where
exists(select * from table2 where productid=a.productid and color ='红色')
and
exists(select * from table2 where productid=a.productid and color ='绿色')
Select productid,productname From table1 a
Where productid In
(select productid from table2
where color='红色' Or cname='绿色')
Group By productid
Having Count(pid)=2)
-- -----------t_mac 小编-------------------
--------------------希望有天成为大虾----
-- =========================================IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1( productid int, productname varchar(10))
go
insert tb1 SELECT 1,'a'
UNION ALL SELECT 2,'b'
go
IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
GO
CREATE TABLE tb2(pid int, productid int, color varchar(10))
go
insert tb2 SELECT 1,1,'红色'
UNION ALL SELECT 2,1,'绿色'
UNION ALL SELECT 3,1,'红色'
go
select productname
from tb1 a
where exists(select * from tb2 where productid=a.productid and color ='红色')
and exists(select * from tb2 where productid=a.productid and color ='绿色')
go
productname
-----------
a
(SELECT productid FROM table2
WHERE color IN('红色','绿色') GROUP BY productid HAVING COUNT(*)>=2)
insert into table1 select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'create table table2(pid int,productid int ,color varchar(10))
insert into table2 select 1,2,'红色'
union all select 2,2,'绿色'
union all select 3,2,'黑色'union all select 4,1,'绿色'union all select 5,3,'绿色'
union all select 6,3,'白色'
union all select 7,3,'黄色'union all select 8,4,'红色'
union all select 9,4,'绿色'
------------------------------------
Select productid,productname From table1 a
Where productid In
(select productid from table2
where color='红色' Or color='绿色'
Group By productid
Having Count(pid)=2
)
/*
productid productname
----------- -----------
2 bb
4 dd(2 行受影响)
*/
insert into table1 select 1,'aa'
union all select 2,'bb'
union all select 3,'cc'
union all select 4,'dd'create table table2(pid int,productid int ,color varchar(10))
insert into table2 select 1,2,'红色'
union all select 2,2,'绿色'
union all select 3,2,'黑色'union all select 4,1,'绿色'union all select 5,3,'绿色'
union all select 6,3,'白色'
union all select 7,3,'黄色'union all select 8,4,'红色'
union all select 9,4,'绿色'
------------------------------------
--方法一
Select productid,productname From table1 a
Where productid In
(select productid from table2
where color='红色' or color='绿色'
Group By productid
Having Count(pid)=2
) --方法二
select * from table1 a
where
exists (select 1 from table2 where productid=a.productid and color='红色' )
and
exists (select 1 from table2 where productid=a.productid and color='绿色' )drop table table1,table2
/*
productid productname
----------- -----------
2 bb
4 dd(2 行受影响)
*/
为什么每张帖都看见T-MAC,任贤齐,梁朝伟,还有那个古装美女不知道是谁,你们是这个版的版主吗?
from table1 as t1 inner join table2 as t2 on t1.productid=t2.productid
where t2.color='red'
intersect
select t1.productname
from table1 as t1 inner join table2 as t2 on t1.productid=t2.productid
where t2.color='green'
或者
select productname from table1
where productid in
(select t1.productid
from table2 as t1 inner join table2 as t2 on t1.productid=t2.productid
where t1.color='red' and t2.color='green')
select productname from table1 a,table2 b
where a.productid=b.productid and b.color in('红色' ,'绿色')
select * from table1 a,table2 b where a.productid=b.productid and b.color in('红色','绿色')
where a.productid=b.productid and color='红色' and color='绿色'