3.2w+
社区成员
- 主页
- 基础类
- 应用实例
- 新技术前沿
如何得到如下表:
有表名sss
CREATE TABLE [dbo].[sss] (
[票号] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[序号] [int] NOT NULL ,
[小号] [int] NOT NULL ,
[产品编号] [char] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[库房] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[批号] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[效期] [datetime] NULL ,
[标识] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[库房数量] [numeric](18, 0) NOT NULL ,
[数量] [numeric](18, 0) NULL ,
[件数] [float] NULL ,
[保管员] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[单价] [float] NULL ,
[金额] [numeric](18, 2) NULL ,
[毛利] [numeric](18, 2) NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[sss] WITH NOCHECK ADD
CONSTRAINT [PK_sss] PRIMARY KEY CLUSTERED
(
[票号],
[序号],
[小号]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
有表cp
CREATE TABLE [dbo].[cp] (
[产品编号] [char] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[产品名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[规格] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[产地] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[单位] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[平均进价] [float] NOT NULL ,
[销售价1] [float] NOT NULL ,
) ON [PRIMARY]
GO
两个表用产品编号关联的,sss为销售表
sss
想得到如下结果
保管员 点5(cp.销售价1-yscc.单价/cp.销售价1<0.05) 点5(cp.销售价1-yscc.单价/cp.销售价1>0.05) 总金额(sum (yscc.金额))
谢谢
CREATE TABLE [dbo].[sss] (
[票号] [char] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[序号] [int] NOT NULL ,
[小号] [int] NOT NULL ,
[产品编号] [char] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[库房] [char] (10) COLLATE Chinese_PRC_CI_AS NULL ,
[批号] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[效期] [datetime] NULL ,
[标识] [char] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[库房数量] [numeric](18, 0) NOT NULL ,
[数量] [numeric](18, 0) NULL ,
[件数] [float] NULL ,
[保管员] [char] (8) COLLATE Chinese_PRC_CI_AS NULL ,
[单价] [float] NULL ,
[金额] [numeric](18, 2) NULL ,
[毛利] [numeric](18, 2) NULL ,
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[sss] WITH NOCHECK ADD
CONSTRAINT [PK_sss] PRIMARY KEY CLUSTERED
(
[票号],
[序号],
[小号]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO
有表cp
CREATE TABLE [dbo].[cp] (
[产品编号] [char] (13) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[产品名称] [varchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[规格] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[产地] [varchar] (20) COLLATE Chinese_PRC_CI_AS NULL ,
[单位] [char] (4) COLLATE Chinese_PRC_CI_AS NULL ,
[平均进价] [float] NOT NULL ,
[销售价1] [float] NOT NULL ,
) ON [PRIMARY]
GO
两个表用产品编号关联的,sss为销售表
sss
想得到如下结果
保管员 点5(cp.销售价1-yscc.单价/cp.销售价1<0.05) 点5(cp.销售价1-yscc.单价/cp.销售价1>0.05) 总金额(sum (yscc.金额))
谢谢
...全文
当前发帖距今超过3年，不再开放新的回复
发表回复
dawugui 2007-12-27
我还以为你真看懂了.
中国风 2007-12-27
可要细分销售中点在5以下的或5以上的就麻烦了..
猜一个
--[col1]=sum(case when (cp.销售价1-yscc.单价 /cp.销售价1 <0.05) then 金额 else 0 end),
[col2]=sum(case when (cp.销售价1-yscc.单价 /cp.销售价1> 0.05) then 金额 else 0 end)
猜一个
--[col1]=sum(case when (cp.销售价1-yscc.单价 /cp.销售价1 <0.05) then 金额 else 0 end),
[col2]=sum(case when (cp.销售价1-yscc.单价 /cp.销售价1> 0.05) then 金额 else 0 end)
想飞的狼 2007-12-27
不好意思,俺没讲明白,咱晚上没想出来,今早整出来了,谢谢个位提醒,分照给,行列转换.
select sss.保管员,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.05) then 金额 else 0 end) as y1,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.15) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.05) then 金额 else 0 end) as y2,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.25) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.15) then 金额 else 0 end) as y3,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.35) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.25) then 金额 else 0 end) as y4,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.45) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.35) then 金额 else 0 end) as y5,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.55) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.45) then 金额 else 0 end) as y6,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.65) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.55) then 金额 else 0 end) as y7,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1>=0.65) then 金额 else 0 end) as y8,sum(金额) as 总金额 from sss inner join cp on cp.产品编号=sss.产品编号 group by sss.保管员 order by sss.保管员
sss和yscc是一个表
select sss.保管员,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.05) then 金额 else 0 end) as y1,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.15) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.05) then 金额 else 0 end) as y2,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.25) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.15) then 金额 else 0 end) as y3,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.35) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.25) then 金额 else 0 end) as y4,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.45) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.35) then 金额 else 0 end) as y5,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.55) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.45) then 金额 else 0 end) as y6,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1<0.65) and ((cp.销售价1-sss.单价)/cp.销售价1>=0.55) then 金额 else 0 end) as y7,sum(case when ((cp.销售价1-sss.单价)/cp.销售价1>=0.65) then 金额 else 0 end) as y8,sum(金额) as 总金额 from sss inner join cp on cp.产品编号=sss.产品编号 group by sss.保管员 order by sss.保管员
sss和yscc是一个表
nanalilo 2007-12-26
yscc是什么表？怎么没有提到？
dawugui 2007-12-26
我没看懂.楼下继续.
想飞的狼 2007-12-26
得到每个保管员总销售金额很容易,
select 保管员,sum(金额) from yscc
可要细分销售中点在5以下的或5以上的就麻烦了,不会了,还请高人指点下
select 保管员,sum(金额) from yscc
可要细分销售中点在5以下的或5以上的就麻烦了,不会了,还请高人指点下
