declare @0 varchar(20),@1 varchar(20) ,@daima varchar(20),@mingcheng varchar(20)select @0='2008-04-26',@1='2008-05-25',@daima='',@mingcheng=''select isnull(a.客户或供应商代码, b.客户或供应商代码) as'客户或供应商代码' ,isnull(a.客户或供应商名称,b.客户或供应商名称) as'客户或供应商名称' ,isnull((a.凭证总计-b.凭证总计),isnull(a.凭证总计,b.凭证总计)) as'凭证总计'from(SELECT T0.CardCode AS '客户或供应商代码', T0.CardName AS '客户或供应商名称', SUM(T0.DocTotal) AS '凭证总计'
FROM [dbo].[OPDN] T0
where (T0.DOCDATE>=@0 AND T0.DOCDATE<=@1 ) or (T0.CardCode LIKE @daima and T0.CardName LIKE @mingcheng)GROUP BY T0.CardCode,T0.CardName) a full join(SELECT T0.CardCode AS '客户或供应商代码', T0.CardName AS '客户或供应商名称', SUM(T0.DocTotal) AS '凭证总计' FROM [dbo].[ORPD] T0where (T0.DOCDATE>=@0 AND T0.DOCDATE<=@1) or (T0.CardCode LIKE @daima and T0.CardName LIKE @mingcheng)GROUP BY T0.CardCode,T0.CardName)bon a.客户或供应商代码=b.客户或供应商代码诸位帮我看看 我写的这个语句 怎么@daima @mingcheng 这两个变量不起作用 也就是说我在运行这个语句时 前三个变量依次填上 时间1 时间2 daima;显示出的结果跟只填写时间1 时间2 的结果是一样的 也就是说 第三、四个变量 没有起到应起的作用 问题应该出在判断条件这,但是我应该如何修改呢 急!!!
FROM [dbo].[OPDN] T0
where (T0.DOCDATE>=@0 AND T0.DOCDATE<=@1 ) or (T0.CardCode LIKE @daima and T0.CardName LIKE @mingcheng)GROUP BY T0.CardCode,T0.CardName) a full join(SELECT T0.CardCode AS '客户或供应商代码', T0.CardName AS '客户或供应商名称', SUM(T0.DocTotal) AS '凭证总计' FROM [dbo].[ORPD] T0where (T0.DOCDATE>=@0 AND T0.DOCDATE<=@1) or (T0.CardCode LIKE @daima and T0.CardName LIKE @mingcheng)GROUP BY T0.CardCode,T0.CardName)bon a.客户或供应商代码=b.客户或供应商代码诸位帮我看看 我写的这个语句 怎么@daima @mingcheng 这两个变量不起作用 也就是说我在运行这个语句时 前三个变量依次填上 时间1 时间2 daima;显示出的结果跟只填写时间1 时间2 的结果是一样的 也就是说 第三、四个变量 没有起到应起的作用 问题应该出在判断条件这,但是我应该如何修改呢 急!!!
通配符一定要用
insert into tb select 'fdsffd454fdfsdfs'
insert into tb select 'fdsf54fdfdsf454fdfsfdfs'
insert into tb select 'ff'
select * from tb where id like 'f'/*
id
------*/
--这样是没有数据出来的
select * from tb where id like 'f%'/*
id
--------
fdsf454fdfsdfs
fdsffd454fdfsdfs
fdsf54fdfdsf454fdfsfdfs
ff
*/
drop table tb
则将
T0.CardCode LIKE @daima and T0.CardName LIKE @mingcheng
修改为
T0.CardCode=@daima and T0.CardName=@mingcheng
declare @0 varchar(20),@1 varchar(20) ,@daima varchar(20),@mingcheng varchar(20)
select @0='2008-04-26',@1='2008-05-25',@daima='',@mingcheng=''
select
isnull(a.客户或供应商代码, b.客户或供应商代码) as'客户或供应商代码'
,isnull(a.客户或供应商名称,b.客户或供应商名称) as'客户或供应商名称'
,isnull((a.凭证总计-b.凭证总计),isnull(a.凭证总计,b.凭证总计)) as'凭证总计'
from
(SELECT T0.CardCode AS '客户或供应商代码', T0.CardName AS '客户或供应商名称', SUM(T0.DocTotal) AS '凭证总计'
FROM [dbo].[OPDN] T0
where (T0.DOCDATE>=@0 AND T0.DOCDATE <=@1 ) or (T0.CardCode =@daima and T0.CardName =@mingcheng) --修改这里
GROUP BY T0.CardCode,T0.CardName) a
full join
(SELECT T0.CardCode AS '客户或供应商代码', T0.CardName AS '客户或供应商名称', SUM(T0.DocTotal) AS '凭证总计'
FROM [dbo].[ORPD] T0
where (T0.DOCDATE>=@0 AND T0.DOCDATE <=@1) or (T0.CardCode =@daima and T0.CardName =@mingcheng) -- 修改这里
GROUP BY T0.CardCode,T0.CardName)b
on a.客户或供应商代码=b.客户或供应商代码
如果需要like条件,而且传入的参数中没有'%'
则加上此句:
select @daima=@daima+'%',@mingcheng=@mingcheng +'%' declare @0 varchar(20),@1 varchar(20) ,@daima varchar(20),@mingcheng varchar(20)
select @0='2008-04-26',@1='2008-05-25',@daima='',@mingcheng=''
select @daima=@daima+'%',@mingcheng=@mingcheng +'%' -- 新加的...
select
isnull(a.客户或供应商代码, b.客户或供应商代码) as'客户或供应商代码'
,isnull(a.客户或供应商名称,b.客户或供应商名称) as'客户或供应商名称'
,isnull((a.凭证总计-b.凭证总计),isnull(a.凭证总计,b.凭证总计)) as'凭证总计'
from
(SELECT T0.CardCode AS '客户或供应商代码', T0.CardName AS '客户或供应商名称', SUM(T0.DocTotal) AS '凭证总计'
FROM [dbo].[OPDN] T0
where (T0.DOCDATE>=@0 AND T0.DOCDATE <=@1 ) or (T0.CardCode LIKE @daima and T0.CardName LIKE @mingcheng)
GROUP BY T0.CardCode,T0.CardName) a
full join
(SELECT T0.CardCode AS '客户或供应商代码', T0.CardName AS '客户或供应商名称', SUM(T0.DocTotal) AS '凭证总计'
FROM [dbo].[ORPD] T0
where (T0.DOCDATE>=@0 AND T0.DOCDATE <=@1) or (T0.CardCode LIKE @daima and T0.CardName LIKE @mingcheng)
GROUP BY T0.CardCode,T0.CardName)b
on a.客户或供应商代码=b.客户或供应商代码 PS:select @0='2008-04-26',@1='2008-05-25',@daima='',@mingcheng=''
你这句@daima='',@mingcheng赋为空值,这样不同好吧,
和不写这两个条件也是一样的结果,
检查你的OR的条件和参数的赋值情况.declare @tb table (id int identity(1,1),cname nvarchar(10))
insert @tb
select 'A' union all
select 'B' union all
select 'C' union all
select 'AA' union all
select 'BB' union all
select 'CC' select * from @tb
/*
id cname
----------- ----------
1 A
2 B
3 C
4 AA
5 BB
6 CC(6 row(s) affected)
*/
select * from @tb where cname like '%'
/*
id cname
----------- ----------
1 A
2 B
3 C
4 AA
5 BB
6 CC*/
select * from @tb where cname like 'A%'
/*
id cname
----------- ----------
1 A
4 AA
*/
你已经把参数值定死了,调用的时候输入其他任何值,在里面都是都会是你select的值,所以输不输daima值都一样了。
应该去掉select @0='2008-04-26',@1='2008-05-25',@daima='',@mingcheng=''
这一行
我以为这是过程,你在赋值的字符串后面加%再试下:
select @0='2008-04-26',@1='2008-05-25',@daima='**%',@mingcheng='**%'