如何为下面给出的查询编写等效的SQL case语句？

小编典典

如何为下面给出的查询编写等效的SQL case语句？

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 { }

在这里，我尝试编写等效的SQL case语句。

SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
    WHERE  1 = CASE WHEN @Clients_Title != " " THEN  Clients_Title  AND
                              WHEN  @Address_Current != " " THEN  Address_Current  AND
                             WHEN  @Phone_Number != " " THEN  Phone_Number AND
                             WHEN  @Mobile_Number != " " THEN  Mobile_Number AND
                             WHEN  @AreaLocation != " " THEN  AreaLocation 
END

任何人都可以纠正我的案情陈述吗？

阅读 183

2021-05-05

共1个答案

小编典典

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)

2021-05-05