SQL交叉200分(100)

W

wisdomphg

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-07

• #1

表Aaa bb cc dd ds123 a 5 4 ds123 a 2 5ds234 b 3 4 ds234 b 2 3表B EE FF GG HHDS123 A 7 8DS123 A 1 2DS234 B 8 9DS234 B 2 3 查出结果如下aa bb cc dd GG HHds123 a 7 9 8 10ds234 b 5 7 10 12

 

李

李翔鹏

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-07

• #2

来自：forgot2002, 时间：2009-5-7 17:00:13, ID：3957291Select aa,max(bb) as bb,sum(cc) as cc,sum(dd) as dd,max(ee) as ee,max(ff) as ff from Table1 group by aa

 

D

de410

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-07

• #3

SELECT Table1.aa, MAX(Table1.bb) AS bb, SUM(Table1.cc) / 2 AS Expr1, SUM(Table1.dd) / 2 AS Expr2, SUM(Table2.GG) / 2 AS Expr3, SUM(Table2.HH) / 2 AS Expr4FROM Table1 INNER JOIN Table2 ON Table1.aa = Table2.EEGROUP BY Table1.aa

 

Z

znxia

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-07

• #4

表的关联关系都不说。Select aa,bb,sum(cc) as cc,sum(dd) as dd,max(ee) as ee,max(ff) as ff from 表A, 表BWhere 表A.aa=表B.EE AND 表A.BB=表B.FFgroup by aa, bb也是出现这个结果

 

D

de410

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-07

• #5

SELECT Table1.aa, MAX(Table1.bb) AS bb, SUM(Table1.cc) / 2 AS cc, SUM(Table1.dd) / 2 AS dd, SUM(Table2.GG) / 2 AS GG, SUM(Table2.HH) / 2 AS HHFROM Table1 INNER JOIN Table2 ON Table1.aa = Table2.EEGROUP BY Table1.aa

 

W

wisdomphg

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-08

• #6

de410 您好!如果表Aaa bb cc dd ds123 a 5 4 ds123 a 2 5ds234 b 3 4 ds234 b 2 3dd123 c 2 6表A多了一条记录还能行到以下结果吗?查出结果如下aa bb cc dd GG HHds123 a 7 9 8 10ds234 b 5 7 10 12dd123 c 2 6

 

D

de410

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-08

• #7

SELECT Table1.aa, Table1.bb, SUM(Table1.cc) / 4 AS cc, SUM(Table1.dd) / 4 AS ddFROM Table1 CROSS JOIN Table2GROUP BY Table1.aa, Table1.bb

 

F

ftlifeng

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-08

• #8

select a.aa,a.bb,a.cc,a.dd,b.gg,b.hhfrom (select aa,max(bb) bb,sum(cc) cc,sum(dd) dd from 表A group by aa) as a,(select EE,sum(GG) gg,sum(HH) hh from 表B group by EE) as bwhere a.aa*=b.EE

 

D

de410

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-08

• #9

楼上的正解，通过测试~~[]学习了~~

 

W

wisdomphg

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2009-05-12

• #10

ftlifeng您好查出结果如下aa bb cc dd GG HHds123 a 7 9 8 10ds234 b 5 7 10 12dd123 c 2 6字段aa和bb要同时相同