有两个表，VW_QTLSMX3表（订单明细表）有字段RQ(日期)、CKJ（原价）、SL（数量）、JE1(卖出价格)、CKDM（仓库代码），CANGKU表有CKDM（仓库代码），CKMC（仓库名称），QYDM（区域代码），要求按照区域取出最近三天每天的原价总额、每天销售总额、每天销售数量。
SELECT
CKDM,
CKMC,
SUM (SL) AS SL,
SUM (JE) AS JE,
SUM (CKJ) AS CKJ
FROM
(
SELECT
CANGKU.CKDM AS CKDM,
CANGKU.CKMC AS CKMC,
SUM (VW_QTLSMX3.SL) AS SL,
SUM (VW_QTLSMX3.JE1) AS JE,
SUM (VW_QTLSMX3.CKJ * SL) AS CKJ
FROM
VW_QTLSMX3,
CANGKU
WHERE
VW_QTLSMX3.CKDM = CANGKU.CKDM
AND VW_QTLSMX3.CKDM IN (
SELECT
CKDM
FROM
CANGKU
WHERE
QYDM = '100'
)
AND RQ BETWEEN '2014-9-24' and '2014-9-26'
AND VW_QTLSMX3.SPDM IN (
SELECT
SPDM
FROM
SHANGPIN
WHERE
BYZD4 IN ('000', '001')
)
GROUP BY
VW_QTLSMX3.DYDM,
CANGKU.CKDM,
CANGKU.CKMC
) AS HJ
GROUP BY
HJ.CKDM,
HJ.CKMC
ORDER BY
JE DESC
这个是取指定时间内所有数量、金额，要的结果如下图
谢谢各位大神了
------解决思路----------------------
统计各个区域最近三天每天的原价总额、销售总额和销售数量，然后对这个结果集用row_number()进行分组按时间排序，这样可以形成下面的数据格式
区域1 原价总额 1
区域1 原价总额 2
区域1 销售总额 3
区域1 销售总额 1
区域1 销售总额 2
区域1 销售总额 3
……
然后进行行列转换即可
------解决思路----------------------
在你的SEL里面价格时间啊。
--元数据
ckdm ckmc sl je ckj rq
----------- ---------- ----------- ----------- ----------- ----------
810 南京长发 16 10878 12754 2014-09-25
803 南京山百 15 10365 12545 2014-09-25
810 南京长发 32 9586 27078 2014-09-26
803 南京山白 24 15284 9029 2014-09-26
(4 行受影响)
with cte as
(select *,ROW_NUMBER()over(partition by CKDM order by rq)
as n from test),
t1 as
(select ckdm,ckmc,[1]as 昨天销售总金额,[2] as 今天销售总金额 from
(select ckdm,ckmc,je,n from cte)as t
pivot (max(je) for n in([1],[2],[3])) as p),
t2 as
(select ckdm,[1]as 昨天销售数量,[2] as 今天销售数量 from
(select ckdm,sl,n from cte)as t
pivot (max(sl) for n in([1],[2],[3])) as p),
t3 as
(select ckdm,[1]as 昨天销售原价,[2] as 今天销售原价 from
(select ckdm,ckj,n from cte)as t
pivot (max(ckj) for n in([1],[2],[3])) as p)
select t1.*,t2.今天销售数量,t2.昨天销售数量,t3.今天销售原价,
t3.昨天销售原价 from t1 join t2 on t1.ckdm=t2.ckdm
join t3 on t1.ckdm=t3.ckdm