zwwldw表 zwwldw_dwbh(客户编号),zwwldw_dwmc(客户名称)
XSTD表:XSTD_SHDKH(客户编号),XSTD_TDLS(提单流水号), XSTD_DJRQ(日期),
XSTDMX表:XSTDMX_TDLS(提单流水号),XSTDMX_ZSL(本日销售量),XSTDMX_BHSE(本日应收款)
XSHK表:XSHK_SHDKH(客户编号),XSHK_BHKE(本日实收款),XSHK_DJRQ(日期)存储过程实现功能:当客户输入日期后按客户统计,表如下:
日期:如:20070428 本日销售是指20070428当天的
本月销售是指从20070401到20070428之间的
客户编号 客户名称 本日销售总量 本日应收总款 本日实收总款
本月累计销售总量 本月累计应收总款 本月累计实收总款http://community.csdn.net/Expert/TopicView3.asp?id=5502220
http://community.csdn.net/Expert/TopicView3.asp?id=5504448
再加这个,我已经开出180分了!有高手帮忙吗?谢谢了~问题提示(只要把这两个表合并起来就基本完成了!):
表一:select sum(CASE WHEN b.xstd_djrq='20070404' THEN 1 ELSE 0 END),b.xstd_djrq,b.XSTD_SHDKH,b.XSTD_SHDKHMC,sum(a.xstdmx_zsl),sum(a.xstdmx_bhse),
sum(c.xshk_bhke)
from (xstdmx as a inner join xstd as b ON a.xstdmx_tdls = b.xstd_tdls)
inner join xshk as c ON c.xshk_shdkh=b.xstd_shdkh
where b.xstd_djrq='20070404' group by b.XSTD_SHDKH,b.XSTD_SHDKHMC,b.xstd_djrq表二:select sum(CASE WHEN bb.xstd_djrq<='20070404' and bb.xstd_djrq>='20070401' THEN 1 ELSE 0 END),bb.XSTD_SHDKH,bb.XSTD_SHDKHMC,sum(aa.xstdmx_zsl),sum(aa.xstdmx_bhse),
sum(cc.xshk_bhke)
from (xstdmx as aa inner join xstd as bb ON aa.xstdmx_tdls = bb.xstd_tdls)
inner join xshk as cc ON cc.xshk_shdkh=bb.xstd_shdkh
where bb.xstd_djrq<='20070404' and bb.xstd_djrq>='20070401' group by bb.XSTD_SHDKH,bb.XSTD_SHDKHMC
查询出来表一:
4200704041002山矿供应公司4.077200.0106230.0
3200704043220神华集团包头矿业有限责任公司机电制造安装分公司3.0312844.05000000005297000.0
66200704043149通用客户5280.01011120.0391220.0
表二:
43349华电国际电力有限公司8.01240000.017640000.0
21003济宁华电电力设备有限公司60.01200.02280.0
41969嘉星热电有限公司52.08000.016000.0
20151江苏淮钢集团有限公司2.05540000.0689000.0
81002山矿供应公司8.0309200.0212460.0
33220神华集团包头矿业有限责任公司机电制造安装分公司3.0312844.05000000005297000.0
4623149通用客户7260.01597200.02738540.0
表二客户数量比表一多!怎么把两个表合并在一起?
XSTD表:XSTD_SHDKH(客户编号),XSTD_TDLS(提单流水号), XSTD_DJRQ(日期),
XSTDMX表:XSTDMX_TDLS(提单流水号),XSTDMX_ZSL(本日销售量),XSTDMX_BHSE(本日应收款)
XSHK表:XSHK_SHDKH(客户编号),XSHK_BHKE(本日实收款),XSHK_DJRQ(日期)存储过程实现功能:当客户输入日期后按客户统计,表如下:
日期:如:20070428 本日销售是指20070428当天的
本月销售是指从20070401到20070428之间的
客户编号 客户名称 本日销售总量 本日应收总款 本日实收总款
本月累计销售总量 本月累计应收总款 本月累计实收总款http://community.csdn.net/Expert/TopicView3.asp?id=5502220
http://community.csdn.net/Expert/TopicView3.asp?id=5504448
再加这个,我已经开出180分了!有高手帮忙吗?谢谢了~问题提示(只要把这两个表合并起来就基本完成了!):
表一:select sum(CASE WHEN b.xstd_djrq='20070404' THEN 1 ELSE 0 END),b.xstd_djrq,b.XSTD_SHDKH,b.XSTD_SHDKHMC,sum(a.xstdmx_zsl),sum(a.xstdmx_bhse),
sum(c.xshk_bhke)
from (xstdmx as a inner join xstd as b ON a.xstdmx_tdls = b.xstd_tdls)
inner join xshk as c ON c.xshk_shdkh=b.xstd_shdkh
where b.xstd_djrq='20070404' group by b.XSTD_SHDKH,b.XSTD_SHDKHMC,b.xstd_djrq表二:select sum(CASE WHEN bb.xstd_djrq<='20070404' and bb.xstd_djrq>='20070401' THEN 1 ELSE 0 END),bb.XSTD_SHDKH,bb.XSTD_SHDKHMC,sum(aa.xstdmx_zsl),sum(aa.xstdmx_bhse),
sum(cc.xshk_bhke)
from (xstdmx as aa inner join xstd as bb ON aa.xstdmx_tdls = bb.xstd_tdls)
inner join xshk as cc ON cc.xshk_shdkh=bb.xstd_shdkh
where bb.xstd_djrq<='20070404' and bb.xstd_djrq>='20070401' group by bb.XSTD_SHDKH,bb.XSTD_SHDKHMC
查询出来表一:
4200704041002山矿供应公司4.077200.0106230.0
3200704043220神华集团包头矿业有限责任公司机电制造安装分公司3.0312844.05000000005297000.0
66200704043149通用客户5280.01011120.0391220.0
表二:
43349华电国际电力有限公司8.01240000.017640000.0
21003济宁华电电力设备有限公司60.01200.02280.0
41969嘉星热电有限公司52.08000.016000.0
20151江苏淮钢集团有限公司2.05540000.0689000.0
81002山矿供应公司8.0309200.0212460.0
33220神华集团包头矿业有限责任公司机电制造安装分公司3.0312844.05000000005297000.0
4623149通用客户7260.01597200.02738540.0
表二客户数量比表一多!怎么把两个表合并在一起?
首先把关联关系搞清楚,然后使用CASE语句分类,然后sum求和,group by一下就行了。
sum(c.xshk_bhke),sum(CASE WHEN bb.xstd_djrq<='20070404' and bb.xstd_djrq>='20070401' THEN 1 ELSE 0 END)
from (xstdmx as a inner join xstd as b ON a.xstdmx_tdls = b.xstd_tdls)
inner join xshk as c ON c.xshk_shdkh=b.xstd_shdkh
select 信息
from 用户表1 left join
表一 on 条件 left join
表二 ON 条件
CREATE TABLE [lc0029999].[aaa] (
[XSTD_SHDKH] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[XSTD_SHDKHMC] [char] (60) COLLATE Chinese_PRC_CI_AS NULL ,
[xstdmx_zsl_m] [u001] NULL ,
[xstdmx_bhse_m] [u001] NULL ,
[xshk_bhke_m] [u001] NULL
) ON [PRIMARY]
GOCREATE TABLE [lc0029999].[bbb] (
[XSTD_SHDKH] [char] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[XSTD_SHDKHMC] [char] (60) COLLATE Chinese_PRC_CI_AS NULL ,
[xstdmx_zsl_day] [u001] NULL ,
[xstdmx_bhse_day] [u001] NULL ,
[xshk_bhke_day] [u001] NULL
) ON [PRIMARY]
GOCREATE PROCEDURE SP_TEST( @datea char(8))
AS
begin
Declare @date char(8)
While @date=@datea
begin
delete from aaa
delete from bbb
insert into aaa select bb.XSTD_SHDKH,bb.XSTD_SHDKHMC,
sum(aa.xstdmx_zsl) as xstdmx_zsl_m,sum(aa.xstdmx_bhse) as xstdmx_bhse_m,sum(cc.xshk_bhke) as xshk_bhke_m
from (xstdmx as aa inner join xstd as bb ON aa.xstdmx_tdls = bb.xstd_tdls)
inner join xshk as cc ON cc.xshk_shdkh=bb.xstd_shdkh
where bb.xstd_djrq<=@date and bb.xstd_djrq>=Convert(nvarchar,year(@date))+Convert(nvarchar,month(@date))+'01'
group by bb.XSTD_SHDKH,bb.XSTD_SHDKHMCinsert into bbb select b.XSTD_SHDKH,b.XSTD_SHDKHMC,sum(a.xstdmx_zsl) as xstdmx_zsl_day,
sum(a.xstdmx_bhse) as xstdmx_bhse_day,sum(c.xshk_bhke) as xshk_bhke_day
from (xstdmx as a inner join xstd as b ON a.xstdmx_tdls = b.xstd_tdls)
inner join xshk as c ON c.xshk_shdkh=b.xstd_shdkh
where b.xstd_djrq=@date group by b.XSTD_SHDKH,b.XSTD_SHDKHMC,b.xstd_djrqselect m.XSTD_SHDKH,m.XSTD_SHDKHMC,d.xstdmx_zsl_day,d.xstdmx_bhse_day,d.xshk_bhke_day,m.xstdmx_zsl_m,m.xstdmx_bhse_m,m.xshk_bhke_m
from aaa as m left join bbb as d on m.XSTD_SHDKH=d.XSTD_SHDKHend
endGO
AS
begin
select
m.XSTD_SHDKH,m.XSTD_SHDKHMC,d.xstdmx_zsl_day,d.xstdmx_bhse_day,d.xshk_bhke_day,m.xstdmx_zsl_m,m.xstdmx_bhse_m,m.xshk_bhke_m
from
(select bb.XSTD_SHDKH,bb.XSTD_SHDKHMC,
sum(aa.xstdmx_zsl) as xstdmx_zsl_m,sum(aa.xstdmx_bhse) as xstdmx_bhse_m,sum(cc.xshk_bhke) as xshk_bhke_m
from (xstdmx as aa inner join xstd as bb ON aa.xstdmx_tdls = bb.xstd_tdls)
inner join xshk as cc ON cc.xshk_shdkh=bb.xstd_shdkh
where bb.xstd_djrq<=@datea and bb.xstd_djrq>=Convert(nvarchar,year(@datea))+Convert(nvarchar,month(@datea))+'01'
group by bb.XSTD_SHDKH,bb.XSTD_SHDKHMC) as m
left join
(select b.XSTD_SHDKH,b.XSTD_SHDKHMC,sum(a.xstdmx_zsl) as xstdmx_zsl_day,
sum(a.xstdmx_bhse) as xstdmx_bhse_day,sum(c.xshk_bhke) as xshk_bhke_day
from (xstdmx as a inner join xstd as b ON a.xstdmx_tdls = b.xstd_tdls)
inner join xshk as c ON c.xshk_shdkh=b.xstd_shdkh
where b.xstd_djrq=@datea group by b.XSTD_SHDKH,b.XSTD_SHDKHMC,b.xstd_djrq) as d
on m.XSTD_SHDKH=d.XSTD_SHDKH
end
where bb.xstd_djrq<=@datea and bb.xstd_djrq>=Convert(nvarchar,year(@datea))+Convert(nvarchar,month(@datea))+'01'
可以這麼修改下
where bb.xstd_djrq<=@datea and bb.xstd_djrq>=Left(@datea, 6) +'01'