--供应商销货清单拉单 select --article.segregate_id '类别编码', --segregate_name '类别名称', barcode '商品条码', article_name '商品名称', sum(q) '入库数量', sum(TAX_A) '含税成本' from entry_stock_d, entry_stock_m ,article,segregate where entry_stock_d.entry_stock_bi=entry_stock_m.entry_stock_bi and article.aid=entry_stock_d.aid and article.segregate_id=segregate.segregate_id and sys_dt>='20040801' and entry_stock_m.SUPPLIER_ID='002211' and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115') group by article.segregate_id ,segregate_name,barcode,article_name order by barcode --查询销售数量 select --article.segregate_id '类别编码', --segregate_name '类别名称', barcode1.barcode '商品条码', article_name '商品名称', sum(sale_q) '实销数', sum(tax_sale_a) '交易金额' from article,supplier_article_sale_da ,barcode1,segregate where article.aid =supplier_article_sale_da.aid and article.aid=barcode1.aid and article.segregate_id=segregate.segregate_id and supplier_article_sale_da.SUPPLIER_ID='002211' and sys_dt>='20040801' and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115') group by article.segregate_id ,segregate_name ,barcode1.barcode,article_name order by barcode1.barcode--查询退货数量 select --article.segregate_id '类别编码', --segregate_name '类别名称', return_sale_d.barcode '条形码', ARTICLE_NAME '商品名称', sum(RETURN_SALE_Q) '退货数量', sum(tax_a) '退货金额' from ARTICLE ,barcode,RETURN_SALE_D,RETURN_SALE_M,segregate where ARTICLE.AID=barcode.AID and return_sale_d.aid=article.aid and return_sale_d.return_sale_bi=return_sale_m.return_sale_bi and article.segregate_id=segregate.segregate_id and SYS_DT>='20040801' and return_sale_m.SUPPLIER_ID='001739' and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115') GROUP BY article.segregate_id,segregate_name,ARTICLE_NAME,return_sale_d.barcode order by return_sale_d.barcode --查库存数量 select --article.segregate_id '类别编码', --segregate_name '类别名称', barcode1.barcode '条形码', ARTICLE_NAME '商品名称', stock_q '库存数' from stock,article,segregate,barcode1 where stock.aid=article.aid and barcode1.aid=article.aid and article.segregate_id=segregate.segregate_id and supplier_id='002211' and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115') order by barcode1.barcode可不可以把这四个语句变成一个语句,这四个语句所查出的记录条数是不同的。 有一些难度请各位大侠帮忙
select 商品条码=isnull(a.商品条码,isnull(b.商品条码,isnull(c.条形码,d.条形码))) ,商品名称=isnull(a.商品名称,isnull(b.商品名称,isnull(c.商品名称,d.商品名称))) ,入库数量=isnull(a.入库数量,0) ,含税成本=isnull(a.含税成本,0) ,实销数=isnull(b.实销数,0) ,交易金额=isnull(b.交易金额,0) ,退货数量=isnull(c.退货数量,0) ,退货金额=isnull(c.退货金额,0) ,库存数=isnull(d.库存数,0) from( --供应商销货清单拉单 select --article.segregate_id '类别编码', --segregate_name '类别名称', barcode '商品条码', article_name '商品名称', sum(q) '入库数量', sum(TAX_A) '含税成本' from entry_stock_d, entry_stock_m ,article,segregate where entry_stock_d.entry_stock_bi=entry_stock_m.entry_stock_bi and article.aid=entry_stock_d.aid and article.segregate_id=segregate.segregate_id and sys_dt>='20040801' and entry_stock_m.SUPPLIER_ID='002211' and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115') group by article.segregate_id ,segregate_name,barcode,article_name )a full join( --查询销售数量 select --article.segregate_id '类别编码', --segregate_name '类别名称', barcode1.barcode '商品条码', article_name '商品名称', sum(sale_q) '实销数', sum(tax_sale_a) '交易金额' from article,supplier_article_sale_da ,barcode1,segregate where article.aid =supplier_article_sale_da.aid and article.aid=barcode1.aid and article.segregate_id=segregate.segregate_id and supplier_article_sale_da.SUPPLIER_ID='002211' and sys_dt>='20040801' and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115') group by article.segregate_id ,segregate_name ,barcode1.barcode,article_name )b on a.商品条码=b.商品条码 and a.商品名称=b.商品名称 full join( --查询退货数量 select --article.segregate_id '类别编码', --segregate_name '类别名称', return_sale_d.barcode '条形码', ARTICLE_NAME '商品名称', sum(RETURN_SALE_Q) '退货数量', sum(tax_a) '退货金额' from ARTICLE ,barcode,RETURN_SALE_D,RETURN_SALE_M,segregate where ARTICLE.AID=barcode.AID and return_sale_d.aid=article.aid and return_sale_d.return_sale_bi=return_sale_m.return_sale_bi and article.segregate_id=segregate.segregate_id and SYS_DT>='20040801' and return_sale_m.SUPPLIER_ID='001739' and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115') GROUP BY article.segregate_id,segregate_name,ARTICLE_NAME,return_sale_d.barcode )c on a.商品条码=c.条形码 and a.商品名称=c.商品名称 full join( --查库存数量 select --article.segregate_id '类别编码', --segregate_name '类别名称', barcode1.barcode '条形码', ARTICLE_NAME '商品名称', stock_q '库存数' from stock,article,segregate,barcode1 where stock.aid=article.aid and barcode1.aid=article.aid and article.segregate_id=segregate.segregate_id and supplier_id='002211' and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115') )d on a.商品条码=d.条形码 and a.商品名称=d.商品名称 order by isnull(a.商品条码,isnull(b.商品条码,isnull(c.条形码,d.条形码)))
select
--article.segregate_id '类别编码',
--segregate_name '类别名称',
barcode '商品条码',
article_name '商品名称',
sum(q) '入库数量',
sum(TAX_A) '含税成本'
from entry_stock_d, entry_stock_m ,article,segregate
where entry_stock_d.entry_stock_bi=entry_stock_m.entry_stock_bi
and article.aid=entry_stock_d.aid
and article.segregate_id=segregate.segregate_id
and sys_dt>='20040801'
and entry_stock_m.SUPPLIER_ID='002211'
and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115')
group by article.segregate_id ,segregate_name,barcode,article_name
order by barcode
--查询销售数量
select
--article.segregate_id '类别编码',
--segregate_name '类别名称',
barcode1.barcode '商品条码',
article_name '商品名称',
sum(sale_q) '实销数',
sum(tax_sale_a) '交易金额'
from article,supplier_article_sale_da ,barcode1,segregate
where article.aid =supplier_article_sale_da.aid
and article.aid=barcode1.aid
and article.segregate_id=segregate.segregate_id
and supplier_article_sale_da.SUPPLIER_ID='002211'
and sys_dt>='20040801'
and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115')
group by article.segregate_id ,segregate_name ,barcode1.barcode,article_name
order by barcode1.barcode--查询退货数量
select
--article.segregate_id '类别编码',
--segregate_name '类别名称',
return_sale_d.barcode '条形码',
ARTICLE_NAME '商品名称',
sum(RETURN_SALE_Q) '退货数量',
sum(tax_a) '退货金额'
from ARTICLE ,barcode,RETURN_SALE_D,RETURN_SALE_M,segregate
where ARTICLE.AID=barcode.AID
and return_sale_d.aid=article.aid
and return_sale_d.return_sale_bi=return_sale_m.return_sale_bi
and article.segregate_id=segregate.segregate_id
and SYS_DT>='20040801'
and return_sale_m.SUPPLIER_ID='001739'
and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115')
GROUP BY article.segregate_id,segregate_name,ARTICLE_NAME,return_sale_d.barcode
order by return_sale_d.barcode
--查库存数量
select
--article.segregate_id '类别编码',
--segregate_name '类别名称',
barcode1.barcode '条形码',
ARTICLE_NAME '商品名称',
stock_q '库存数'
from stock,article,segregate,barcode1
where stock.aid=article.aid
and barcode1.aid=article.aid
and article.segregate_id=segregate.segregate_id
and supplier_id='002211'
and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115')
order by barcode1.barcode可不可以把这四个语句变成一个语句,这四个语句所查出的记录条数是不同的。
有一些难度请各位大侠帮忙
,商品名称=isnull(a.商品名称,isnull(b.商品名称,isnull(c.商品名称,d.商品名称)))
,入库数量=isnull(a.入库数量,0)
,含税成本=isnull(a.含税成本,0)
,实销数=isnull(b.实销数,0)
,交易金额=isnull(b.交易金额,0)
,退货数量=isnull(c.退货数量,0)
,退货金额=isnull(c.退货金额,0)
,库存数=isnull(d.库存数,0)
from(
--供应商销货清单拉单
select
--article.segregate_id '类别编码',
--segregate_name '类别名称',
barcode '商品条码',
article_name '商品名称',
sum(q) '入库数量',
sum(TAX_A) '含税成本'
from entry_stock_d, entry_stock_m ,article,segregate
where entry_stock_d.entry_stock_bi=entry_stock_m.entry_stock_bi
and article.aid=entry_stock_d.aid
and article.segregate_id=segregate.segregate_id
and sys_dt>='20040801'
and entry_stock_m.SUPPLIER_ID='002211'
and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115')
group by article.segregate_id ,segregate_name,barcode,article_name )a full join(
--查询销售数量
select
--article.segregate_id '类别编码',
--segregate_name '类别名称',
barcode1.barcode '商品条码',
article_name '商品名称',
sum(sale_q) '实销数',
sum(tax_sale_a) '交易金额'
from article,supplier_article_sale_da ,barcode1,segregate
where article.aid =supplier_article_sale_da.aid
and article.aid=barcode1.aid
and article.segregate_id=segregate.segregate_id
and supplier_article_sale_da.SUPPLIER_ID='002211'
and sys_dt>='20040801'
and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115')
group by article.segregate_id ,segregate_name ,barcode1.barcode,article_name
)b on a.商品条码=b.商品条码 and a.商品名称=b.商品名称
full join( --查询退货数量
select
--article.segregate_id '类别编码',
--segregate_name '类别名称',
return_sale_d.barcode '条形码',
ARTICLE_NAME '商品名称',
sum(RETURN_SALE_Q) '退货数量',
sum(tax_a) '退货金额'
from ARTICLE ,barcode,RETURN_SALE_D,RETURN_SALE_M,segregate
where ARTICLE.AID=barcode.AID
and return_sale_d.aid=article.aid
and return_sale_d.return_sale_bi=return_sale_m.return_sale_bi
and article.segregate_id=segregate.segregate_id
and SYS_DT>='20040801'
and return_sale_m.SUPPLIER_ID='001739'
and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115')
GROUP BY article.segregate_id,segregate_name,ARTICLE_NAME,return_sale_d.barcode
)c on a.商品条码=c.条形码 and a.商品名称=c.商品名称
full join( --查库存数量
select
--article.segregate_id '类别编码',
--segregate_name '类别名称',
barcode1.barcode '条形码',
ARTICLE_NAME '商品名称',
stock_q '库存数'
from stock,article,segregate,barcode1
where stock.aid=article.aid
and barcode1.aid=article.aid
and article.segregate_id=segregate.segregate_id
and supplier_id='002211'
and (article.segregate_id like '140106'or article.segregate_id like '140107'or article.segregate_id like '140115')
)d on a.商品条码=d.条形码 and a.商品名称=d.商品名称
order by isnull(a.商品条码,isnull(b.商品条码,isnull(c.条形码,d.条形码)))