属性ID 产品ID 属性值
100001 4510 15P
100001 4511 15P
100002 4510 20L
100002 4511 18L
100003 4510 40W
100004 4510 36V
100001 4516 18P
100005 4510 26A
100006 4510 88G
100004 4511 24V
100003 4516 15W现在要取出
属性ID100001等于15P
并且
属性ID100004等于24L
并且
属性ID100006等于88G
的产品ID不够在加分!!!
100001 4510 15P
100001 4511 15P
100002 4510 20L
100002 4511 18L
100003 4510 40W
100004 4510 36V
100001 4516 18P
100005 4510 26A
100006 4510 88G
100004 4511 24V
100003 4516 15W现在要取出
属性ID100001等于15P
并且
属性ID100004等于24L
并且
属性ID100006等于88G
的产品ID不够在加分!!!
where 产品ID=a.产品ID and 属性ID=100001 and 属性值='15P')and exists(select 1 from 表
where 产品ID=a.产品ID and 属性ID=100004 and 属性值='24L')and exists(select 1 from 表
where 产品ID=a.产品ID and 属性ID=100006 and 属性值='88G')
Declare @t Table(属性ID varchar(10),产品ID int,属性值 varchar(20))
Insert @t Select '100001',4510,'15P'
Union All Select '100001',4511,'15P'
Union All Select '100002',4510,'20L'
Union All Select '100002',4511,'18L'
Union All Select '100003',4510,'40W'
Union All Select '100004',4510,'36V'
Union All Select '100001',4516,'18P'
Union All Select '100005',4510,'26A'
Union All Select '100006',4510,'88G'
Union All Select '100004',4511,'24V'
Union All Select '100003',4516,'15W'
--Test
Select * From @t
Where (属性ID='100001' And 属性值='15P')
Or (属性ID='100004' And 属性值='24L')
Or (属性ID='100006' And 属性值='88G')
From TableName A
Inner Join TableName B
On A.产品ID=B.产品ID
Inner Join TableName C
On A.产品ID=C.产品ID
Where A.属性ID='100001' And A.属性值='15P'
And B.属性ID='100004' And B.属性值='24L'
And C.属性ID='100006' And C.属性值='88G'
忘了说一点我想要的是唯一的产品ID
FROM(
SELECT DISTINCT
产品ID, 属性ID, 属性值
FROM tb
WHERE
属性ID=100001 AND 属性值='15P' OR
属性ID=100004 AND 属性值='24L' OR
属性ID=100006 AND 属性值='88G'
)A
GROUP BY 产品ID
HAVING COUNT(*) = 3
是要1,取出三个条件各自己的记录
SELECT 产品ID FROM 表
WHERE 属性ID='100001' AND 属性值='15P'
UNION
SELECT 产品ID FROM 表
WHERE 属性ID='100004' AND 属性值='24L'
UNION
SELECT 产品ID FROM 表
WHERE 属性ID='100006' AND 属性值='88G'
还是要2,取出产品ID在三个条件中都存在的产品ID
SELECT 产品ID FROM
(SELECT 产品ID FROM 表
WHERE 属性ID='100001' AND 属性值='15P'
) a
INNER JOIN
(SELECT 产品ID FROM 表
WHERE 属性ID='100004' AND 属性值='24L'
) b
ON a.产品ID=b.产品ID
INNER JOIN
(SELECT 产品ID FROM 表
WHERE 属性ID='100006' AND 属性值='88G'
) c
ON b.产品ID=C.产品ID或者是其它什么意思
(
属性ID varchar(20),
产品ID varchar(20),
属性值 varchar(10)
)
insert into te
select '100001', '4510','15P' union all
select '100001' , '4511', '15P' union all
select '100002' , '4510', '20L' union all
select '100002' , '4511', '18L' union all
select '100003' , '4510', '40W' union all
select '100004' , '4510', '36V' union all
select '100001' , '4516', '18P' union all
select '100005' , '4510', '26A' union all
select '100006' , '4511', '88G' union all
select '100004' , '4511', '24L' union all
select '100003' , '4516', '15W'select distinct 产品ID from te t
where exists(select 1 from te
where 产品ID=t.产品ID and 属性ID='100001' and 属性值='15P') and exists(select 1 from te
where 产品ID=t.产品ID and 属性ID='100004' and 属性值='24L') and exists(select 1 from te
where 产品ID=t.产品ID and 属性ID='100006' and 属性值='88G')
楼主给出的数据并没有符合条件的产品ID,我把数据改了一下