请问这个sql怎么实现？

[hmzgz81]

我的表如下：
客户。。 金额 状态 。。。。
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' 无效

[pengdali]

if object_id('tempdb..#temp') is not null DROP TABLE #temp

SELECT IDENTITY (int, 1, 1) 序列, *
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.状态 = '退货')) table1

SELECT *, cast(100 * 销售余额 /(SELECT SUM(销售余额) FROM #temp) AS varchar(20)) + '%' 金额比例 FROM #temp

[realgz]

楼主 比如 有
select * from
(select * from table where ……) t
这个语句，就是使用了一个视图，其中 t是视图名(select * from table where ……) 是视图定义
(select * from table where ……) t 就是一个完整的引用了。

[hmzgz81]

如果我想用case语句来判断状态该怎么写？

[hmzgz81]

问题已经解决，现在想把代码减减肥，或者算法更简单些，比如用case 语句来判断状态，希望大家多提意见，我觉得我这代码太菜了！ 呵呵

[hmzgz81]

多谢realgz(realgz)，我还以为(select * from table where ……) t 的t 是把前面的(select * from table where ……) 给命名呢，怪不得不能在后面用t 来代替这个表！

[victorycyz]

检查你的左右括号有没有错的地方。

[hmzgz81]

没有！ 我检查好几遍了！

[wzh1215]

执行：
select * from #temp
看看有没有结果！

[hmzgz81]

to: wzh1215(四脚蛇)

有结果的！ 就是加上者句就出错！
cast(100 * 销售余额 /
(SELECT SUM(销售余额)
FROM table1) AS varchar(20)) + '%' 金额比例

[hmzgz81]

有结果的！ 就是加上这句就出错，又不知道怎么改！
///////////////////////////////////////////
cast(100 * 销售余额/(SELECT SUM(销售余额)
FROM table1) AS varchar(20)) + '%' 金额比例

[realgz]

视图不允许重复引用。。。
(SELECT SUM(销售余额)
FROM table1) AS varchar(20)) + '%' 金额比例这个 table1 要用完整的视图定义代替

[realgz]

我在http://expert.csdn.net/Expert/TopicView1.asp?id=2544041这个帖子里还郁闷了一下这个问题。

[wzh1215]

这就好办，把
SELECT *, cast(100 * 销售余额 /
(SELECT SUM(销售余额)
FROM table1) AS varchar(20)) + '%' 金额比例
FROM #temp
语句试着自己改一改了！

[hmzgz81]

to:realgz(realgz)

我学SQl没多久，对视图不是很了解，是不是这个一定要用视图解决！

[hmzgz81]

to :wzh1215(四脚蛇)

就是不知道该怎么改啊！

[hmzgz81]

怎么没人回啊！ 都吃饭去了！？！！？！？！？！

[evlon]

我先帮人UP一下，吃饭去!

[txlicenhe]

比较麻烦:
SELECT SUM(销售余额)
FROM table1
用如下代替:

select sum(销售余额)
from
(
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 table2

建议把table1的定义先写成一个视图