请问这个sql怎么实现?
我的表如下:
客户。。 金额 状态 。。。。
a 100 销售
a 200 销售
a 200 退货
b 300 销售
b 100 退货
现在要实现
序号(自增量) 客户 销售金额 退货金额 销售余额 比列
1 a 300 200 100 33.33%
2 b 300 100 200 66.67
下面是我写的 大家帮我看看!!
DROP TABLE #temp SELECT IDENTITY (int, 1, 1) AS 序列, *
INTO #temp
FROM (SELECT b.客户简码 AS 客户简码, a.客户简称 AS 客户简称,
a.金额 AS 销售金额,
(SELECT c.金额
FROM 销售单 c
WHERE (c.状态 = '退货' AND c.销售ID = a.销售ID))
AS 退货金额, (isnull(a.金额, 0) - isnull((SELECT c.金额 AS 撤消金额
FROM 销售单 c
WHERE (c.状态 = '退货' AND c.销售ID = a.销售ID)), 0))
AS 销售余额
FROM 销售单 a LEFT OUTER JOIN
客户 b ON a.客户ID = b.客户ID
WHERE (a.状态 = '销售')
UNION
SELECT b.客户简码 AS 客户简码, a.客户简称 AS 客户简称,
(SELECT c.金额
FROM 销售单 c
WHERE (c.状态 = '销售' AND c.销售ID = a.销售ID))
AS 销售金额, a.金额 AS 退货金额, (isnull
((SELECT c.金额
FROM 销售单 c
WHERE (c.状态 = '销售' AND c.销售ID = a.销售ID)), 0)
- isnull(a.金额, 0)) AS 销售余额
FROM 销售单 a LEFT OUTER JOIN
客户 b ON a.客户ID = b.客户ID
WHERE (a.状态 = '退货')) AS table1
SELECT *, cast(100 * 销售余额 /
(SELECT SUM(销售余额)
FROM table1) AS varchar(20)) + '%' 金额比例
FROM #temp
提示错误为:
///////对象名 'table1' 无效