这样的SQL语句怎么创建（看里面，好复杂的）(50分)

S

SevenOrient

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #1

1.Select TableA.FieldA,Sum(TableB.FieldA) as 总和1,Sum(TableB.FieldA) as 总和2 from TableA,TableB(不全)
2.其中，TableB.FieldA, 的两个Sum值是满足一定条件的：
where TableB.FieldX=0，TableB.FieldX=1
就是说同一个字段根据不同条件得出两个Sum值构成一个SQL语句，怎么构建？

 

S

Shuzi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #2

用Group By

 

S

SevenOrient

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #3

那么用Group By 怎么写？

 

S

Shuzi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #4

不知道两张表的具体结构,不太好写的
如果B表只按FieldX分组,可以写为
select sum(fieldA) from TableB group by FieldX
然后将它作为子查询与A表关联上就可以了

－－需继续讨论请提供两张表的结构

 

S

SevenOrient

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #5

因我表结构很复杂，我清理出我需要的给你了
//>然后将它作为子查询与A表关联上就可以了
这句怎么实现？
另外
select sum(fieldA) from TableB group by FieldX
得到的是两条记录（我FieldX只有两种情况），这两条记录分别是我
Select TableA.FieldA,Sum(TableB.FieldA) as 总和1,Sum(TableB.FieldA) as 总和2 from TableA,TableB(不全)
中的总和1和总和2
和TableA关联的话，我要形成一条记录，如何做？

 

Y

ysai

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #6

select suma,sumb from
(select sum(fielda) as suma from tableb where fieldx=1),
(select sum(fieldb) as sumb from tableb where fieldx=2)
..........

 

S

Shuzi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #7

结合ysai的方法可以写如下SQL：
Select A.FieldA,
B.suma 总和1,
C.sumb 总和2
from TableA A,
(select sum(fielda) as suma from tableb where fieldx=1) B,
(select sum(fieldb) as sumb from tableb where fieldx=2) C
需要说明的是，A表和B表应该有关联字段，B表的关联字段在子查询中提出来，
具体的视你的实际情况而定。子查询B可能会写成
select 关键字段,sum(fielda) as suma
from tableb
where fieldx=1 Group By 关键字段
子查询B类似.那么在上面的语句最可以会加上:
where A.关键字段=B.关键字段(+) and A.关键字段=C.关键字段(+)

另外，上面的SQL语法适合Oracle，根据你的数据库，可能需要作调整

 

S

SevenOrient

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #8

Select * from (Select sum(FieldA) as sumA from Ticket where FieldX=0)
这句SQL语句怎么执行起来错误？

 

S

Shuzi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #9

你是什么数据库？不支持子查询吗？如果是可能得考虑编程实现了

 

S

SevenOrient

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #10

Sybase
应该支持吧

 

S

Shuzi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #11

查查它的SQL语法吧

 

S

SevenOrient

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #12

查不到～
晕
不会吧
～

 

S

soul

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #13

Select (Select Sum(TableA.FieldA) as 总和1 from TableA where TableA.FieldX=0),
(Select Sum(TableB.FieldA) as 总和1 from TableB where TableB.FieldX=1)

 

L

luojf999

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-19

• #14

Select A.FieldA,B.suma 总和1, C.sumb 总和2
from TableA A,
(select sum(fieldA) as suma from tableb where fieldX=0) B,
(select sum(fieldA) as sumb from tableb where fieldX=1) C
where A.关键字段=B.关键字段(+) and A.关键字段=C.关键字段(+)

 

F

fangchangjiang

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-20

• #15

up

 

S

SevenOrient

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-11-22

• #16

多人接受答案了。