简单select问题

[cczeyun]

A表
id,name,type
1 a d
2 b d

b表
id cost1 cost2
1 23.5 10
1 22 12
1 33 10
2 23 15
2 45 16

结果:
如果id=1显示如下结果：
id name type cost
1 a d 总成本

这样的怎么写

[ming_Y]

create table a (id int,name varchar(12),type varchar(10))
insert into a
select '1','a','d' union all
select '2','b','d'

create table b (id int,cost1 float(5),cost2 float(5))
insert into b
select '1','23.5','10' union all
select '1','22','12' union all
select '1','33','10' union all
select '2','23','15' union all
select '2','45','16'

select * from b
drop table b

select a.id,a.name,a.type,d.totalcost from a,(select id,sum(cost) totalcost from (select id,cost=cost1+cost2 from b) c group by id) d
where a.id=d.id

[ming_Y]

select * from b
drop table b
去掉!

[silentwins]

--> 测试数据：@A
declare @A table([id] int,[name] varchar(100),[type] varchar(100))
insert @A
select 1,'a','d' union all
select 2,'b','d'
--> 测试数据：@B
declare @B table([id] int,[cost1] numeric(3,1),[cost2] int)
insert @B
select 1,23.5,10 union all
select 1,22,12 union all
select 1,33,10 union all
select 2,23,15 union all
select 2,45,16

select a.id,sum(cost1+cost2) as total from @A a inner join @B b on a.id = b.id where a.id = '1' group by a.id

/*
id          total                                    
----------- ---------------------------------------- 
1           110.5

(1 row(s) affected)
*/

[jayqean]

呵呵 楼上都写出来了```
我就不写了

[zhaodong1014]

楼上都正！

[小宏]

if object_id('[A]') is not null drop table [A]
go
create table [A]([id] int,[name] varchar(1),[type] varchar(1))
insert [A]
select 1,'a','d' union all
select 2,'b','d'
if object_id('[b]') is not null drop table [b]
go
create table [b]([id] int,[cost1] numeric(3,1),[cost2] int)
insert [b]
select 1,23.5,10 union all
select 1,22,12 union all
select 1,33,10 union all
select 2,23,15 union all
select 2,45,16

select a.* , b.cost
from A a inner join
(select id , sum(cost1 + cost2) as cost from b group by id)b
on a.id = b.id

[小宏]

1 a d 110.5
2 b d 99.0

[tree9]

楼上均正确

[panglin3688]

LS 都是对的

[战斗生活]

select id,name,type,(select sum(cost1+cost2))as cost
from a inner join b
where a.id=b.id

试一试 是不是这样更简单

[coolyayaya]

学习了。。

[cxmcxm]

select a.id,a.name,a.type,b.cost 
from a,
(select id,sum(cost1+cost2) cost from b where id=1 group by id) b
whre a.id=b.id

[影帆]

Create table #a(
id int,
[name] varchar(10),
[type] varchar(10)
)

insert into #a
select 1,'a','b' union all
select 2,'b','d'


create table #b(
id int,
cost1 float,
cost2 float
)

insert into #b
select 1,23.5,10 union all
select 1,22,12 union all
select 1,33,10 union all
select 2,23,15 union all
select 2,45,16 


--原表--
#a
1	a	b
2	b	d


#b
1	23.5	10
1	22	12
1	33	10
2	23	15
2	45	16



select a.id,a.[Name],a.[Type],b.cost
from (select id,sum(cost1*cost2) cost
	  from #b 
	  group by id) b left join #a a on b.id=a.id


--结果---

1	a	b	829
2	b	d	1065

[xuejiecn]

[Quote=引用 7 楼 guguda2008 的回复:]
LS全是正解，我就不复制了。
[/Quote]
我也支持。

[guguda2008]

[Quote=引用 34 楼 zsqsc 的回复:]
SQL codeCreatetable #a(
idint,[name]varchar(10),[type]varchar(10)
)insertinto #aselect1,'a','b'unionallselect2,'b','d'createtable #b(
idint,
cost1float,
cost2float
)insertinto #bselect1,23.5,10unionallselect1,22,12unionallselect1,33,10unionallselect2,23,15unionallselect2,45,16--原表--#a1 a b2 b d


#b123.51012212133102231524516select a.id,a.[Name],a.[Type],b.costfrom (select id,sum(cost1*cost2) costfrom #bgroupby id) bleftjoin #a aon b.id=a.id--结果---1 a b8292 b d1065
[/Quote]
这位仁兄真是执着，三天前的问题还能翻出来

[ahbbyu]

楼上都有了，不写了。

[随行的太阳]

学些了

[lihan6415151528]

select a.*,cost=sum(b.cost1+b.cost2)
from a,b where a.id=b.id
group by a.id,a.name,a.type 

[chen_ya_ping]

create table #temp
(
   id int not null,
cost1 float not null,
cost2 int not null,
)

select id,sum(cost1),sum(cost2)
from B
group by id
into #temp

select A.id,A.name,A.type sum(#temp.cost1+#temp.cost2)
from A inner join #temp on A.id=#temp.id

[feixianxxx]

我是来围观的