标题可能说得和需求相差甚远...(汗,多但待)表Specifications
SpecId ProductId No Stock
1 103 4343 13
2 103 4343 5
3 103 4343 48
4 103 4343 30
5 103 4343 15
6 103 4343 25
7 103 4343 20
8 103 4343 15
9 103 4343 12表Items
ItemId SpecId Type SpecValue SpecRe1 1 1 1eddff 天蓝色
2 1 2 S S
3 2 1 1eddff 天蓝色
4 2 2 M M
5 3 1 1eddff 天蓝色
6 3 2 L L
7 4 1 ffb6c1 粉红色
8 4 2 S S
9 5 1 ffb6c1 粉红色
10 5 2 M M
11 6 1 ffb6c1 粉红色
12 6 2 L L
13 7 1 dda0dd 紫罗兰
14 7 2 S S
15 8 1 dda0dd 紫罗兰
16 8 2 M M
17 9 1 dda0dd 紫罗兰
18 9 2 L L需求是传入 2个SpecValue和1个ProductId 如果匹配就返回SpecId。比如传入ProductId 103,SpecValue 'dda0dd'和'L'返回SpecId是9。SpecValue可能会传入多个值(例子表是2个值对应一个SpecId),而这多个组合为一起才是条件,另外为了保证唯一加上了ProductId.select spec.SpecId from Specifications AS spec
LEFT JOIN Items AS items ON items.SpecId=spec.SpecId
WHERE spec.ProductId=103 AND items.SpecValue='dda0dd' AND items.SpecValue='L'
SpecId ProductId No Stock
1 103 4343 13
2 103 4343 5
3 103 4343 48
4 103 4343 30
5 103 4343 15
6 103 4343 25
7 103 4343 20
8 103 4343 15
9 103 4343 12表Items
ItemId SpecId Type SpecValue SpecRe1 1 1 1eddff 天蓝色
2 1 2 S S
3 2 1 1eddff 天蓝色
4 2 2 M M
5 3 1 1eddff 天蓝色
6 3 2 L L
7 4 1 ffb6c1 粉红色
8 4 2 S S
9 5 1 ffb6c1 粉红色
10 5 2 M M
11 6 1 ffb6c1 粉红色
12 6 2 L L
13 7 1 dda0dd 紫罗兰
14 7 2 S S
15 8 1 dda0dd 紫罗兰
16 8 2 M M
17 9 1 dda0dd 紫罗兰
18 9 2 L L需求是传入 2个SpecValue和1个ProductId 如果匹配就返回SpecId。比如传入ProductId 103,SpecValue 'dda0dd'和'L'返回SpecId是9。SpecValue可能会传入多个值(例子表是2个值对应一个SpecId),而这多个组合为一起才是条件,另外为了保证唯一加上了ProductId.select spec.SpecId from Specifications AS spec
LEFT JOIN Items AS items ON items.SpecId=spec.SpecId
WHERE spec.ProductId=103 AND items.SpecValue='dda0dd' AND items.SpecValue='L'
select spec.SpecId from Specifications AS spec
LEFT JOIN Items AS i ON i.SpecId=spec.SpecId
WHERE spec.ProductId=103 AND exists(select 1 from Items where SpecId=i.SpecId and SpecValue='dda0dd' )
AND exists(select 1 from Items where SpecId=i.SpecId and SpecValue='L')
返回了2个值,任何时候只能有一个值返回。。
LEFT JOIN Items AS items ON items.SpecId=spec.SpecId
WHERE spec.ProductId=103 AND (items.SpecValue='dda0dd' or items.SpecValue='L')
select spec.SpecId from Specifications AS spec
LEFT JOIN Items AS items1 ON spec.SpecId=items1.SpecId
LEFT JOIN Items AS items2 ON spec.SpecId=items2.SpecId
WHERE spec.ProductId=103 AND items1.SpecValue='dda0dd' and items2.SpecValue='L'
/*
SpecId
-----------
9(所影响的行数为 1 行)
*/
IF OBJECT_ID('[Specifications]') IS NOT NULL
DROP TABLE [Specifications]
GO
CREATE TABLE [Specifications] ([SpecId] [int],[ProductId] [int],[No] [int],[Stock] [int])
INSERT INTO [Specifications]
SELECT '1','103','4343','13' UNION ALL
SELECT '2','103','4343','5' UNION ALL
SELECT '3','103','4343','48' UNION ALL
SELECT '4','103','4343','30' UNION ALL
SELECT '5','103','4343','15' UNION ALL
SELECT '6','103','4343','25' UNION ALL
SELECT '7','103','4343','20' UNION ALL
SELECT '8','103','4343','15' UNION ALL
SELECT '9','103','4343','12'--> 生成测试数据表: [Items]
IF OBJECT_ID('[Items]') IS NOT NULL
DROP TABLE [Items]
GO
CREATE TABLE [Items] ([ItemId] [int],[SpecId] [int],[Type] [int],[SpecValue] [nvarchar](10),[SpecRe] [nvarchar](10))
INSERT INTO [Items]
SELECT '1','1','1','1eddff','天蓝色' UNION ALL
SELECT '2','1','2','S','S' UNION ALL
SELECT '3','2','1','1eddff','天蓝色' UNION ALL
SELECT '4','2','2','M','M' UNION ALL
SELECT '5','3','1','1eddff','天蓝色' UNION ALL
SELECT '6','3','2','L','L' UNION ALL
SELECT '7','4','1','ffb6c1','粉红色' UNION ALL
SELECT '8','4','2','S','S' UNION ALL
SELECT '9','5','1','ffb6c1','粉红色' UNION ALL
SELECT '10','5','2','M','M' UNION ALL
SELECT '11','6','1','ffb6c1','粉红色' UNION ALL
SELECT '12','6','2','L','L' UNION ALL
SELECT '13','7','1','dda0dd','紫罗兰' UNION ALL
SELECT '14','7','2','S','S' UNION ALL
SELECT '15','8','1','dda0dd','紫罗兰' UNION ALL
SELECT '16','8','2','M','M' UNION ALL
SELECT '17','9','1','dda0dd','紫罗兰' UNION ALL
SELECT '18','9','2','L','L'--SELECT * FROM [Specifications]
--SELECT * FROM [Items]-->SQL查询如下:
SELECT spec.SpecId
FROM Specifications AS spec
LEFT JOIN Items AS i
ON i.SpecId = spec.SpecId
WHERE spec.ProductId = 103
AND i.SpecValue IN ('dda0dd', 'L')
GROUP BY spec.SpecId
HAVING COUNT(1)>1
/*
SpecId
-----------
9(1 行受影响)
*/
insert into Specifications values(1 ,103 ,4343 ,13)
insert into Specifications values(2 ,103 ,4343 ,5 )
insert into Specifications values(3 ,103 ,4343 ,48)
insert into Specifications values(4 ,103 ,4343 ,30)
insert into Specifications values(5 ,103 ,4343 ,15)
insert into Specifications values(6 ,103 ,4343 ,25)
insert into Specifications values(7 ,103 ,4343 ,20)
insert into Specifications values(8 ,103 ,4343 ,15)
insert into Specifications values(9 ,103 ,4343 ,12)
create table Items(ItemId int,SpecId int,Type int,SpecValue varchar(10),SpecRe varchar(10))
insert into Items values(1 ,1 ,1 ,'1eddff' ,'天蓝色')
insert into Items values(2 ,1 ,2 ,'S' ,'S')
insert into Items values(3 ,2 ,1 ,'1eddff' ,'天蓝色')
insert into Items values(4 ,2 ,2 ,'M' ,'M')
insert into Items values(5 ,3 ,1 ,'1eddff' ,'天蓝色')
insert into Items values(6 ,3 ,2 ,'L' ,'L')
insert into Items values(7 ,4 ,1 ,'ffb6c1' ,'粉红色')
insert into Items values(8 ,4 ,2 ,'S' ,'S')
insert into Items values(9 ,5 ,1 ,'ffb6c1' ,'粉红色')
insert into Items values(10, 5, 2,'M' ,'M')
insert into Items values(11, 6, 1,'ffb6c1' ,'粉红色')
insert into Items values(12, 6, 2,'L' ,'L')
insert into Items values(13, 7, 1,'dda0dd' ,'紫罗兰')
insert into Items values(14, 7, 2,'S' ,'S')
insert into Items values(15, 8, 1,'dda0dd' ,'紫罗兰')
insert into Items values(16, 8, 2,'M' ,'M')
insert into Items values(17, 9, 1,'dda0dd' ,'紫罗兰')
insert into Items values(18, 9, 2,'L' ,'L')
goselect spec.SpecId from Specifications AS spec
LEFT JOIN Items AS items1 ON spec.SpecId=items1.SpecId
LEFT JOIN Items AS items2 ON spec.SpecId=items2.SpecId
WHERE spec.ProductId=103 AND items1.SpecValue='dda0dd' and items2.SpecValue='L'
/*
SpecId
-----------
9(所影响的行数为 1 行)
*/select specid from Specifications where ProductId=103 and specid in
(
select SpecId from
(
select distinct SpecId from items where SpecValue='dda0dd'
union all
select distinct SpecId from items where SpecValue='L'
) t group by SpecId having count(1) = 2
)
/*
SpecId
-----------
9(所影响的行数为 1 行)
*/drop table Specifications ,Items
select spec.SpecId from Specifications AS spec
LEFT JOIN Items AS items1 ON spec.SpecId=items1.SpecId
LEFT JOIN Items AS items2 ON spec.SpecId=items2.SpecId
WHERE spec.ProductId=103 AND items1.SpecValue='dda0dd' and items2.SpecValue='L'不过,你测试一下即知道.