/*
这个条件没看懂
比如我要找出(AttributeID=1且AttributeValue=VS1或VS2)且(AttributeID=2且AttributeValue=VG)且(AttributeID=4且AttributeValue=E)的产品ID?
*/
-- 猜两个
SELECT * FROM ProductPart WHERE AttributeID IN(1,2,4) AND AttributeValue IN('VS1','VS2','VG','E')
--OR
SELECT * FROM ProductPart WHERE AttributeID IN(1,2,4) OR AttributeValue IN('VS1','VS2','VG','E')
这个条件没看懂
比如我要找出(AttributeID=1且AttributeValue=VS1或VS2)且(AttributeID=2且AttributeValue=VG)且(AttributeID=4且AttributeValue=E)的产品ID?
*/
-- 猜两个
SELECT * FROM ProductPart WHERE AttributeID IN(1,2,4) AND AttributeValue IN('VS1','VS2','VG','E')
--OR
SELECT * FROM ProductPart WHERE AttributeID IN(1,2,4) OR AttributeValue IN('VS1','VS2','VG','E')
解决方案 »
- sql2008里怎么把活动监视器里耗掉大量资源的查询停掉?
- 求一条SQL题????^^^^^^^^^^^^^^^^^^^
- 放分求一存储过程
- sql server2008不能使用中文模糊查询
- 如何求出两个表的交值、差值?
- user和login 的区别?
- 一个十分恐怖的问题,ms sql server中数据库日志文件竟然有2G那么大,如何让这个文件变小,谢谢!
- 数据库还原?????????
- sql server7.0复制?
- sql查询效率问题
- 有没有办法做sql server 2005到sql server 2000的 log shipping
- sql05里,执行一个过程,总是提示:必须声明标量变量 "@cid"。如何解决???新手求助!
(select id from
(
select distinct id from tb where AttributeID=1 and AttributeValue in ('VS1','VS2')
select distinct id from tb where AttributeID=2 and AttributeValue = 'VG'
select distinct id from tb where AttributeID=4 and AttributeValue = 'E'
) t
group by id having count(*) = 3
)
(select PID from
(
select distinct PID from tb where AttributeID=1 and AttributeValue in ('VS1','VS2')
select distinct PID from tb where AttributeID=2 and AttributeValue = 'VG'
select distinct PID from tb where AttributeID=4 and AttributeValue = 'E'
) t
group by PID having count(*) = 3
)
insert into tb values(2 , 65 ,12 ,'7.5')
insert into tb values(3 , 1 ,1 ,'VS1')
insert into tb values(5 , 1 ,3 ,'0.08')
insert into tb values(7 , 2 ,5 ,'黄金(18K白金)')
insert into tb values(8 , 2 ,6 ,'0.8')
insert into tb values(11, 1 ,4 ,'E')
insert into tb values(12, 16 ,12 ,'10.5')
insert into tb values(13, 1 ,2 ,'VG')
insert into tb values(16, 68 ,12 ,'2.5')
insert into tb values(17, 69 ,12 ,'3.6')
insert into tb values(18, 70 ,12 ,'4.8')
insert into tb values(19, 71 ,12 ,'2.5')
insert into tb values(20, 73 ,12 ,'3.5')
insert into tb values(21, 74 ,12 ,'9.8')
insert into tb values(22, 75 ,12 ,'2.6')
insert into tb values(23, 76 ,12 ,'2.6')
insert into tb values(24, 78 ,5 ,'黄金(18K白金)')
insert into tb values(25, 77 ,12 ,'2.3')
insert into tb values(26, 79 ,12 ,'10.8')
insert into tb values(27, 80 ,24 ,'2000')
goselect * from tb where PID in
(select PID from
(
select distinct PID from tb where AttributeID=1 and AttributeValue in ('VS1','VS2')
union all
select distinct PID from tb where AttributeID=2 and AttributeValue = 'VG'
union all
select distinct PID from tb where AttributeID=4 and AttributeValue = 'E'
) t
group by PID having count(*) = 3
)drop table tb /*
ID PID AttributeID AttributeValue
----------- ----------- ----------- --------------------
3 1 1 VS1
5 1 3 0.08
11 1 4 E
13 1 2 VG(所影响的行数为 4 行)
*/
(select PID from
(
select distinct PID from tb where AttributeID=1 and AttributeValue in ('VS1','VS2')
INTERSECT
select distinct PID from tb where AttributeID=2 and AttributeValue = 'VG'
INTERSECT
select distinct PID from tb where AttributeID=4 and AttributeValue = 'E'
) t