34,576
社区成员
发帖
与我相关
我的任务
分享
SELECT * FROM table
where 1=1
and sd>= case when @sd is not null when @sd else sd end
and ed<= case when @ed is not null when @ed else ed end
and CosID= case when @CosID is not null when @CosID else CosID end
and CosName= case when @CosName is not null when @CosName else CosName end
and .....
SELECT * FROM table_name
where ((@sd is null and @ed is null) or (sd >= @sd and ed <= @ed)) --sd和ed都是空时,此条件总为true,否则相当于sd >= @sd and ed <= @ed
and (@con1 is null or con1 = @con1) --如果@con1不传值,则此行条件总为true;如果传值,则此行为and con1=@con1
and (@con2 is null or con2 = @con2) --同con1
and (@con3 is null or con3 = @con3) --同con1
and (@con4 is null or con4 = @con4) --同con1
and (@con5 is null or con5 = @con5) --同con1
create table #temp
(
卡号 varchar(100),
等级 nvarchar(100),
积分 int,
升级时间 datetime
)
insert #temp
select '000005', '9折', '3001', '2010-3-8' union all
select '000006', '9折', '3022', '2010-5-9' union all
select '000005', '8.5折', '6008', '2010-7-9' union all
select '000005', '8折', '10009', '2011-2-9' union all
select '000006', '8.5折', '6700', '2011-1-1'
--SQL:
select [9折升级时间] = n.[9折], [9折时积分]=m.[9折], [8.5折升级时间]=n.[8.5折], [8.5折时积分]=m.[8.5折],[8折升级时间]=n.[8折],[8折时积分]=m.[8折] from
(
select * from
(select 卡号, 等级, 积分 from #temp) a
pivot
(max(积分) for 等级 in ([9折], [8.5折], [8折])) b
) m
inner join
(
select * from
(select 卡号, 等级, 升级时间 from #temp) a
pivot
(max(升级时间) for 等级 in ([9折], [8.5折], [8折])) b
) n
on m.卡号 = n.卡号
/*
9折升级时间 9折时积分 8.5折升级时间 8.5折时积分 8折升级时间 8折时积分
2010-03-08 00:00:00.000 3001 2010-07-09 00:00:00.000 6008 2011-02-09 00:00:00.000 10009
2010-05-09 00:00:00.000 3022 2011-01-01 00:00:00.000 6700 NULL NULL
*/
--如果列不固定,用动态SQL自己拼,CSDN上的例子很多
http://topic.csdn.net/u/20110310/12/ed017dee-c0b9-42f6-b02d-afa222466e9f.html
CREATE PROCEDURE sp_tbWasteSource_Search
(
@sd datetime=null, //开始日期
@ed datetime=null, //结束日期
@con1 varchar(50),
@con2 varchar(30),
@con3 varchar(5),
@con4 varchar(10),
@con5 varchar(4)
)
as
begin
select * from tb
where (@sd is null or date>@sd) and (@ed is null or date<@ed)
end