能不能用一个select直接搞定？

[cutedelphigirl]

一个表aaa
有字段：code,number,mark （产品代码，销售或退货数量，标记）
0001 10 出货
0001 20 出货
0001 5 退货
0002 8 出货
0002 3 退货
我想得出实际销售数量：
0001 25
0002 5

[superlionet]

select code,(select sum(number) from aaa where mark="出货'")-(select sum(number) from aaa where mark="退货'") as 销售数量 from aaa group by code

[superlionet]

声明: 这个语句我没试过.

[Frewin]

select code, sum(case mark when '出货' then number else -number)
from tablename
group by code
//ok

[Frewin]

少了个end !!!!
哈哈，
正确的如下：
select code, sum(case mark when '出货' then number else -number end)
from tablename
group by code

[cutedelphigirl]

我用ado+access

[zhoutian618]

select code,
[实际销售数量]=sum(case when mark='退货' then -isnull(number, 0)
else isnull(number, 0)
end
)
from [一个表aaa]
group by code

[zhoutian618]

MSSQL代码如下：
select code,
[实际销售数量]=sum(case when mark='退货' then -isnull(number, 0)
else isnull(number, 0)
end
)
from [一个表aaa]
group by code

[hiflower]

SELECT a.code, (a.suma-b.sumb) AS [number]
FROM [SELECT aaa.code, Sum(aaa.number) AS suma
FROM aaa
WHERE Mark='出货'
GROUP BY aaa.code]. AS a LEFT JOIN [select aaa.code,sum(aaa.number) as sumb
from aaa
where Mark='退货'
GROUP by aaa.code]. AS b ON a.code = b.code;

[hmily1688]

嘻嘻，答案出来了，我是来学习的

[noil0125]

sql server
SELECT a.code, SUM(a.number - b.number) AS SUM
FROM T a INNER JOIN
t b ON a.code = b.code
WHERE a.mark = '进货' AND b.mark = '退货'
GROUP BY a.code

[Northwindrocker]

select code,(select sum(number) from aaa where mark='出货')-(select sum(number) from aaa where mark='退货') as 销售数量 from aaa group by code

推荐这个！简洁，其他的保证非空是库结构的事情！

[hongjg]

学习

[说好不能打脸]

是我，就不会用SELECT。我用经典算法。

[popmailzjw]

要我就把出的数据用负数表示

[haoguozhong]

不管用什么方法，解决问题时关键！

[csntgao]

zhoutian618(秦失其鹿，天下共逐，唯胜者得鹿而割之) !

你是个聪明的人，可惜这帮人连好歹都分不清楚的！

[karach]

没有意义拉

[redcola]

mark

[mib3000]

up

[vchoushen6]

select code,sum(sumber) as num from (
select code,number from aaa where mark='出货'
union all
select code,-number as number from aaa where mark='退货')
group by code