select
max(a.gysbh) gysbh,max(a.gysname) gysname,sum(c.sl*c.dj)-sum(d.ytmoney) cmoney
from a a,b b,c c,d d where a.gysbh=b.gysybh and b.gjhdh=c.gjhdh and a.gysbh=d.gysbh
group by a.gysbh
能把以上语句的结果集贴几条给我看看?
max(a.gysbh) gysbh,max(a.gysname) gysname,sum(c.sl*c.dj)-sum(d.ytmoney) cmoney
from a a,b b,c c,d d where a.gysbh=b.gysybh and b.gjhdh=c.gjhdh and a.gysbh=d.gysbh
group by a.gysbh
能把以上语句的结果集贴几条给我看看?
(select
max(a.gysbh) gysbh,max(a.gysname) gysname,sum(c.sl*c.dj) cmoney
from a a,b b,c c where a.gysbh=b.gysbh and b.jhdh=c.jhdh group by a.gysbh) e,d bwhere e.gysbh=b.gysbh group by e.gysbh
from (
select jhdb.jhdh,jhmxb.jhdh,jhdb.gysbh,sum(jhmxb.sl*jhmxb.dj) as cmoney
from jhdb,jhmxb
where jhdb.jhdh=jhmxb.jhdh
)as t1,gysb,
(select gysbh,sum(ytmoney) as ytmoney
from thdb
) as t2
where gysb.gysbh=t1.gysbh and gysb.gysbh=t2.gysbh//出错Server: Msg 8156, Level 16, State 1, Line 1
The column 'jhdh' was specified multiple times for 't1'.
from (
select jhdb.jhdh,jhmxb.jhdh,jhdb.gysbh,sum(jhmxb.sl*jhmxb.dj) as cmoney
from jhdb,jhmxb //这里还少一个group by jhdh
where jhdb.jhdh=jhmxb.jhdh原来这句是这样的。select jhdb.JHDH,jhdb.gysbh,Sum(C.money) AS Cmoney,from jhdb B,
(select jhdh,sum(dj*sl) as money from jhmxb group by jhdh) C,
where jhdb.jhdh=jhmxb.jhdh
gysbh gysname (gysbh主键)
1 张
2 李表jhdb
gysbh jhdh (gysbh是表A的外键, jhdh是表B的主键,)
1 2002-01-01
2 2002-01-05
1 2002-01-02
1 2002-01-03表jhmxb
jhdh sl dj (jhdh是表B的外键)
2002-01-01 1 2
2002-01-01 3 5
2002-01-05 1 10
2002-01-02 1 10
2002-01-03 3 10表thdb
gysbh ytmoney THDH (gysb的外键,THDH是主键)
1 30 001
2 40 002最终结果表E是这样的.gysbh gysname Cmoney
1 张 57-表D.ytmoney(30)
2 李 10-40
gysbh gysname Cmoney
1 张 27
2 李 -30
(select
max(a.gysbh) gysbh,max(a.gysname) gysname,sum(c.sl*c.dj) cmoney
from gysb a,jhdb b,jhmxb c where a.gysbh=b.gysbh and b.jhdh=c.jhdh group by a.gysbh) e,thdb bwhere e.gysbh=b.gysbh group by e.gysbh结果是这样的
----------- ---------------------------------------- -----------------------------------------------------
1 张 2558.4019999999996(1 row(s) affected)a-->gysb, b-->jhdb, c-->jhmxb, d-->thdb
1 张 27 //这值是正确的。 但为什么显示一条记录。(1 row(s) affected)
from a left outer join
(select b.gysbh,sum(c.sl*c.dj) as cmoney from b,c where b.jhdh=c.jhdh GROUP BY b.gysbh) as a1
on a.gysbh=a1.gysbh left outer join
(select gysbh,sum(ytmoney) as ytmoney from d GROUP BY gysbh) as a2
on a.gysbh=a2.gysbh
dbo.z_a.gysname
FROM dbo.z_b INNER JOIN
dbo.z_c ON dbo.z_b.jhdh = dbo.z_c.jhdh INNER JOIN
dbo.z_a ON dbo.z_b.gysbh = dbo.z_a.gysbh INNER JOIN
dbo.z_d ON dbo.z_a.gysbh = dbo.z_d.gysbh AND
dbo.z_b.gysbh = dbo.z_d.gysbh
GROUP BY dbo.z_d.gysbh, dbo.z_d.ytmoney, dbo.z_a.gysname
z_a,z_b,z_c,z_d分别顺序对应你上面的四个表,执行以后可以显示出表E的效果
from (
select jhdb.gysbh,sum(jhmxb.sl*jhmxb.dj) as cmoney
from jhdb,jhmxb
where jhdb.jhdh=jhmxb.jhdh group by jhdb.gysbh
)as t1,gysb,
(select gysbh,sum(ytmoney) as ytmoney
from thdb group by gysbh
) as t2
where gysb.gysbh=t1.gysbh and gysb.gysbh=t2.gysbh
inner join z_c on z_b.jhdh=z_c.jhdh inner join z_a on z_b.gysbh=z_a.gysbh inner
join z_d on z_a.gysbh=z_d.gysbh and z_b.gysbh=z_d.gysbh group by z_d.gysbh,
z_d.ytmoney,z_a.gysname
from gysb left join
(
select jhdb.gysbh,sum(jhmxb.sl*jhmxb.dj) as cmoney
from jhdb,jhmxb
where jhdb.jhdh=jhmxb.jhdh
and jhdb.jdate='2002-05-02'
GROUP BY jhdb.gysbh
) as t1
on gysb.gysbh=t1.gysbh
left join
(
select gysbh,sum(ytmoney) as ytmoney
from thdb
where tdate='2002-05-2'
GROUP BY gysbh
) as t2
on gysb.gysbh=t2.gysbh
where gysb.gysbh=1再开一贴发分
select a.gysbh,a.gysName,(KK.Money-WW.Money) as CMoney
from a,
(select a.gysbh,Sum(c.sl*c.dj) as Money
from a inner join b on (a.gysbh=b.gysbh) inner join c
on (c.jhch=b.jhch) group by a.gysbh ) KK,
(Select a.gysbh,Sum(d.YtMoney) as Money
from a inner join d on (a.gysbh=d.gysbh) Group by a.gysbh) WW
where a.gysbh=KK.gysbh and a.gysbh=WW.gysbh