再一次求助，有关SQL语句问题

suwenkai · 发表于 2010-10-5 15:49

知道是怎么回事了，看来是自己的疏忽了。。。
你更新一下语句就可以了。
select a1.品名,a2.类别,
iif(分类="出库到柜台1",数量,0) as 出库到柜台1,
iif(分类="出库到柜台2",数量,0) as 出库到柜台2,
iif(分类="出库到柜台3",数量,0) as 出库到柜台3,
iif(分类="出库到其他",数量,0) as 出库到其他,
iif(分类="柜台1销售",数量,0) as 柜台1销售,
iif(分类="柜台2销售",数量,0) as 柜台2销售,
iif(分类="柜台3销售",数量,0) as 柜台3销售,
iif(分类="其他销售清单",数量,0) as 其他销售清单,
iif(分类="出库到柜台1",数量,0) - iif(分类="柜台1销售",数量,0) as 柜台1库存,
iif(分类="出库到柜台2",数量,0) - iif(分类="柜台2销售",数量,0) as 柜台2库存,
iif(分类="出库到柜台3",数量,0) - iif(分类="柜台3销售",数量,0) as 柜台3库存,
iif(分类="出库到其他",数量,0) - iif(分类="其他销售清单",数量,0) as 其他库存
from
(select 品名,val(数量) as 数量,分类
from (select 品名,入库数量 as 数量,"库数量" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到柜台1,"出库到柜台1" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到柜台2,"出库到柜台2" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到柜台3,"出库到柜台3" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到其他,"出库到其他" as 分类 from [公司出入库$a2:j] union all
select 品名,公司库存,"公司库存" as 分类 from [公司出入库$a2:j] union all
select 品名,数量,"柜台1销售"  as 分类 from [柜台1销售清单$a2:j] union all
select 品名,数量,"柜台2销售"  as 分类 from [柜台2销售清单$a2:j] union all
select 品名,数量,"柜台3销售"  as 分类 from [柜台3销售清单$a2:j] union all
select 品名,数量,"其他销售清单"  as 分类 from [其他销售清单$a2:j] ) where 数量 is not null) a1 right join
(select distinct 类别,品名 from [公司出入库$a2:j] ) a2 on a1.品名=a2.品名

brdsz · 发表于 2010-10-5 18:06

谢谢suwenkai这么热心的帮助，真的很不好意思，我现在正在学习透视表和SQL，也就半个月，可能也就刚刚入门，连照葫芦画瓢的水平都还没有达到。只能从大家这里“拿来”用，所以一直在麻烦大家。

我刚刚试了一下，很好，基本达到我要的目的。但是请suwenkai再帮忙，

“入库数量”和“公司库存”麻烦suwenkai再帮我加上去，其他的项目不变

。因为这两个数据对我来说很关键。再次感谢！！！！！！！！！！

suwenkai · 发表于 2010-10-5 18:11

select a1.品名,a2.类别,
iif(分类="入库数量",数量,0) as 入库数量,
iif(分类="公司库存",数量,0) as 公司库存,
iif(分类="出库到柜台1",数量,0) as 出库到柜台1,
iif(分类="出库到柜台2",数量,0) as 出库到柜台2,
iif(分类="出库到柜台3",数量,0) as 出库到柜台3,
iif(分类="出库到其他",数量,0) as 出库到其他,
iif(分类="柜台1销售",数量,0) as 柜台1销售,
iif(分类="柜台2销售",数量,0) as 柜台2销售,
iif(分类="柜台3销售",数量,0) as 柜台3销售,
iif(分类="其他销售清单",数量,0) as 其他销售清单,
iif(分类="出库到柜台1",数量,0) - iif(分类="柜台1销售",数量,0) as 柜台1库存,
iif(分类="出库到柜台2",数量,0) - iif(分类="柜台2销售",数量,0) as 柜台2库存,
iif(分类="出库到柜台3",数量,0) - iif(分类="柜台3销售",数量,0) as 柜台3库存,
iif(分类="出库到其他",数量,0) - iif(分类="其他销售清单",数量,0) as 其他库存
from
(select 品名,val(数量) as 数量,分类
from (select 品名,入库数量 as 数量,"库数量" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到柜台1,"出库到柜台1" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到柜台2,"出库到柜台2" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到柜台3,"出库到柜台3" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到其他,"出库到其他" as 分类 from [公司出入库$a2:j] union all
select 品名,公司库存,"公司库存" as 分类 from [公司出入库$a2:j] union all
select 品名,数量,"柜台1销售"  as 分类 from [柜台1销售清单$a2:j] union all
select 品名,数量,"柜台2销售"  as 分类 from [柜台2销售清单$a2:j] union all
select 品名,数量,"柜台3销售"  as 分类 from [柜台3销售清单$a2:j] union all
select 品名,数量,"其他销售清单"  as 分类 from [其他销售清单$a2:j] ) where 数量 is not null) a1 right join
(select distinct 类别,品名 from [公司出入库$a2:j] ) a2 on a1.品名=a2.品名

