根据产品属性搜索产品sql

需求：

每个产品都有N个属性，现在前端要根据属性要筛选产品，每个所选属性都符合的产品要搜索出来

表结构及数据(ms sqlserver)：

CREATE TABLE [dbo].[ProductTag](
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[ProductId] [int] NOT NULL,
	[AttributeId] [int] NOT NULL,
 CONSTRAINT [PK_ProductTag] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ProductTag] ON 

INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (6, 13, 2)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (7, 13, 5)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (8, 13, 13)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (9, 13, 23)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (10, 13, 33)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (13, 12, 19)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (14, 12, 7)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (15, 12, 5)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (16, 12, 13)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (17, 12, 25)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (18, 14, 2)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (19, 14, 5)
INSERT [dbo].[ProductTag] ([Id], [ProductId], [AttributeId]) VALUES (20, 14, 13)
SET IDENTITY_INSERT [dbo].[ProductTag] OFF
ALTER TABLE [dbo].[ProductTag] ADD  CONSTRAINT [DF_ProductTag_ProductId]  DEFAULT ((0)) FOR [ProductId]
GO
ALTER TABLE [dbo].[ProductTag] ADD  CONSTRAINT [DF_ProductTag_AttributeId]  DEFAULT ((0)) FOR [AttributeId]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'产品Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProductTag', @level2type=N'COLUMN',@level2name=N'ProductId'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'属性Id' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'ProductTag', @level2type=N'COLUMN',@level2name=N'AttributeId'
GO

sql:

写法1【有问题的写法】:

select ProductId from ProductTag where AttributeId in(2,5)

结果：

13    
13    
12    
14    
14    

显示结果有问题： ProductId=12的不应该出来，它只有属性=5符合，属性=2不符合， in是条件or,所以不能这么写

 

写法2【不推荐】：

select ProductId from (
select * from ProductTag where ProductId in(select ProductId from productTag where AttributeId in(2))) a where AttributeId=5

结果：

13
14

此写法数据取得是对的，如果筛选条件多，则嵌套会很多，不推荐

 

写法3

select ProductId from productTag where AttributeId in(2,5,13) group by ProductId having count(ProductId)=3

结果：

13
14

select ProductId from productTag where AttributeId in(2,5,13,23) group by ProductId having count(ProductId)=4

结果：

13

结果都是对的，having count(ProductId)=4 此处的数字则是筛选属性的个数

 

总结：

写法3比较好，如果有更好的写法，或者此类需求的表设计，请发消息告知，不胜感激，谢谢！

 

--- end ---