求助，一道sql题

newnix123 发布于 2009-09-02 14:47， 704 次点击

表goods
-----------------------
|Id  |  count  |  type|
|1   |  50     |  进货|
|2   |  120    |  出货|
|3   |  30     |  进货|
|1   |  50     |  进货|
|2   |  150    |  进货|
|3   |  100    |  出货|
-----------------------
使用sql如何得到以下结果
--------------------------------
|Id  |  进货  |  出货  |  库存 |
|1   |  100   |  0     |  100  |
|2   |  150   |  120   |  30   |
|3   |  30    |  100   |  -70  |
--------------------------------
请大家帮忙啊

[ 本帖最后由 newnix123 于 2009-9-2 14:55 编辑 ]

4 回复

hgfeng19842009-09-02 20:33

select id,sum(JH) as '进货',sum(FH) as '出货',sum(JH-FH) as 库存 from (
select id,(case type when '进货' then count end) as JH,(case type when '出货' then count end) as FH from Goods
)m group by m.id

私人用户2009-09-02 22:31

select id,sum(case when type="进货" then count end) "进货"，
sum(case when type="出货" then count end) "出货"，
sum(jH-CH) "库存"
from goods
group by id

chinataozhi2009-09-10 12:48

WITH tmptable AS (SELECT     id, CASE WHEN type = N'进货' THEN count ELSE '0' END AS 进货,
                                 CASE WHEN type = N'出货' THEN count ELSE '0' END AS 出货
                  FROM       Table_2)
SELECT     id, SUM(进货) AS 进货, SUM(出货) AS 出货, SUM(进货) - SUM(出货) AS 库存
FROM       tmptable AS tmptable_1
GROUP BY id

zjzhouzhou2009-09-16 09:32

能实现此功能的SQL语句：
select id,sum(JH) as '进货',sum(CH) as '出货',sum(JH-CH) as 库存 from (
select id,(case type when '进货' then convert(int,count) else '0' end) as JH,(case type when '出货' then convert(int,count) else '0' end) as CH from Goods
) good group by good.id