根据产品属性搜索产品sql

超、凢脫俗 2023-02-27 05:42 61阅读 0赞

# 需求： #

每个产品都有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

结果：

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

### 总结： ###

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