如何为下面给出的查询编写等效的Sqlite语句？ [重复]

This question already has an answer here:

这个问题在这里已有答案:

How to write the equivalent SQL case statement for query given below? 2 answers

如何为下面给出的查询编写等效的SQL case语句? 2个答案

This is my working non-sql query:

这是我工作的非SQL查询:

Query = "select Cust_Id,Card_Number,Clients_Title,Address_Current,Phone_Number,Mobile_Number from Customer_New Where 1=1";
try
{
    if (txt_title.Text != "")
        Query += " and Clients_Title Like '%" + txt_title.Text + "%'";
    if (txt_address.Text != "")
        Query += " and Address_Current Like '%" + txt_address.Text + "%'";
    if (txt_phone.Text != "")
        Query += " and Phone_Number Like '%" + txt_phone.Text + "%'";
    if (txt_mobile.Text != "")
        Query += " and Mobile_Number Like '%" + txt_mobile.Text + "%'";
    if (cbo_location.Text != "")
        Query += " and AreaLocation Like '%" + cbo_location.Text + "%'";
}

catch { }

In this code the working of "where" clause is:

在这段代码中,“where”子句的工作是:

If all text boxes are null it selects all the records mean skip where clause

如果所有文本框都为空,则选择所有记录表示跳过where子句
if some text is entered in any 1 text box then section in 'where'
clause is made on that particular text box

如果在任何1个文本框中输入了一些文本,则在该特定文本框中的“where”子句中进行部分
if some text is entered in any multiple text box then section in 'where' clause is made according to them by fulfilling "AND" condition between them .It mean all values entered into text boxes must match with corresponding rows attributes

如果在任何多个文本框中输入了一些文本,那么'where'子句中的部分是根据它们通过满足它们之间的“AND”条件来制作的。这意味着输入到文本框中的所有值必须与相应的行属性匹配

Here I am attempting to write its equivalent SQL statement .

在这里,我试图编写其等效的SQL语句。

SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
WHERE (@Clients_Title IS NULL   OR   Clients_Title    = @Clients_Title )
AND (@Address_Current IS NULL   OR   Address_Current  = @Address_Current )
AND (@Phone_Number    IS NULL   OR   Phone_Number     = @Phone_Number)
AND (@Mobile_Number   IS NULL   OR   Mobile_Number    = @Mobile_Number )
AND (@AreaLocation    IS NULL   OR   AreaLocation     = @AreaLocation)

Problem in this query is :

此查询中的问题是:

It search NULL or ''(empty string) from db tablt if nothing is provided in text box.I want as in above code if nothing entered in text box that attribute get skip from where clause and only values provided in text box are considered for checking in where clause.Can sql case and if else condition help in this scenario? Can anyone tell me how I can accomplish this ? Thanks

如果文本框中没有提供任何内容,它会从db tablt中搜索NULL或''(空字符串)。如上所述,如果在文本框中没有输入任何属性从where子句跳过并且只考虑文本框中提供的值,则我想要如上所述检查where子句。可以在这种情况下使用sql case和if else条件帮助吗?谁能告诉我如何才能做到这一点?谢谢

Note : In sqlite stored procedure don't exit .So guide me to write the correct sql query according to scenario.

注意:在sqlite存储过程中不要退出。所以指导我根据场景编写正确的sql查询。

3 个解决方案

#1

Use the conditions

使用条件

(@Clients_Title = ''
 OR ((@Clients_Title != '') AND (Clients_Title = @Clients_Title)

and so on.

等等。

Here's the full version

这是完整版

SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current,
       Phone_Number, Mobile_Number, AreaLocation FROM Customer_New 
WHERE (@Clients_Title = '' 
       OR (@Clients_Title != '' AND Clients_Title LIKE '%'+@Clients_Title+'%'))
  AND (@Address_Current = '' 
       OR (@Address_Current != '' AND Address_Current LIKE '%'+@Address_Current+'%'))
  AND (@Phone_Number = '' 
       OR (@Phone_Number != '' AND Phone_Number LIKE '%'+@Phone_Number+'%'))
  AND (@Mobile_Number = '' 
       OR (@Mobile_Number != '' AND Mobile_Number LIKE '%'+@Mobile_Number+'%'))
  AND (@AreaLocation = '' 
       OR (@AreaLocation != '' AND AreaLocation LIKE '%'+@AreaLocation+'%'))

See comment below for supplying '' instead of NULL arguments. Otherwise the conditions get way too ugly, e.g.:

请参阅下面的注释,提供''而不是NULL参数。否则条件太丑了,例如:

  AND (@AreaLocation = '' OR @AreaLocation IS NULL
       OR ((@AreaLocation != '' AND @AreaLocation IS NOT NULL)
            AND AreaLocation LIKE '%'+@AreaLocation+'%'))

#2

You probably need to test if the parameter is an empty string so you can do

您可能需要测试参数是否为空字符串,以便您可以执行此操作

AND (@param IS NULL OR @param = '' OR @param = param)

#3

At-last i made the query that fulfill my requirements

最后我做了满足我要求的查询

SELECT        Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM            Customer_New
    where        (CASE
        WHEN @Clients_Title != ''   THEN Clients_Title=@Clients_Title
      ELSE
         NULL IS NULL
      END)
      AND(CASE
        WHEN @Address_Current != '' THEN Address_Current =@Address_Current
      ELSE
         NULL IS NULL
      END)
              AND(CASE
        WHEN @Phone_Number != '' THEN Phone_Number=@Phone_Number
      ELSE
         NULL IS NULL
      END)
      AND(CASE
        WHEN @Mobile_Number != '' THEN Mobile_Number=@Mobile_Number
      ELSE
         NULL IS NULL
      END)
      AND(CASE
        WHEN @AreaLocation != '' THEN AreaLocation =@AreaLocation
      ELSE
         NULL IS NULL
      END)

#1