问题描述
最近我进入电子商务应用程序，在这里我想根据类别名称或子类别名称或产品名称搜索产品。在这里，我打算为类别，子类别和产品创建表格。如果我搜索项目，可以是类别名称或子类别名称或产品名称，则应显示产品。
例如。 Men-Shirt-Peterengland / Levis / john播放器/
如果我搜索文本'衬衫'，应显示所有产品（Peterengland / Levis / john player /） 。如果我搜索perterengland，只显示该产品。如果我使用主要类别搜索所有子类别，则应显示此子类别下的所有产品。
如果有人对此有所了解，请帮助我。
Recently i am into E-commerce application, here i want to search the products according to category name or sub category name or product name. here i am planning to create table for Category, subcategory and products. If i search for an item, that can be category name or sub category name or product name, accordingly the products should be displayed.
Eg. Men-Shirt-Peterengland/Levis/john player/
If i search the text 'shirt' all the products(Peterengland/Levis/john player/) should be displayed. If i search perterengland ,only that product to be displayed. If i search with the main category all the subcategories and all products under this sub categories should be displayed.
Help me if any one have an idea on it.
推荐答案
SET @search = '%' + ISNULL(@search, '') + CASE WHEN ISNULL(@search, '') = '' THEN '' ELSE '%'
SELECT * FROM Categories c inner join Products p on p.category_id = c.id
WHERE category_name like @search OR p.product_name LIKE @search OR product_code like @search
e tc ...对于你想要的多个字段
如果有空类别，你可以将INNER JOIN更改为左连接，如果有没有产品，你可以将完全外连接更改为类别等......
etc...for as many fields as you like
You can change INNER JOIN to left join if there are empty categories, or full outer join if there are products without categories etc...
create table tbl_Product
(id bigint primary key identity,category nvarchar(max),subcategory nvarchar(max),product nvarchar(max))
insert into tbl_Product values('shirt','PeterEng','../image/1.jpg')
insert into tbl_Product values('shirt','Levis','../image/2.jpg')
insert into tbl_Product values('T-shirt','PeterEng','../image/3.jpg')
insert into tbl_Product values('shirt','Levis','../image/4.jpg')
//Pass the Values to Stored Procedure from C#
create procedure sp_product
@category nvarchar(max)=null,
@subcategory nvarchar(max)=null,
as
begin
declare @sql nvarchar(max);
if @category!='' && @subcategory=''
set @sql=@sql+'select product,subcategory from tbl_product'
if @category!='' && @subcategory=!''
set @sql=@sql+'select product from tbl_product'
set @sql
end
create table tbl_Product
(id bigint primary key identity,category nvarchar(max),subcategory nvarchar(max),product nvarchar(max))
insert into tbl_Product values('shirt','PeterEng','../image/1.jpg')
insert into tbl_Product values('shirt','Levis','../image/2.jpg')
insert into tbl_Product values('T-shirt','PeterEng','../image/3.jpg')
insert into tbl_Product values('shirt','Levis','../image/4.jpg')
只需使用OR条件，像运算符，通配符
你可以得到它
Just Use OR Condition ,Like Operator ,wildcards
You can Get it
select *from tbl_Product where category like 'Levis%' or subcategory like 'Levis%'
不要Expec这里有确切的答案。得到一个想法。试试你自己：）
Do not Expect Exact Answer here. Just get an Idea.Try Your self :)
这篇关于如何在多个表中搜索项目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！