如何得到如下表:

[想飞的狼]

有表名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.金额))

谢谢

[想飞的狼]

得到每个保管员总销售金额很容易,

select 保管员,sum(金额) from yscc

可要细分销售中点在5以下的或5以上的就麻烦了,不会了,还请高人指点下

[dawugui]

我没看懂.楼下继续.

[nanalilo]

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是一个表

[中国风]

可要细分销售中点在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)

[dawugui]

我还以为你真看懂了.