联合查询

[yueguangkai001]

有这样的一张表 里面有这样的数据

GOODNAME NUM TYPE
电脑 10 进
手提 20 进
音响 15 进
电脑 15 出
手提 10 出
音响 5 出
电脑 20 进

我要查出来
名称 进 出 库存
电脑 30 15 15
手提 20 10 10
音响 15 5 10

请高手帮帮忙，实在是忘记怎么搞了

[yueguangkai001]

高手，学习了，多给分你们

[yueguangkai001]

貌似 不能多给分，只能这样结了

[sgtzzc]

select GOODNAME 名称,
sum(case when type='进' then NUM else 0 end) as 进,
sum(case when type='出' then NUM else 0 end) as 出,
sum(case when type='进' then NUM else -NUM end) as 库存
from tb
group by GOODNAME

[htl258_Tony]

select GOODNAME,
  sum(case type when '进' then num else 0 end) 进,
  sum(case type when '出' then num else 0 end) 出,  
  sum(case type when '出' then -num else num end)
from tb
group by GOODNAME

[sgtzzc]

if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([GOODNAME] varchar(4),[NUM] int,[TYPE] varchar(2))
insert [tb]
select '电脑',10,'进' union all
select '手提',20,'进' union all
select '音响',15,'进' union all
select '电脑',15,'出' union all
select '手提',10,'出' union all
select '音响',5,'出' union all
select '电脑',20,'进'
 
---查询
select GOODNAME 名称,
sum(case when type='进' then NUM else 0 end) as 进,
sum(case when type='出' then NUM else 0 end) as 出,
sum(case when type='进' then NUM else -NUM end) as 库存
from tb
group by GOODNAME

/**
名称   进           出           库存
---- ----------- ----------- -----------
电脑   30          15          15
手提   20          10          10
音响   15          5           10

(3 行受影响)
**/

[htl258_Tony]

--------------------------------------------------------------------------
--  Author : htl258(Tony)
--  Date   : 2010-04-05 12:03:26
--  Version:Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86) 
--          Jul  9 2008 14:43:34 
--          Copyright (c) 1988-2008 Microsoft Corporation
--          Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
--------------------------------------------------------------------------
--> 生成测试数据表:tb

IF NOT OBJECT_ID('[tb]') IS NULL
	DROP TABLE [tb]
GO
CREATE TABLE [tb]([GOODNAME] NVARCHAR(10),[NUM] INT,[TYPE] NVARCHAR(10))
INSERT [tb]
SELECT N'电脑',10,N'进' UNION ALL
SELECT N'手提',20,N'进' UNION ALL
SELECT N'音响',15,N'进' UNION ALL
SELECT N'电脑',15,N'出' UNION ALL
SELECT N'手提',10,N'出' UNION ALL
SELECT N'音响',5,N'出' UNION ALL
SELECT N'电脑',20,N'进'
GO
--SELECT * FROM [tb]

-->SQL查询如下:
select GOODNAME,
  sum(case type when '进' then num else 0 end) 进,
  sum(case type when '出' then num else 0 end) 出,  
  sum(case type when '出' then -num else num end) 库存
from tb
group by GOODNAME
/*
GOODNAME   进           出           库存
---------- ----------- ----------- -----------
电脑         30          15          15
手提         20          10          10
音响         15          5           10

(3 行受影响)
*/

[老黎]

学习......