请各位老大和zjcxc(邹建)进来看看啊!复杂查询问题? 得出的正价出货额是实际的出货额的好几倍。具体的倍数就是看查询的天数了。单独一天的数据是正确的(即@strdate=@EndDate). 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 declare @StrDate varchar(15), @EndDate varchar(15), @Xqbh varchar(10)set @StrDate='2004-05-17'set @EndDate='2004-05-17'set @Xqbh='0113'select a.wdid ,出货总数=sum(a.isum) ,正价出货数=sum(case a.tjflag when 0 then a.isum else 0 end) ,正价出货额=sum(case a.tjflag when 0 then a.isum*a.jg_zj*(1-b.kd)*(1-b.fd)*(1-b.fl) else 0 end) ,特价出货数=sum(case a.tjflag when 1 then a.isum else 0 end) ,特价出货额=sum(case a.tjflag when 1 then a.isum*a.jg_zj*(1-a.tj_kd)*(1-a.tj_fd)*(1-a.tj_fl) else 0 end)-- ,销售成本合计=sum(a.isum*a.jg_cb)from chd_cpmx a right join chd_yywd b on a.tjflag=0 and a.wdid=b.wdid and b.xqbh=@xqbh and (b.lrsj between @Strdate and @EndDate)and (b.js_yj=1)where (a.lrsj between @Strdate and @EndDate) and substring(a.wdid,1,4)=@xqbhgroup by a.wdid结果为wdid 出货总数 正价出货数 正价出货额 特价出货数 特价出货额 销售成本合计 0113047 20 20 37.600000000000001 0 0.0 .00000113048 24 24 46.399999999999999 0 0.0 .00000113049 0 0 0.0 0 0.0 .00000113050 37 37 72.0 0 0.0 .00000113051 0 0 0.0 0 0.0 .00000113052 22 22 43.399999999999999 0 0.0 .00000113053 0 0 0.0 0 0.0 .00000113054 30 30 60.600000000000001 0 0.0 .00000113055 22 22 31.0 0 0.0 .00000113056 10 10 14.0 0 0.0 .00000113057 0 0 0.0 0 0.0 .00000113058 0 0 0.0 0 0.0 .00000113059 0 0 0.0 0 0.0 .00000113060 0 0 0.0 0 0.0 .00000113061 25 25 45.600000000000001 0 0.0 .00000113062 0 0 0.0 0 0.0 .00000113063 0 0 0.0 0 0.0 .00000113064 0 0 0.0 0 0.0 .0000把时间改为set @StrDate='2004-05-16'set @EndDate='2004-05-17'而16日没有数量全部是零查询结果为:wdid 出货总数 正价出货数 正价出货额 特价出货数 特价出货额 销售成本合计 0113047 40 40 75.200000000000003 0 0.0 .00000113048 48 48 92.799999999999997 0 0.0 .00000113049 0 0 0.0 0 0.0 .00000113050 74 74 144.0 0 0.0 .00000113051 0 0 0.0 0 0.0 .00000113052 44 44 86.799999999999997 0 0.0 .00000113053 0 0 0.0 0 0.0 .00000113054 60 60 121.2 0 0.0 .00000113055 44 44 62.0 0 0.0 .00000113056 20 20 28.0 0 0.0 .00000113057 0 0 0.0 0 0.0 .00000113058 0 0 0.0 0 0.0 .00000113059 0 0 0.0 0 0.0 .00000113060 0 0 0.0 0 0.0 .00000113061 50 50 91.200000000000003 0 0.0 .00000113062 0 0 0.0 0 0.0 .00000113063 0 0 0.0 0 0.0 .00000113064 0 0 0.0 0 0.0 .0000查询的结果都乘以了天数了. 太复杂,懒得看明白。但是一眼就能看明白的是:你应该使用 inner join。 哦,看错了!sorry。right join正确。 你看一看在你上面设定的日期区间内,select count(*)from chd_cpmx a where (a.lrsj between @Strdate and @EndDate) and substring(a.wdid,1,4)=@xqbhgroup by a.wdid是不是一样的!如果不一样,那么你的结果肯定不一样!如果是一样的,建议你调一下right join后面的几个ON条件。 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chd_cpmx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[chd_cpmx]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chd_yywd]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[chd_yywd]GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fhd_cpmx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)drop table [dbo].[fhd_cpmx]GO--每天初始化后,一下三个表都添加添加一条新记录,然后由人工录入出货数和返货数。CREATE TABLE [dbo].[chd_yywd] ( --营业网点表 [wdid] [varchar] (10) NOT NULL , [Name] [varchar] (30) NULL , [Wdbh] [varchar] (6) NULL , [bmbh] [varchar] (3) NULL , [bmmc] [varchar] (30) NULL , [xqbh] [varchar] (5) NULL , [xqmc] [varchar] (30) NULL , [type] [varchar] (3) NULL , [js_rj] [bit] NULL , --日结标志 [js_yj] [bit] NULL , --月结 [yj_syrq] [varchar] (2) NULL , [yj_byrq] [varchar] (2) NULL , [js_ljse] [bit] NULL , [ljse_xj] [money] NULL , [kd] [float] NULL , [fd] [float] NULL , [fl] [float] NULL , [lsrw] [money] NULL , [hkrw] [money] NULL , [fpw] [money] NULL , [fpn] [money] NULL , [qt] [money] NULL , [ftglf] [money] NULL , [lsglf] [money] NULL , [glr_id] [varchar] (5) NULL , [glr_man] [varchar] (10) NULL , [xlbh] [varchar] (5) NULL , [xltype] [varchar] (10) NULL , [plxh] [numeric](18, 0) NULL , [rlbz] [bit] NULL , [rlll] [float] NULL , [rl_lx] [bit] NULL , [rlje] [money] NULL , [Address] [varchar] (30) NULL , [phone] [varchar] (18) NULL , [jsrq] [varchar] (12) NULL , [lrsj] [varchar] (12) NOT NULL , --录入日期 [gysID] [varchar] (20) NULL , [gysbm] [varchar] (10) NULL , [jsbz] [bit] NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[chd_cpmx] ( --出货单 [cpbh] [varchar] (18) NOT NULL , [Name] [varchar] (30) NULL , [WdID] [varchar] (12) NOT NULL , [type] [varchar] (3) NULL , [jg_cb] [money] NULL , [jg_zj] [money] NULL , [tjflag] [bit] NULL ,--特价标志 [tj_kd] [float] NULL , [tj_fd] [float] NULL , [tj_fl] [float] NULL , [isum] [int] NULL , [jsrq] [varchar] (12) NOT NULL , [lrsj] [varchar] (12) NOT NULL ,--录入日期 [dnbm] [varchar] (20) NULL ) ON [PRIMARY]GOCREATE TABLE [dbo].[fhd_cpmx] ( --返货单 [cpbh] [varchar] (18) NOT NULL , [Name] [varchar] (30) NULL , [wdid] [varchar] (12) NOT NULL , [type] [varchar] (3) NULL , [jg_cb] [money] NULL , [jg_zj] [money] NULL , [tjflag] [bit] NULL , --特价标志 [tj_kd] [float] NULL , [tj_fd] [float] NULL , [tj_fl] [float] NULL , [isum] [int] NULL , [jsrq] [varchar] (12) NULL , [lrsj] [varchar] (12) NOT NULL ,--录入日期 [dnbm] [varchar] (20) NULL ) ON [PRIMARY]GOALTER TABLE [dbo].[chd_cpmx] ADD CONSTRAINT [PK_chd_cpmx] PRIMARY KEY CLUSTERED ( [cpbh], [WdID], [lrsj] ) ON [PRIMARY] GOALTER TABLE [dbo].[chd_yywd] ADD CONSTRAINT [DF_chd_yywd_jsbz] DEFAULT (0) FOR [jsbz], CONSTRAINT [PK_chd_yywd] PRIMARY KEY CLUSTERED ( [wdid], [lrsj] ) ON [PRIMARY] GOALTER TABLE [dbo].[fhd_cpmx] ADD CONSTRAINT [PK_fhd_cpmx] PRIMARY KEY CLUSTERED ( [cpbh], [wdid], [lrsj] ) ON [PRIMARY] GO 树的儿子,孙子,曾孙。。。。 求一触发器语句,急用... 存储过程问题 用一条sql语句可以实现这样的效果吗 数据库文件mdf,log文件的修改时间不变 数据库建表动态指定表项 SQL日志过大,如何打开查看? 求一个简单的SQL语句 关于动态执行公式的方法 怎么样实现如下的sql?谢谢! 请pisces007(蝶鱼) ,dut(到哪里都是菜鸟), zjcxc(邹建) 进来 MSDE安装的时候如果没有安装网络协议,通过什么连接?
@EndDate varchar(15),
@Xqbh varchar(10)
set @StrDate='2004-05-17'
set @EndDate='2004-05-17'
set @Xqbh='0113'select a.wdid
,出货总数=sum(a.isum)
,正价出货数=sum(case a.tjflag when 0 then a.isum else 0 end)
,正价出货额=sum(case a.tjflag when 0 then a.isum*a.jg_zj*(1-b.kd)*(1-b.fd)*(1-b.fl) else 0 end)
,特价出货数=sum(case a.tjflag when 1 then a.isum else 0 end)
,特价出货额=sum(case a.tjflag when 1 then a.isum*a.jg_zj*(1-a.tj_kd)*(1-a.tj_fd)*(1-a.tj_fl) else 0 end)--
,销售成本合计=sum(a.isum*a.jg_cb)
from chd_cpmx a
right join chd_yywd b on a.tjflag=0 and a.wdid=b.wdid and b.xqbh=@xqbh and (b.lrsj between @Strdate and @EndDate)and (b.js_yj=1)
where (a.lrsj between @Strdate and @EndDate) and substring(a.wdid,1,4)=@xqbh
group by a.wdid
结果为
wdid 出货总数 正价出货数 正价出货额 特价出货数 特价出货额 销售成本合计
0113047 20 20 37.600000000000001 0 0.0 .0000
0113048 24 24 46.399999999999999 0 0.0 .0000
0113049 0 0 0.0 0 0.0 .0000
0113050 37 37 72.0 0 0.0 .0000
0113051 0 0 0.0 0 0.0 .0000
0113052 22 22 43.399999999999999 0 0.0 .0000
0113053 0 0 0.0 0 0.0 .0000
0113054 30 30 60.600000000000001 0 0.0 .0000
0113055 22 22 31.0 0 0.0 .0000
0113056 10 10 14.0 0 0.0 .0000
0113057 0 0 0.0 0 0.0 .0000
0113058 0 0 0.0 0 0.0 .0000
0113059 0 0 0.0 0 0.0 .0000
0113060 0 0 0.0 0 0.0 .0000
0113061 25 25 45.600000000000001 0 0.0 .0000
0113062 0 0 0.0 0 0.0 .0000
0113063 0 0 0.0 0 0.0 .0000
0113064 0 0 0.0 0 0.0 .0000
把时间改为
set @StrDate='2004-05-16'
set @EndDate='2004-05-17'
而16日没有数量全部是零
查询结果为:
wdid 出货总数 正价出货数 正价出货额 特价出货数 特价出货额 销售成本合计
0113047 40 40 75.200000000000003 0 0.0 .0000
0113048 48 48 92.799999999999997 0 0.0 .0000
0113049 0 0 0.0 0 0.0 .0000
0113050 74 74 144.0 0 0.0 .0000
0113051 0 0 0.0 0 0.0 .0000
0113052 44 44 86.799999999999997 0 0.0 .0000
0113053 0 0 0.0 0 0.0 .0000
0113054 60 60 121.2 0 0.0 .0000
0113055 44 44 62.0 0 0.0 .0000
0113056 20 20 28.0 0 0.0 .0000
0113057 0 0 0.0 0 0.0 .0000
0113058 0 0 0.0 0 0.0 .0000
0113059 0 0 0.0 0 0.0 .0000
0113060 0 0 0.0 0 0.0 .0000
0113061 50 50 91.200000000000003 0 0.0 .0000
0113062 0 0 0.0 0 0.0 .0000
0113063 0 0 0.0 0 0.0 .0000
0113064 0 0 0.0 0 0.0 .0000查询的结果都乘以了天数了.
select count(*)
from chd_cpmx a
where (a.lrsj between @Strdate and @EndDate) and substring(a.wdid,1,4)=@xqbh
group by a.wdid
是不是一样的!如果不一样,那么你的结果肯定不一样!
如果是一样的,建议你调一下right join后面的几个ON条件。
drop table [dbo].[chd_cpmx]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[chd_yywd]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[chd_yywd]
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fhd_cpmx]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[fhd_cpmx]
GO--每天初始化后,一下三个表都添加添加一条新记录,然后由人工录入出货数和返货数。
CREATE TABLE [dbo].[chd_yywd] ( --营业网点表
[wdid] [varchar] (10) NOT NULL ,
[Name] [varchar] (30) NULL ,
[Wdbh] [varchar] (6) NULL ,
[bmbh] [varchar] (3) NULL ,
[bmmc] [varchar] (30) NULL ,
[xqbh] [varchar] (5) NULL ,
[xqmc] [varchar] (30) NULL ,
[type] [varchar] (3) NULL ,
[js_rj] [bit] NULL , --日结标志
[js_yj] [bit] NULL , --月结
[yj_syrq] [varchar] (2) NULL ,
[yj_byrq] [varchar] (2) NULL ,
[js_ljse] [bit] NULL ,
[ljse_xj] [money] NULL ,
[kd] [float] NULL ,
[fd] [float] NULL ,
[fl] [float] NULL ,
[lsrw] [money] NULL ,
[hkrw] [money] NULL ,
[fpw] [money] NULL ,
[fpn] [money] NULL ,
[qt] [money] NULL ,
[ftglf] [money] NULL ,
[lsglf] [money] NULL ,
[glr_id] [varchar] (5) NULL ,
[glr_man] [varchar] (10) NULL ,
[xlbh] [varchar] (5) NULL ,
[xltype] [varchar] (10) NULL ,
[plxh] [numeric](18, 0) NULL ,
[rlbz] [bit] NULL ,
[rlll] [float] NULL ,
[rl_lx] [bit] NULL ,
[rlje] [money] NULL ,
[Address] [varchar] (30) NULL ,
[phone] [varchar] (18) NULL ,
[jsrq] [varchar] (12) NULL ,
[lrsj] [varchar] (12) NOT NULL , --录入日期
[gysID] [varchar] (20) NULL ,
[gysbm] [varchar] (10) NULL ,
[jsbz] [bit] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[chd_cpmx] ( --出货单
[cpbh] [varchar] (18) NOT NULL ,
[Name] [varchar] (30) NULL ,
[WdID] [varchar] (12) NOT NULL ,
[type] [varchar] (3) NULL ,
[jg_cb] [money] NULL ,
[jg_zj] [money] NULL ,
[tjflag] [bit] NULL ,--特价标志
[tj_kd] [float] NULL ,
[tj_fd] [float] NULL ,
[tj_fl] [float] NULL ,
[isum] [int] NULL ,
[jsrq] [varchar] (12) NOT NULL ,
[lrsj] [varchar] (12) NOT NULL ,--录入日期
[dnbm] [varchar] (20) NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[fhd_cpmx] ( --返货单
[cpbh] [varchar] (18) NOT NULL ,
[Name] [varchar] (30) NULL ,
[wdid] [varchar] (12) NOT NULL ,
[type] [varchar] (3) NULL ,
[jg_cb] [money] NULL ,
[jg_zj] [money] NULL ,
[tjflag] [bit] NULL , --特价标志
[tj_kd] [float] NULL ,
[tj_fd] [float] NULL ,
[tj_fl] [float] NULL ,
[isum] [int] NULL ,
[jsrq] [varchar] (12) NULL ,
[lrsj] [varchar] (12) NOT NULL ,--录入日期
[dnbm] [varchar] (20) NULL
) ON [PRIMARY]
GOALTER TABLE [dbo].[chd_cpmx] ADD
CONSTRAINT [PK_chd_cpmx] PRIMARY KEY CLUSTERED
(
[cpbh],
[WdID],
[lrsj]
) ON [PRIMARY]
GOALTER TABLE [dbo].[chd_yywd] ADD
CONSTRAINT [DF_chd_yywd_jsbz] DEFAULT (0) FOR [jsbz],
CONSTRAINT [PK_chd_yywd] PRIMARY KEY CLUSTERED
(
[wdid],
[lrsj]
) ON [PRIMARY]
GOALTER TABLE [dbo].[fhd_cpmx] ADD
CONSTRAINT [PK_fhd_cpmx] PRIMARY KEY CLUSTERED
(
[cpbh],
[wdid],
[lrsj]
) ON [PRIMARY]
GO