古诗词sql

1.求汇总sql语句

select 科目名称，sum（金额） from 表 group by 科目名称

我是跟据你给的数据做个例子。

create table test1(

code varchar(30),

name varchar(50),

upcode varchar(30)

)

create table test2(

code varchar(30),

name varchar(50),

cost money

)

select * from test1

select * from test2

select c.name,upcode,sum(cost) from test2 a

inner join test1 b on a.code=b.code

inner join( select name,code from test1 where upcode=(select code from test1 where name='a'))c on c.code=b.upcode

group by b.upcode,c.name

得到的结果：（）

2.sql语句 查询 类别下所有子类

--假如表名是 tablea

select *

from tablea b

where ID between 2 and 6

union all

select *

from tablea a

where exists

(

select *

from tablea b

where b.ID between 2 and 6

and a.ID=b.FID

)

--第二办法是使用递归

with

temp

as

(

select *

from tablea

where ID between 2 and 6

union all

select b.*

from temp

inner join tablea b on b.ID=temp.ID

)

select *

from temp

3.求一个一对多汇总的sql句子

--#1.根据业务逻辑，可以用inner join来提高查询效率

--#2.如果是sql server 2005及以上版本，用cross apply(outer apply)，相当于inner join(left join)

SELECT A.*, B.*, C.* FROM

Class A

CROSS APPLY

(SELECT StudentID FROM Relation WHERE ClassID = A.id) B

CROSS APPLY

(SELECT totalMoney = SUM(Money) FROM Student WHERE id = B.StudentID) C

WHERE A.[Name] = '三年一班'