所有的分都给了，麻烦看一下如何实现四张表联查并实现统计的功能

[javajspjing]

ALTER proc [dbo].[xf_rpt_turnover_category_list]
@begin varchar(10),
@end varchar(10)
as
select tb_Goods_Data.CGoodCatelogNo as 分类编号 ,tb_Goods_Data.CGoodCatelogName as 分类名称 ,
sum(tb_Bill_Ex.DTotle) as 成交金额 ,count(tb_Bill_Ex.CBillNo) 成交比数
from tb_Bill , tb_Bill_Ex,tb_Bill_Trace , tb_Goods_Data
where tb_Bill.CBillNo = tb_Bill_Ex.CBillNo and
tb_Bill.CBillNo = tb_Bill_Trace.CBillNo and
tb_Bill_Ex.CGoodsNo = tb_Goods_Data.CGoodsNo and
-- tb_Bill.CBillStatus ='4' and
tb_Bill.TBillCreate between @begin and @end

group by tb_Goods_Data.CGoodCatelogNo,tb_Goods_Data.CGoodCatelogName

我现在想要实现统计一个分类下的订单总总额和成交比数。但查询出来的值为什么不一直不对啊
那位可以帮帮我啊，小弟所有的分值在给奉献给大家了！

[bancxc]

有可能是连接tb_Bill_Trace连多了

[--小F--]

最好给点数据看看

[javajspjing]

htl258 这位大哥。还是不行啊
好像别人起的不对呀

[htl258_Tony]

[Quote=引用 17 楼 javajspjing 的回复:]
htl258 这位大哥。还是不行啊
好像别人起的不对呀
[/Quote]
那只有提供点数据来分析了.

[bancxc]

最好给点数据看看 说说你想要的结果 不然很难搞地啊

ALTER proc [dbo].[xf_rpt_turnover_category_list]
@begin varchar(10),
@end varchar(10)
as 
select d.CGoodCatelogNo as 分类编号 ,d.CGoodCatelogName as 分类名称 ,成交金额 ,成交比数
from tb_Bill a 
    join ( 
        select CBillNo,sum(DTotle) as 成交金额,count(CBillNo) 成交比数 
        from  tb_Bill_Ex
        group by CBillNo
        ) b on a.CBillNo = b.CBillNo 
    join tb_Bill_Trace c on a.CBillNo = c.CBillNo
    join tb_Goods_Data d on b.CGoodsNo = d.CGoodsNo
where convert(datetime,a.TBillCreate) between @begin and @end 
group by d.CGoodCatelogNo,d.CGoodCatelogName 

[东那个升]

ALTER proc [dbo].[xf_rpt_turnover_category_list]
@begin varchar(10),
@end varchar(10)
as 
select	d.CGoodCatelogNo as 分类编号 
		,d.CGoodCatelogName as 分类名称 
		,sum(b.成交金额) as 成交金额 
		,sum(b.成交比数) 成交比数
from tb_Bill a 
	,(select CGoodsNo,CBillNo,sum(DTotle) as 成交金额,count(CBillNo) 成交比数 
        from  tb_Bill_Ex
        group by CGoodsNo,CBillNo) b
	,tb_Bill_Trace  c 
	,tb_Goods_Data d
where	 a.CBillNo = b.CBillNo 
		and a.CBillNo = c.CBillNo 
		and b.CGoodsNo = d.CGoodsNo 
		-- a.CBillStatus ='4'
		and	a.TBillCreate between @begin and @end 

group by d.CGoodCatelogNo,d.CGoodCatelogName 

这样如何

[javajspjing]

这样吧

我还是把具体的表的字段一个个的写出来吧。。等一下啊

[bancxc]

不用一个一个写 关键的写出来就ok了

[javajspjing]

表12: tb_Bill 订单表

(1) CBillNo 订单编号 varchar 20 PK
(2) DTotle 总金额 float 8 Y
(3) CBillStatus 订单状态 (1,2,3,4,5,6,7) 4为成功

tb_Bill_Ex 订单扩展表
(2) CBillNo 订单编号 varchar 20 Y
(4) CGoodsNo 商品编号 varchar 20 Y

