请问各位大侠,我有一个多条件查问题:
select* from a where 条件1 条件2 条件3 条件4 条件5.....
我要求,当其中一个或几个条件为空时,该条件不起任何作用,能显示a表满足其他(不为空)条件的
所有内容,请各位高手指教,谢谢!!!!
select* from a where 条件1 条件2 条件3 条件4 条件5.....
我要求,当其中一个或几个条件为空时,该条件不起任何作用,能显示a表满足其他(不为空)条件的
所有内容,请各位高手指教,谢谢!!!!
select* from a where 条件1 and 条件3 and 条件4 and 条件5
or
select* from a where 条件1 and 2>1 and 条件3 and 条件4 and 条件5
-- 参看这两个例子(inside sql server 中的),筛选 northwind.dbo.orders 表
-- 静态的
ALTER PROC dbo.usp_GetOrders
@OrderID AS INT = NULL,
@CustomerID AS NCHAR(5) = NULL,
@EmployeeID AS INT = NULL,
@OrderDate AS DATETIME = NULL
WITH RECOMPILE
AS
SELECT OrderID, CustomerID, EmployeeID, OrderDate, filler
FROM dbo.Orders
WHERE OrderID = COALESCE(@OrderID, OrderID)
AND CustomerID = COALESCE(@CustomerID, CustomerID)
AND EmployeeID = COALESCE(@EmployeeID, EmployeeID)
AND OrderDate = COALESCE(@OrderDate, OrderDate);
GO-- 动态的
ALTER PROC dbo.usp_GetOrders
@OrderID AS INT = NULL,
@CustomerID AS NCHAR(5) = NULL,
@EmployeeID AS INT = NULL,
@OrderDate AS DATETIME = NULL
ASDECLARE @sql AS NVARCHAR(4000);SET @sql =
N'SELECT OrderID, CustomerID, EmployeeID, OrderDate, filler'
+ N' FROM dbo.Orders'
+ N' WHERE 1 = 1'
+ CASE WHEN @OrderID IS NOT NULL THEN
N' AND OrderID = @oid' ELSE N'' END
+ CASE WHEN @CustomerID IS NOT NULL THEN
N' AND CustomerID = @cid' ELSE N'' END
+ CASE WHEN @EmployeeID IS NOT NULL THEN
N' AND EmployeeID = @eid' ELSE N'' END
+ CASE WHEN @OrderDate IS NOT NULL THEN
N' AND OrderDate = @dt' ELSE N'' END;EXEC sp_executesql
@sql,
N'@oid AS INT, @cid AS NCHAR(5), @eid AS INT, @dt AS DATETIME',
@oid = @OrderID,
@cid = @CustomerID,
@eid = @EmployeeID,
@dt = @OrderDate;
GO
个人建议 用动态来写