SQL语句问题,在线...

bondhu 2003-09-29 01:50:05
可参考http://expert.csdn.net/Expert/topic/2313/2313609.xml?temp=.7527124
不好意思,我将问题简化过度了,实际情况是这样的
CREATE PROCEDURE u_import_tritem
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or material

AS
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no

where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and a.move_type = ( case @product when 'B' then 'decin' or'bomrtn' when 'A' then 'decout'or mkout' or 'prdmsy'or 'prdout' end ) --就是这里错误

select * into #tt2 from dbo.trade e
where e.typeflag = @product

select a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code

当然还是那句case语句的问题。因为case在where 中所以楼上的部分方法用不上。因为a.move_type的值不子一个字符,所以大力的方法也用不上。aierong兄的应该可行,可不大方便,请兄台们想想如何很好解决。

...全文
25 9 打赏 收藏 转发到动态 举报
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
lshuy2001 2003-09-29
  • 打赏
  • 举报
回复
case @product
when 'B' then 'decin' or'bomrtn'
when 'A' then 'decout'or mkout' or 'prdmsy'or 'prdout'
end )

好像不能给一个变量两个直吧!
另外mkout' 应该为'mkout' 吧!

不知道能不能给你点帮助
bondhu 2003-09-29
  • 打赏
  • 举报
回复
经测试,大力的漏掉了move_type = 'decin'的情况,只检索出2456条数据,其他的均检索出14981条数据,是正确的。
大力兄,是不是再检查一下你的方法?
bondhu 2003-09-29
  • 打赏
  • 举报
回复
谢谢各位高人,正在看。
aierong 2003-09-29
  • 打赏
  • 举报
回复
CREATE PROCEDURE u_import_tritem
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or material

AS

if @product='A'
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no
where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and a.move_type in ('decout','mkout','prdmsy','prdout')

if @product='b'
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no
where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and a.move_type in ('decin' ,'bomrtn')

select * into #tt2 from dbo.trade e
where e.typeflag = @product

select a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code




分开写,简单明了
pengdali 2003-09-29
  • 打赏
  • 举报
回复
CREATE PROCEDURE u_import_tritem
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or material

AS
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no

where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and case @product when 'B' then ',decin,bomrtn,' when 'A' then ',decout,mkout,prdmsy,prdout,' end like '%,'+a.move_type+',%'

select * into #tt2 from dbo.trade e
where e.typeflag = @product

select a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code
txlicenhe 2003-09-29
  • 打赏
  • 举报
回复

a.move_type = ( case @product when 'B' then 'decin' or'bomrtn' when 'A' then 'decout'or 'mkout' or 'prdmsy' or 'prdout' end )
改为
(@product = 'B' and (a.move_type in ('decin','bomrtn')) or @product = 'A' and (a.move_type in ('decout','mkout','prdmsy','prdout')) )


CREATE PROCEDURE u_import_tritem
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or material

AS
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no

where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and (@product = 'B' and (a.move_type in ('decin','bomrtn')) or @product = 'A' and (a.move_type in ('decout','mkout','prdmsy','prdout')) )

select * into #tt2 from dbo.trade e
where e.typeflag = @product

select a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code
LoveSQL 2003-09-29
  • 打赏
  • 举报
回复
a.move_type = ( case @product when 'B' then 'decin' or'bomrtn' when 'A' then 'decout'or mkout' or 'prdmsy'or 'prdout' end ) --就是这里错误
改为:
=============================================================
a.move_type in (
select case @product when 'B' then 'decin' end union
select case @product when 'B' then 'bomrtn' end union
select case @product when 'A' then 'decout' end union
select case @product when 'A' then 'mkout' end union
select case @product when 'A' then 'prdmsy' end union
select case @product when 'A' then 'prdout' end )
pengdali 2003-09-29
  • 打赏
  • 举报
回复
CREATE PROCEDURE u_import_tritem
@dates datetime,
@datee datetime,
@movetype char(1), --in or out
@product char(1) --product or material

AS
select a.ref_no,a.move_date,a.tran_type,a.flow_type,a.move_type,a.issued_to,b.item_no,b.qty,
c.item_no,c.article_code,c.description
into #tt1
from icmovh a join icmovd b
on a.ref_no = b.ref_no join gbitem c on b.item_no = c.item_no

where a.move_date >= @dates and a.move_date <= @datee
and a.flow_type = ( case @product when 'A' then 'i' when 'B' then 'r'end )
and c.article_code is not null
and c.article_code <> ''
and a.posted = 'Y'
and case @product when 'B' then ',decin,bomrtn,' when 'A' then ',decout,mkout,prdmsy,prdout,' end like '%,'+a.move_type+',%'

select * into #tt2 from dbo.trade e
where e.typeflag = @product

select a.ref_no,b.haiguanid,b.itemname,a.qty,c.oriprice,a.movedate,@movetype,@product
from #tt1 a join #tt2 b on a.article_code = b.article_code
愉快的登山者 2003-09-29
  • 打赏
  • 举报
回复
a.move_type = ( case @product when 'B' then 'decin' or'bomrtn' when 'A' then 'decout'or mkout' or 'prdmsy'or 'prdout' end ) --就是这里错误
改为:
(@product = 'B' and a.move_type in ('decin','bomrtn')) or
(@product = 'A' and a.move_type in ('decout','mkout','prdmsy','prdout'))

22,206

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