如何使用SQL从数据库中搜索多个条件? [英] How to search with multiple criteria from a database with SQL?

问题描述

我是新手程序员.我有一个页面如下所示.我有一个汽车数据库，因此必须使用此页面从数据库中进行搜索.用户可以输入和选择值的条件很多.但是，用户不必填写或选择所有条件.例如，用户可以搜索仅依赖于"Mark"的汽车，或者可以依赖于Year ="2007" Fuel ="diesel" Gear ="Automatic" ...等.我的问题是如何设计一个可以检测哪些控件有价值.我使用C#和MSSQL Server.

I am a newbie programmer. I have a page looks like below. I have a car database and i have to make a search from database with this page. There are many criterias that Users can enter and select values. But User doesn't have to fill or select all criterias. For example User can search a car that only depends on "Mark" or can search that depends on Year="2007" Fuel="diesel" Gear="Automatic"... etc. My problem is how to design a query that can detect which controls have value or not. I use C# and MSSQL Server.

替代文本http://img8.imageshack.us/img8/5781/searchad.jpg

推荐答案

您应该足够灵活地构造查询(处理未填充的参数)，以在所有搜索条件下工作.考虑相同的例子.如果用户仅输入标记字段，而保留为空白

You should construct your query flexible enough (handling unfilled parameters) to work an all search conditions. Consider the same example. if the user entered only the mark field, and remaining left blank

示例代码，假设您具有这些参数

sample code, assume you have these parameters

@mark varchar(20)
@series varchar(20)
@model varchar(20)

select * from tbl 
where (@mark is null or markColumn=@mark) and
(@series is null or series Column=@series ) and
(@model is null or model Column=@model )

我建议您将所有参数分别传递给SP.这样可以避免动态构造查询，并且可以避免SQL注入.

I would recomment you to pass all parameters indivitually to SP. this avoids the dynamic construction of query and will avoid SQL injections.

这篇关于如何使用SQL从数据库中搜索多个条件?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！