如何在记录中过滤产品 [英] how to filter product wise in record
问题描述
如何筛选产品明智的记录示例选择尺寸或适合产品强制性的产品展示
1.tbl_size
---------- -------------------------------------------------- ----------------
USE [库存]
GO
/ **** **对象：表[dbo]。[tbl_size]脚本日期：09/29/2014 11:21:41 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]。[tbl_size]（
[cate_id ] [bigint] IDENTITY（1,1）NOT NULL，
[Size] [nvarchar]（50）COLLATE Latin1_General_CI_AI NULL，
[product_id] [bigint] NULL，
[list_id] [bigint] NULL，
CONSTRAINT [PK_Person_choicdetail] PRIMARY KEY CLUSTERED
（
[cate_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
ALTER TABLE [dbo]。[tbl_size] WITH CHECK ADD CONSTRAINT [FK_Person_choicdetail_Products] FOREIGN KEY（[product_id]）
REFERENCES [dbo]。[Products]（[ ProductID]）
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo]。[tbl_size ] CHECK CONSTRAINT [FK_Person_choicdetail_Products]
GO
ALTER TABLE [dbo]。[tbl_size] with CHECK ADD CONSTRAINT [FK_tbl_size_Category_List] FOREIGN KEY（[list_id]）
REFERENCES [dbo]。[Category_List]（[list_id]）
GO
ALTER TABLE [dbo]。[tbl_size] CHECK CONSTRAINT [FK_tbl_size_Category_List]
2.tbl_fit
------------------------- -------------------------------------------------- ---
USE [库存]
GO
/ ******对象：表[dbo]。[tbl_Fit]脚本日期：09/29/2014 11:22:29 ****** /
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREA TE TABLE [dbo]。[tbl_Fit]（
[fit_id] [int] IDENTITY（1,1）NOT NULL，
[Fit] [nchar]（10） COLLATE Latin1_General_CI_AI NULL，
[Fit_desc] [nvarchar]（50）COLLATE Latin1_General_CI_AI NULL，
[product_id] [bigint] NULL，
[ list_id] [bigint] NULL，
CONSTRAINT [fit_id] PRIMARY KEY CLUSTERED
（
[fit_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
ALTER TABLE [dbo]。[tbl_Fit] WITH CHECK ADD CONSTRAINT [FK_tbl_Fit_Category_List] FOREIGN KEY（[list_id]）
REFERENCES [dbo] ]。[Category_List]（[list_id]）
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo]。[tbl_Fit] CHECK CONSTRAINT [FK_tbl_Fit_Category_List]
GO
ALTER TABLE [dbo]。[tbl_Fit] WITH CHECK ADD CONSTRAINT [FK_t bl_Fit_Products]外键（[product_id]）
参考[dbo]。[产品]（[ProductID]）
GO
ALTER TABLE [ dbo]。[tbl_Fit] CHECK CONSTRAINT [FK_tbl_Fit_Products]
3.主表此表记录显示
------- -------------------------------------------------- --------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo]。[产品]（
[ProductID] [bigint] IDENTITY（1,1）NOT NULL ，
[ProductName] [nvarchar]（100）COLLATE Latin1_General_CI_AI NULL，
[描述] [nvarchar]（max）COLLATE Latin1_General_CI_AI NULL，
[Long_Description] [nvarchar]（max）COLLATE Latin1_General_CI_AI NULL，
[CategoryID] [bigint] NULL，
[list_id] [bigint] NULL，
[category] [nvarchar]（50）COLLATE Latin1_General_CI_AI NULL，
[best_seller_count] [int] NULL，
CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED
（
[ProductID] 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
ALTER TABLE [ dbo]。[产品] WITH CHECK ADD CONSTRAINT [FK_Products_Category_List] FOREIGN KEY（[list_id]）
参考[dbo]。[Category_List]（[list_id]）
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo]。[Products] CHECK CONSTRAINT [FK_Products_Category_List]
------------------------------------------------ -------------------------------
和两个表都是过滤记录，它们都是选中的记录显示产品
how to filter product wise record example of size selected or fit wise product shows in product compulsary
1.tbl_size
----------------------------------------------------------------------------
USE [inventory]
GO
/****** Object: Table [dbo].[tbl_size] Script Date: 09/29/2014 11:21:41 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_size](
[cate_id] [bigint] IDENTITY(1,1) NOT NULL,
[Size] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
[product_id] [bigint] NULL,
[list_id] [bigint] NULL,
CONSTRAINT [PK_Person_choicdetail] PRIMARY KEY CLUSTERED
(
[cate_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
ALTER TABLE [dbo].[tbl_size] WITH CHECK ADD CONSTRAINT [FK_Person_choicdetail_Products] FOREIGN KEY([product_id])
REFERENCES [dbo].[Products] ([ProductID])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_size] CHECK CONSTRAINT [FK_Person_choicdetail_Products]
GO
ALTER TABLE [dbo].[tbl_size] WITH CHECK ADD CONSTRAINT [FK_tbl_size_Category_List] FOREIGN KEY([list_id])
REFERENCES [dbo].[Category_List] ([list_id])
GO
ALTER TABLE [dbo].[tbl_size] CHECK CONSTRAINT [FK_tbl_size_Category_List]
2.tbl_fit
------------------------------------------------------------------------------
USE [inventory]
GO
/****** Object: Table [dbo].[tbl_Fit] Script Date: 09/29/2014 11:22:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tbl_Fit](
[fit_id] [int] IDENTITY(1,1) NOT NULL,
[Fit] [nchar](10) COLLATE Latin1_General_CI_AI NULL,
[Fit_desc] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
[product_id] [bigint] NULL,
[list_id] [bigint] NULL,
CONSTRAINT [fit_id] PRIMARY KEY CLUSTERED
(
[fit_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
ALTER TABLE [dbo].[tbl_Fit] WITH CHECK ADD CONSTRAINT [FK_tbl_Fit_Category_List] FOREIGN KEY([list_id])
REFERENCES [dbo].[Category_List] ([list_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[tbl_Fit] CHECK CONSTRAINT [FK_tbl_Fit_Category_List]
GO
ALTER TABLE [dbo].[tbl_Fit] WITH CHECK ADD CONSTRAINT [FK_tbl_Fit_Products] FOREIGN KEY([product_id])
REFERENCES [dbo].[Products] ([ProductID])
GO
ALTER TABLE [dbo].[tbl_Fit] CHECK CONSTRAINT [FK_tbl_Fit_Products]
3.main table this table record show
-----------------------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Products](
[ProductID] [bigint] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](100) COLLATE Latin1_General_CI_AI NULL,
[Description] [nvarchar](max) COLLATE Latin1_General_CI_AI NULL,
[Long_Description] [nvarchar](max) COLLATE Latin1_General_CI_AI NULL,
[CategoryID] [bigint] NULL,
[list_id] [bigint] NULL,
[category] [nvarchar](50) COLLATE Latin1_General_CI_AI NULL,
[best_seller_count] [int] NULL,
CONSTRAINT [PK_dbo.Products] PRIMARY KEY CLUSTERED
(
[ProductID] 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
ALTER TABLE [dbo].[Products] WITH CHECK ADD CONSTRAINT [FK_Products_Category_List] FOREIGN KEY([list_id])
REFERENCES [dbo].[Category_List] ([list_id])
ON UPDATE CASCADE
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[Products] CHECK CONSTRAINT [FK_Products_Category_List]
-------------------------------------------------------------------------------
and two table are filter record which are selected record show to product
推荐答案
SELECT whatever, columns, you, need
FROM
PRODUCTS p
INNER JOIN tbl_Fit f ON p.productId = f.product_id
INNER JOIN tbl_Size s ON p.productId = s.product_id
WHERE
(size = @size OR @size IS NULL)
AND
(fit = @fit OR @fit IS NULL)
1.如果有不合适的产品，请使用LEFT而不是INNER join尺寸条目
2.如果您拨打一张桌子产品，请拨打其他尺寸并调整或重命名产品对于tbl_Products，混合两个命名约定是不好的做法 - 对于ProductId / product_id字段名称也是如此 - 将它们命名为相同。
3. @size和@fit是您可以填写的参数值，查询将根据您发送的内容选择一个或一个。如果您发送一些中性值（例如-1或0），您应该有SET @fit = NULLIF（@fit，-1）in您的存储过程
4.根据您的需求添加ORDER BY
1. Use LEFT instead of INNER join if there are products without fit / size entries
2. If you call one table Products, call others Size and Fit or rename Products to tbl_Products, it is bad practice to mix two naming conventions - same for ProductId / product_id field names - name them the same.
3. @size and @fit are parameters you can fill in with particular values, the query will select by both, one or none depending on what you send in. If you send some neutral value (such as -1 or maybe 0) you should have SET @fit = NULLIF(@fit, -1) in your stored procedure
4. add ORDER BY by whatever your needs are at the end
这篇关于如何在记录中过滤产品的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
