文件名: 多门店汉硕数据对接.txt

创建日期: 2026-07-30 17:31:08

if exists(select 1 from sysobjects where name = 'v_item_specprice')
   drop view v_item_specprice
go

CREATE VIEW v_item_specprice
as
select 
	d.branch_no,--门店号
	d.branch_name,--门店名称
	a.item_no,--商品编码
	a.item_subno,--商品条码
	a.item_name,--商品名称
	unit_no = isnull(ut.unit_name, a.unit_no), --如果ut.unit_name为空，就取a.unit_no的值，否则就取ut.unit_name的值
	a.product_area,--产地
	a.item_size,--规格
	a.class_no,--商品类别
	sale_price = isnull(e.sale_price, a.sale_price), --如果e.sale_price为空，就取a.sale_price的值，否则就取e.sale_price的值
	a.vip_price,--会员价
	spec_sale_price = b.spe_price,--促销价
	sale_start_date = b.start_date,--促销开始日期
	sale_end_date = b.end_date,--促销结束日期
	spec_vip_price = c.spe_price,--会员特价
	vip_start_date = c.start_date,--会员特价开始日期
	vip_end_date = c.end_date--会员特价结束日期
from item a
	inner join ba_stock_info d on 1 = 1
	left join ba_unit_info ut on ut.unit_no = a.unit_no
	left join ba_branch_sale_price e on  a.item_no = e.item_no and d.branch_no = e.branch_no --不同门店售价
	left join pm_spec_price b on a.item_no = b.item_no and d.branch_no = b.branch_no and b.special_type = '4' and CONVERT(varchar(10),b.start_date,120) <= CONVERT(varchar(10),GETDATE(),120) and CONVERT(varchar(10),GETDATE(),120) <= CONVERT(varchar(10),b.end_date,120) --促销标志为4的促销价
	left join pm_spec_price c on a.item_no = c.item_no and d.branch_no = c.branch_no and c.special_type = '7' and CONVERT(varchar(10),c.start_date,120) <= CONVERT(varchar(10),GETDATE(),120) and CONVERT(varchar(10),GETDATE(),120) <= CONVERT(varchar(10),c.end_date,120) --促销标志为7的会员特价