tb_Goods_Categories 商品分类表
(1) CCategoryNO 商品分类编号 nvarchar 510 PK
(6) CCategory 商品分类名称 nvarchar 510 Y

tb_Goods_Data 商品的总表
(1) CGoodsNO 商品编号 varchar 20 PK
(4) CGoodCatelogNo 商品分类编号与上面标红的是值是相同nvarchar 100 Y

[bancxc]

select tb_Goods_Categories.CCategoryNO [分类编号],tb_Goods_Categories.CCategory [分类名称],
sum(tb_Bill_Ex.DTotle) as [成交金额] ,count(tb_Bill_Ex.CBillNo) [成交比数]
from tb_Bill
join  tb_Bill_Ex         on   tb_Bill.CBillNo = tb_Bill_Ex.CBillNo 
join tb_Goods_Data       on tb_Bill_Ex.CGoodsNo = tb_Goods_Data.CGoodsNo
join tb_Goods_Categories on tb_Goods_Data.CGoodCatelogNo=tb_Goods_Data.CGoodCatelogNo
Group By tb_Goods_Categories.CCategoryNO,tb_Goods_Categories.CCategory

试一下

[bancxc]

select tb_Goods_Categories.CCategoryNO [分类编号],tb_Goods_Categories.CCategory [分类名称],
sum(tb_Bill_Ex.DTotle) as [成交金额] ,count(tb_Bill_Ex.CBillNo) [成交比数]
from tb_Bill
join tb_Bill_Ex          on    tb_Bill.CBillStatus ='4' and tb_Bill.TBillCreate between @begin and @end 
							   and tb_Bill.CBillNo = tb_Bill_Ex.CBillNo 
join tb_Goods_Data       on    tb_Bill_Ex.CGoodsNo = tb_Goods_Data.CGoodsNo
join tb_Goods_Categories on    tb_Goods_Data.CGoodCatelogNo=tb_Goods_Data.CGoodCatelogNo
Group By tb_Goods_Categories.CCategoryNO,tb_Goods_Categories.CCategory

[bancxc]

ALTER proc [dbo].[xf_rpt_turnover_category_list]
@begin varchar(10),
@end varchar(10)
as 
begin
select tb_Goods_Categories.CCategoryNO [分类编号],tb_Goods_Categories.CCategory [分类名称],
sum(tb_Bill_Ex.DTotle) as [成交金额] ,count(tb_Bill_Ex.CBillNo) [成交比数]
from tb_Bill
join tb_Bill_Ex          on    tb_Bill.CBillStatus ='4' and tb_Bill.TBillCreate between @begin and @end 
							   and tb_Bill.CBillNo = tb_Bill_Ex.CBillNo 
join tb_Goods_Data       on    tb_Bill_Ex.CGoodsNo = tb_Goods_Data.CGoodsNo
join tb_Goods_Categories on    tb_Goods_Data.CGoodCatelogNo=tb_Goods_Data.CGoodCatelogNo
Group By tb_Goods_Categories.CCategoryNO,tb_Goods_Categories.CCategory
end
go

----对吗 哥们

[东那个升]

select A.CCategoryNO,A.CCategory,SUM(成交金额) AS 成交金额,SUM(成交比数) AS 成交比数
from  tb_Goods_Categories a
	,tb_Goods_Data b
	(select b.CGoodsNo,sum(a.DTotle) as 成交金额,count(a.CBillNo) 成交比数 
    from   tb_Bill a,tb_Bill_Ex b
	where a.CBillStatus=4 and a.CBillNo=b.CBillNo
    group by CGoodsNo) C
WHERE B.CGoodsNO=C.CGoodsNO AND B.CGoodCatelogNo=A.CCategoryNO
GROUP BY A.CCategoryNO,A.CCategory

[东那个升]

select A.CCategoryNO,A.CCategory,SUM(成交金额) AS 成交金额,SUM(成交比数) AS 成交比数
from  tb_Goods_Categories a
	,tb_Goods_Data b
	(select b.CGoodsNo,sum(a.DTotle) as 成交金额,count(a.CBillNo) 成交比数 
    from   tb_Bill a,tb_Bill_Ex b
	where a.CBillStatus=4 and a.CBillNo=b.CBillNo AND 	a.TBillCreate between @begin and @end 
    group by CGoodsNo) C
