表1:Budgeting_Detail_Infos
id type gasnumber
1 氧气 40
2 氧气 60
3 氯气 20
4 空气 45
5 氯气 30表2:Base_para
id type
1 氧气
2 氯气所有的气体都是通过Base_para这个表维护的,通过type字段两表关联,现在要求取出Base_para表里的type字段作为列,形成以下的查询结果(比如Base_para中没有空气这个数据就不显示):
type gasnumber
氧气 100
氯气 50
id type gasnumber
1 氧气 40
2 氧气 60
3 氯气 20
4 空气 45
5 氯气 30表2:Base_para
id type
1 氧气
2 氯气所有的气体都是通过Base_para这个表维护的,通过type字段两表关联,现在要求取出Base_para表里的type字段作为列,形成以下的查询结果(比如Base_para中没有空气这个数据就不显示):
type gasnumber
氧气 100
氯气 50
b.Base_para ,
sum(t1.gasnumber) as gasnumber
from
a , b
where
a.type = b.type
group by
b.Base_para
a.gasnumber
from Base_para b,
(select type,sum(gasnumber)gasnumber from Budgeting_Detail_Infos group by type)a
where b.type=a.type
insert into t1 values(1 , '氧气' , 40)
insert into t1 values(2 , '氧气' , 60)
insert into t1 values(3 , '氯气' , 20)
insert into t1 values(4 , '空气' , 45)
insert into t1 values(5 , '氯气' , 30)
create table t2(id int, type varchar(10))
insert into t2 values(1 , '氧气')
insert into t2 values(2 , '氯气')
goselect t2.type , sum(t1.gasnumber) gasnumber from t2 , t1 where t2.type = t1.type group by t2.type
drop table t1 , t2 /*
type gasnumber
---------- -----------
氯气 50
氧气 100(所影响的行数为 2 行)
*/
-- Author :fredrickhu(我是小F,向高手学习)
-- Date :2009-12-23 11:36:00
-- Version:
-- Microsoft SQL Server 2005 - 9.00.4035.00 (Intel X86)
-- Nov 24 2008 13:01:59
-- Copyright (c) 1988-2005 Microsoft Corporation
-- Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据:[Budgeting_Detail_Infos]
if object_id('[Budgeting_Detail_Infos]') is not null drop table [Budgeting_Detail_Infos]
go
create table [Budgeting_Detail_Infos]([id] int,[type] varchar(4),[gasnumber] int)
insert [Budgeting_Detail_Infos]
select 1,'氧气',40 union all
select 2,'氧气',60 union all
select 3,'氯气',20 union all
select 4,'空气',45 union all
select 5,'氯气',30
--> 测试数据:[Base_para]
if object_id('[Base_para]') is not null drop table [Base_para]
go
create table [Base_para]([id] int,[type] varchar(4))
insert [Base_para]
select 1,'氧气' union all
select 2,'氯气'
--------------开始查询--------------------------
select
b.[type],
sum(a.gasnumber) as gasnumber
from
[Budgeting_Detail_Infos] a,[Base_para] b
where
a.type = b.type
group by
b.[type]
----------------结果----------------------------
/* type gasnumber
---- -----------
氯气 50
氧气 100(2 行受影响)
*/
select 1, '氧气',40 union all
select 2 ,'氧气',60 union all
select 3 ,'氯气',20 union all
select 4,'空气',45 union all
select 5,'氯气',30
create table #tb2(id int,type varchar(6))insert #tb2
select 1, '氧气' union all
select 2 ,'氯气'一种:
select a.type,sum(case when b.type='氧气' then gasnumber end) as 氧气,
sum(case when b.type='氯气' then gasnumber end) as 氯气
from #tb1 a,#tb2 b
where a.type=b.type
group by a.type另外一种
select a.type,sum(gasnumber) gasnumber from #tb1 a,#tb2 b
where a.type=b.type
group by a.type
from Budgeting_Detail_Infos a iner join Base_para b on a.type=b.type
group by a.type