brdsz · 发表于 2010-10-5 18:25

谢谢suwenkai，这么快就回复。我试了一下，但“入库数量”在透视表中全部为“0”，麻烦suwenkai帮我再检查一下，是不是我的操作问题？如是请提醒，谢谢！！！！！！

brdsz · 发表于 2010-10-5 18:30

原帖由 brdsz 于 2010-10-5 18:25 发表
谢谢suwenkai，这么快就回复。我试了一下，但“入库数量”在透视表中全部为“0”，麻烦suwenkai帮我再检查一下，是不是我的操作问题？如是请提醒，谢谢！！！！！！

找到原因了，是“from (select 品名,入库数量 as 数量,"库数量" as 分类 from [公司出入库$a2:j] union all”中漏了一个“入”字，谢谢suwenkai！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！！

brdsz · 发表于 2010-10-5 21:31

suwenkai，你好！我都不好意思再开口了，晚上跟几个同事在一起讨论这个透视表，大家都觉得很好。但是有同事提出，要是把四个销售清单中的“每一种货品相对应的金额”能够汇总到一起会更完美。所以希望suwenkai再帮忙在透视表中加上“销售金额”这一栏。在四个销售清单表中都分别有“金额”这一栏，希望将每一种货品在四个销售清单中相对应的金额汇总到透视表中。先谢谢了！！！！！

suwenkai · 发表于 2010-10-5 22:46

经测试一下，以下的语句没问题。
select a1.品名,a2.类别,
iif(分类="入库数量",数量,0) as 入库数量,
iif(分类="公司库存",数量,0) as 公司库存,
iif(分类="出库到柜台1",数量,0) as 出库到柜台1,
iif(分类="出库到柜台2",数量,0) as 出库到柜台2,
iif(分类="出库到柜台3",数量,0) as 出库到柜台3,
iif(分类="出库到其他",数量,0) as 出库到其他,
iif(分类="柜台1销售",数量,0) as 柜台1销售,
iif(分类="柜台2销售",数量,0) as 柜台2销售,
iif(分类="柜台3销售",数量,0) as 柜台3销售,
iif(分类="其他销售清单",数量,0) as 其他销售清单,
iif(分类="出库到柜台1",数量,0) - iif(分类="柜台1销售",数量,0) as 柜台1库存,
iif(分类="出库到柜台2",数量,0) - iif(分类="柜台2销售",数量,0) as 柜台2库存,
iif(分类="出库到柜台3",数量,0) - iif(分类="柜台3销售",数量,0) as 柜台3库存,
iif(分类="出库到其他",数量,0) - iif(分类="其他销售清单",数量,0) as 其他库存,
iif(分类="柜台1销售金额",数量,0) as 柜台1销售金额,
iif(分类="柜台2销售金额",数量,0) as 柜台2销售金额,
iif(分类="柜台3销售金额",数量,0) as 柜台3销售金额,
iif(分类="其他销售金额",数量,0) as 其他销售金额

from
(select 品名,val(数量) as 数量,分类
from (select 品名,入库数量 as 数量,"入库数量" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到柜台1,"出库到柜台1" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到柜台2,"出库到柜台2" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到柜台3,"出库到柜台3" as 分类 from [公司出入库$a2:j] union all
select 品名,出库到其他,"出库到其他" as 分类 from [公司出入库$a2:j] union all
select 品名,公司库存,"公司库存" as 分类 from [公司出入库$a2:j] union all
select 品名,数量,"柜台1销售"  as 分类 from [柜台1销售清单$a2:j] union all
select 品名,数量,"柜台2销售"  as 分类 from [柜台2销售清单$a2:j] union all
select 品名,数量,"柜台3销售"  as 分类 from [柜台3销售清单$a2:j] union all
select 品名,数量,"其他销售清单"  as 分类 from [其他销售清单$a2:j] union all
select 品名,[金  额],"柜台1销售金额"  as 分类 from [柜台1销售清单$a2:j] union all
select 品名,[金  额],"柜台2销售金额"  as 分类 from [柜台2销售清单$a2:j] union all
select 品名,[金  额],"柜台3销售金额"  as 分类 from [柜台3销售清单$a2:j] union all
select 品名,[金  额],"其他销售金额"  as 分类 from [其他销售清单$a2:j]  ) where 数量 is not null) a1 right join
(select distinct 类别,品名 from [公司出入库$a2:j] ) a2 on a1.品名=a2.品名

brdsz · 发表于 2010-10-5 23:50

谢谢suwenkai，我测试了一下，达到了我想要的效果。非常感谢suwenkai的鼎力支持！！！

wongme · 发表于 2010-10-12 19:06

抱歉，楼主。来信看晚了。惭愧。。

未命名图片.png

brdsz · 发表于 2010-10-12 21:21

不管如何，还是要谢谢你！！！

		自动登录	找回密码
密码			免费注册

[已解决] 再一次求助，有关SQL语句问题

回复 11楼 suwenkai 的帖子

回复 13楼 suwenkai 的帖子

回复 19楼 wongme 的帖子