WHERE B.CGoodsNO=C.CGoodsNO AND B.CGoodCatelogNo=A.CCategoryNO
GROUP BY A.CCategoryNO,A.CCategory

丢了日期区间

[--小F--]

select
 tb_Goods_Categories.CCategoryNO [分类编号],
 tb_Goods_Categories.CCategory [分类名称],
 sum(tb_Bill_Ex.DTotle) as [成交金额],
 count(tb_Bill_Ex.CBillNo) [成交比数]
from
 tb_Bill
join
 tb_Bill_Ex          
on  
 tb_Bill.CBillStatus ='4' 
and
 tb_Bill.TBillCreate 
between @begin and @end and 
 tb_Bill.CBillNo = tb_Bill_Ex.CBillNo 
join
 tb_Goods_Data 
on 
  tb_Bill_Ex.CGoodsNo = tb_Goods_Data.CGoodsNo
join tb_Goods_Categories on                                                                                     tb_Goods_Data.CGoodCatelogNo=tb_Goods_Data.CGoodCatelogNo
Group By
  tb_Goods_Categories.CCategoryNO,tb_Goods_Categories.CCategory

[东那个升]

select A.CCategoryNO,A.CCategory,SUM(成交金额) AS 成交金额,SUM(成交比数) AS 成交比数
from tb_Goods_Categories a
,tb_Goods_Data b
,(select b.CGoodsNo,sum(a.DTotle) as 成交金额,count(a.CBillNo) 成交比数
from tb_Bill a,tb_Bill_Ex b
where a.CBillStatus=4 and a.CBillNo=b.CBillNo AND a.TBillCreate between @begin and @end
group by CGoodsNo) C
WHERE B.CGoodsNO=C.CGoodsNO AND B.CGoodCatelogNo=A.CCategoryNO
GROUP BY A.CCategoryNO,A.CCategory


上面的丢了‘,’

[--小F--]

select
 tb_Goods_Categories.CCategoryNO [分类编号],
 tb_Goods_Categories.CCategory [分类名称],
 sum(tb_Bill_Ex.DTotle) as [成交金额],
 count(tb_Bill_Ex.CBillNo) [成交比数]
from
 tb_Bill
join
 tb_Bill_Ex          
on  
 tb_Bill.CBillStatus ='4' 
and
 tb_Bill.TBillCreate 
between @begin and @end and 
 tb_Bill.CBillNo = tb_Bill_Ex.CBillNo 
join
 tb_Goods_Data 
on 
  tb_Bill_Ex.CGoodsNo = tb_Goods_Data.CGoodsNo
join tb_Goods_Categories on 
  tb_Goods_Data.CGoodCatelogNo=tb_Goods_Data.CGoodCatelogNo
Group By
  tb_Goods_Categories.CCategoryNO,tb_Goods_Categories.CCategory

[htl258_Tony]

--调试后的版本
ALTER proc [dbo].[xf_rpt_turnover_category_list]
@begin varchar(10),
@end varchar(10)
as 
select e.CCategoryNO as 分类编号,e.CCategory as 分类名称,sum(成交金额) 成交金额,sum(成交比数) 成交比数
from tb_Bill a 
    join ( 
        select CBillNo,CGoodsNo,sum(DTotle) as 成交金额,count(CBillNo) 成交比数
        from  tb_Bill_Ex
        group by CBillNo,CGoodsNo
        ) b on a.CBillNo = b.CBillNo 
    join tb_Goods_Data d on b.CGoodsNo = d.CGoodsNo
	join tb_Goods_Categories e on charindex(e.CCategoryNo,d.CGoodCatelogNo)>0 and e.CBelong is null 
where a.TBillCreate>=@begin and a.TBillCreate<@end
group by e.CCategoryNO ,e.CCategory
GO

[bancxc]

。辛苦半天

[shiwenlu518]

帮顶```