如何为以下场景编写SQL查询？ [英] How can I write an sql query for the following scenario?

问题描述

我正在为报告查看器编写此查询：



SELECT Cust_Id，Clients_Title，Card_Number，Key_Person，Address_Current，Phone_Number，Mobile_Number，AreaLocation

FROM Customer_New

WHERE（Clients_Title = @Clients_Title）或

（Address_Current = @Address_Current）或

（Phone_Number = @Phone_Number）或

（Mobile_Number = @Mobile_Number）或

（AreaLocation = @AreaLocation）

我想要的是：



当没有提供给报表查看器的参数时，将显示所有客户记录。

如果提供了参数，则实现OR条件。

最后，如果在任何条件下都有对比，那么就不会显示任何记录。



谁能告诉我如何才能完成此？<二v class =h2_lin>解决方案

试试这个

 如果 < span class =code-sdkkeyword> @ Clients_Title ！=  NULL  或  @ Address_Current ！=  NULL  或  @ Phone_Number ！=  NULL  或  @ Mobile_Number ！+  NULL  或  @AreaLocation ！=  NULL  
 开始 
  SELECT  Cust_Id，Clients_Title，Card_Number，Key_Person，Address_Current，Phone_Number，Mobile_Number，AreaLocation 
  FROM  Customer_New 
  WHERE （Clients_Title =  @ Clients_Title ） OR  
（Address_Current =  @ Address_Current ） OR  
（Phone_Number =  @ Phone_Number ） OR  
（Mobile_Number =  @ Mobile_Number ） OR  
（AreaLocation =  @ AreaLocation ）
  else  
  SELECT  Cust_Id，Clients_Title，Card_Number，Key_Person，Address_Current，Phone_Number，Mobile_Number，AreaLocation 
  FROM  Customer_New 
 结束 

  SELECT  Cust_Id，Clients_Title，Card_Number，Key_Person， Address_Current，Phone_Number，Mobile_Number，AreaLocation 
  FROM  Customer_New 
  WHERE （Clients_Title = ISNULL （ @ Clients_Title ，Clients_Title）） OR  
（Address_Current = ISNULL（ @ Address_Current ，Address_Current）） OR  
（Phone_Number = ISNULL（ @ Phone_Number ，Phone_Number）） OR  
（Mobile_Number = ISNULL（ @ Mobile_Number ， Mobile_Number）） OR  
（AreaLocation = ISNULL（ @ AreaLocation ，AreaLocation））

这可以作为查询执行，但存储过程最好是为了获得更好的性能。

  SELECT  
 Cust_Id，Cl ients_Title，Card_Number，Key_Person，
 Address_Current，Phone_Number，Mobile_Number，AreaLocation 
  FROM  Customer_New 
  WHERE  
（ @ Clients_Title   IS   NULL  或 Clients_Title =  @ Clients_Title ） AND  
（ @ Address_Current   IS   NULL  或 Address_Current =  @ Address_Current ） AND  
（ @ Phone_Number   IS   NULL  或 Phone_Number =  @ Ph one_Number ） AND  
（ @ Mobile_Number   IS   NULL  或 Mobile_Number =  @ Mobile_Number ） AND  
（ @ AreaLocation   IS   NULL  或 AreaLocation =  @ AreaLocation ）

I am writing this query for report viewer :

SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
WHERE (Clients_Title = @Clients_Title) OR
(Address_Current = @Address_Current) OR
(Phone_Number = @Phone_Number) OR
(Mobile_Number = @Mobile_Number) OR
(AreaLocation = @AreaLocation)
What I want is:

When there are no parameter that is provided to the report viewer, all of the customer record will be displayed.
If there are parameters provided, the OR condition is implemented .
Lastly, if there is contrast in any where condition, then no record will be displayed.

Can anyone tell me how I can accomplish this?

解决方案

try this

if @Clients_Title != NULL OR @Address_Current != NULL OR @Phone_Number != NULL OR @Mobile_Number !+ NULL OR @AreaLocation != NULL 
Begin 
SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
WHERE (Clients_Title = @Clients_Title) OR
(Address_Current = @Address_Current) OR
(Phone_Number = @Phone_Number) OR
(Mobile_Number = @Mobile_Number) OR
(AreaLocation = @AreaLocation)
else
SELECT Cust_Id, Clients_Title, Card_Number, Key_Person, Address_Current, Phone_Number, Mobile_Number, AreaLocation
FROM Customer_New
End

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

This can be executed as a query, but stored procedure is best for better performance.

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)

这篇关于如何为以下场景编写SQL查询？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！