ALTER PROCEDURE [dbo].[ap_sale_form] @no varchar(20) AS
declare @tb table (tl tinyint,row int)
insert @tb select 1,sale_row from shop_sale where id_no=@no and type_='CAS'
insert @tb select 2,pay_row from shop_payment where id_no=@no and type_='CAS'select id_no=d.id_no,cdate=d.c_date,d.shop_no,d.sale_type,d.event_,d.vip_,d.amount_,a.tl,b.stock_code,b.mat_,b.color_,b.size_,b.quantity_,b.unit_price,b.total_
,c.pay_event,isnull(e.card_type,c.pay_kind)
,c.amount_,c.rate_,c.amount_hk,c.card_no,e.trace_number,d.c_user
from @tb a,shop_sale b,shop_payment c,shop_main d,card_pos e
where case a.tl when 1 then a.row else 0 end*=b.sale_row and case a.tl when 2 then a.row else 0 end*=c.pay_row and d.id_no=@no and case a.tl when 2 then a.row else 0 end*=e.pay_row and d.type_='CAS'
order by a.tl,a.row
declare @tb table (tl tinyint,row int)
insert @tb select 1,sale_row from shop_sale where id_no=@no and type_='CAS'
insert @tb select 2,pay_row from shop_payment where id_no=@no and type_='CAS'select id_no=d.id_no,cdate=d.c_date,d.shop_no,d.sale_type,d.event_,d.vip_,d.amount_,a.tl,b.stock_code,b.mat_,b.color_,b.size_,b.quantity_,b.unit_price,b.total_
,c.pay_event,isnull(e.card_type,c.pay_kind)
,c.amount_,c.rate_,c.amount_hk,c.card_no,e.trace_number,d.c_user
from @tb a,shop_sale b,shop_payment c,shop_main d,card_pos e
where case a.tl when 1 then a.row else 0 end*=b.sale_row and case a.tl when 2 then a.row else 0 end*=c.pay_row and d.id_no=@no and case a.tl when 2 then a.row else 0 end*=e.pay_row and d.type_='CAS'
order by a.tl,a.row
相当于left join
=*是2000里的right join2005以上后废除，建议LZ早点改掉这个习惯。