超难度sql语句
表A
gysbh gysname (gysbh主键)
1 张
2 李
表B
gysbh jhdh (gysbh是表A的外键, jhdh是表B的主键,)
1 2002-01-01
2 2002-01-05
1 2002-01-02
1 2002-01-03
表C
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
表D
gysbh ytmoney THDH (gysb的外键,THDH是主键)
1 30 001
2 40 002
最终结果表E是这样的.
gysbh gysname Cmoney
1 张 57-表D.ytmoney(30)
2 李 10-40
解决问题者可得200分， 参与有分。 我再开一贴。 谢谢各位.
问题点数：100、回复次数：28Top
1 楼wangfeixue（）回复于 2002-08-26 13:30:26 得分 0
cmoney//这个字段是一个 as Cmoney的字段
select B.JHDH,B.gysbh,Sum(C.money) AS Cmoney,A.gysname from jhdb B,
(select jhdh,sum(dj*sl) as money from jhmxb group by jhdh) C,
where B.jhdh=C.jhdh
GROUP BY B.GYSBH
它的值是表 Sum(C.money) as Cmoney-D.ytmoney 来的.
这条语句我写的有问题，Top
2 楼windindance（风舞轻扬·白首为功名）回复于 2002-08-26 13:37:29 得分 0
select
gysbh ,
gysname = (select gysname from a where a.gysbh = d.gysbh),
Cmoney = (select sum(dj*sl) from c where jhdh in (select jhdh from b where b.gysbh = d.gysbh and b.jhdh = c.jhdh )
- ytmoney
from dTop
3 楼wangfeixue（）回复于 2002-08-26 13:41:53 得分 0
不对呀.- ytmoney//这时出错Top
4 楼wangfeixue（）回复于 2002-08-26 13:56:05 得分 0
这问题是有点难，分不够再加Top
5 楼wangfeixue（）回复于 2002-08-26 14:09:21 得分 0
真的没人会吗？ 还是大家太忙了Top
6 楼Yang_（扬帆破浪）回复于 2002-08-26 14:20:22 得分 0
select a.gysbh,a.gysname,t1.cmoney-d.ytmoney as Cmoney
from (
select b.gysbh,sum(c.sl*c.dj) as cmoney
from b,c
where b.jhdh=c.jhdh
) as t1,a,d
where a.gysbh=t1.gysbh
and a.gysbh=d.gysbh
or (d表一个gysbh有多条记录的情况):
select a.gysbh,a.gysname,t1.cmoney-t2.ytmoney as Cmoney
from (
select b.gysbh,sum(c.sl*c.dj) as cmoney
from b,c
where b.jhdh=c.jhdh
) as t1,a,
(
select gysbh,sum(ytmoney) as ytmoney
from d
) as t2
where a.gysbh=t1.gysbh
and a.gysbh=t2.gysbh
Top
7 楼wangfeixue（）回复于 2002-08-26 14:24:44 得分 0
我试试。 谢谢你了Top
8 楼wangfeixue（）回复于 2002-08-26 14:31:46 得分 0
select a.gysbh,a.gysname,t1.cmoney-t2.ytmoney as Cmoney
from (
select b.gysbh,sum(c.sl*c.dj) as cmoney
from b,c
where b.jhdh=c.jhdh
) as t1,a,
(
select gysbh,sum(ytmoney) as ytmoney
from d
) as t2
where a.gysbh=t1.gysbh
and a.gysbh=t2.gysbh
我用这段代码代入试了。
Server: Msg 8118, Level 16, State 1, Line 1
Column 'b.gysbh' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'd.gysbh' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Top
9 楼wangfeixue（）回复于 2002-08-26 14:33:39 得分 0
to Yang_(扬帆破浪) 我要的就是你的这个结果。 你理解我的意思。
但我把实际参数代入进去 还出错了。
Column 'jhdb.gysbh' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Server: Msg 8118, Level 16, State 1, Line 1
Column 'thdb.gysbh' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.
Top
10 楼free007（魔蝎）回复于 2002-08-26 14:38:42 得分 0
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
能把以上语句的结果集贴几条给我看看？
Top
11 楼wangfeixue（）回复于 2002-08-26 14:41:04 得分 0
select a.gysbh,a.gysname,t1.cmoney-t2.ytmoney as Cmoney
from (
select b.gysbh,sum(c.sl*c.dj) as cmoney
from b,c //在这里还应该另一条group by jhdh吧,
where b.jhdh=c.jhdh
) as t1,a,
(
select gysbh,sum(ytmoney) as ytmoney
from d
) as t2
where a.gysbh=t1.gysbh
and a.gysbh=t2.gysbh
Top
12 楼wangfeixue（）回复于 2002-08-26 14:44:42 得分 0
我是这样写的。上面的A表->gysb, b表jhdb, c表jhmxb, d表thdb
select gysb.gysbh,gysb.gysname,t1.cmoney-t2.ytmoney as cmoney
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'.
Top
13 楼wanwangzhiwang（万王之王）回复于 2002-08-26 14:47:56 得分 0
少了group byTop
14 楼free007（魔蝎）回复于 2002-08-26 15:06:27 得分 0
select max(e.gysbh),max(e.gysname),sum(e.cmoney)-sum(b.ytmoney) from
(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 b
where e.gysbh=b.gysbh group by e.gysbh
Top
15 楼My_first（小@_@小）回复于 2002-08-26 15:07:35 得分 0
应该怎样改呢？ 谢谢Top
16 楼Yang_（扬帆破浪）回复于 2002-08-26 15:25:02 得分 0
是少了GROUP BY
select gysb.gysbh,gysb.gysname,t1.cmoney-t2.ytmoney as cmoney
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
Top
17 楼huft（flycat）回复于 2002-08-26 15:27:49 得分 0
57从那来得？建议你先建立视图，在进行操作Top
18 楼Yang_（扬帆破浪）回复于 2002-08-26 16:07:20 得分 100
select gysb.gysbh,gysb.gysname,isnull(t1.cmoney,0)-isnull(t2.ytmoney,0) as cmoney
from gysb left join
(
select jhdb.gysbh,sum(jhmxb.sl*jhmxb.dj) as cmoney
from jhdb,jhmxb
where jhdb.jhdh=jhmxb.jhdh
GROUP BY jhdb.gysbh
) as t1
on gysb.gysbh=t1.gysbh
left join
(
select gysbh,sum(ytmoney) as ytmoney
from thdb
GROUP BY gysbh
) as t2
on gysb.gysbh=t2.gysbh
Top
19 楼zou5655（周围）回复于 2002-08-26 16:16:22 得分 0
你把他写成中文，高手有点多哟！！！Top
20 楼My_first（小@_@小）回复于 2002-08-26 16:18:19 得分 0
to Yang_(扬帆破浪) 在我的jhdb和 thdb里分别有一个jdate时间字段。
var
s:string;
begin
s:='2002-05-02'
我想查询时间jhdb.jdate =s and thdb.jdate=s
select gysb.gysbh,gysb.gysname,isnull(t1.cmoney,0)-isnull(t2.ytmoney,0) as cmoney
from gysb left join
(
select jhdb.gysbh,sum(jhmxb.sl*jhmxb.dj) as cmoney
from jhdb,jhmxb
where jhdb.jhdh=jhmxb.jhdh
GROUP BY jhdb.gysbh
) as t1
on gysb.gysbh=t1.gysbh and jdate='2002-05-02'
left join
(
select gysbh,sum(ytmoney) as ytmoney
from thdb
GROUP BY gysbh
) as t2
on gysb.gysbh=t2.gysbh and jdate='2002-05-2'
这样写行不通。
Top
21 楼Yang_（扬帆破浪）回复于 2002-08-26 16:32:01 得分 0
select gysb.gysbh,gysb.gysname,isnull(t1.cmoney,0)-isnull(t2.ytmoney,0) as cmoney
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 jdate='2002-05-2'
GROUP BY gysbh
) as t2
on gysb.gysbh=t2.gysbh
Top
22 楼Yang_（扬帆破浪）回复于 2002-08-26 16:36:22 得分 0
>>如果我在这上面加一个条件， 我查询供应商gys=1 在 jhdb（进货单表)
>>thdb(退货单表), 的总额 那上面
select gysb.gysbh,gysb.gysname,isnull(t1.cmoney,0)-isnull(t2.ytmoney,0) as cmoney
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'
and jhdb.gysbh=1
GROUP BY jhdb.gysbh
) as t1
on gysb.gysbh=t1.gysbh
left join
(
select gysbh,sum(ytmoney) as ytmoney
from thdb
where jdate='2002-05-2'
and gysbh=1
GROUP BY gysbh
) as t2
on gysb.gysbh=t2.gysbh
可能你要的是：
select gysb.gysbh,gysb.gysname,isnull(t1.cmoney,0)-isnull(t2.ytmoney,0) as cmoney
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 jdate='2002-05-2'
GROUP BY gysbh
) as t2
on gysb.gysbh=t2.gysbh
where gysb.gysbh=1
Top
23 楼My_first（小@_@小）回复于 2002-08-26 16:43:05 得分 0
是这样的。
select gysb.gysbh,gysb.gysname,isnull(t1.cmoney,0)-isnull(t2.ytmoney,0) as cmoney
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 jdate='2002-05-2'
GROUP BY gysbh
) as t2
on gysb.gysbh=t2.gysbh
where gysb.gysbh=1
我把 and jdate='2002-05-2' 去掉 和where jdate='2002-05-2'去掉了
查询gysb.gysbh=1 查询成功。 但把日期加上去就出错了
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'jdate'.
我再把jdate加到
select jhdb.gysbh,jhdb.jdate,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, //非得这里加上jhdb.jdate,但这样查询结果不对
又出错了。Top
24 楼My_first（小@_@小）回复于 2002-08-26 16:50:34 得分 0
问题解决了。大放分。
我找到错误的原因。 是在
select gysbh,sum(ytmoney) as ytmoney
from thdb
where jdate='2002-05-2' // thdb表里是 thdate而不是 jdate， 哈 哈 哈 :))))))
GROUP BY gysbh
) as t2
on gysb.gysbh=t2.gysbh
where gysb.gysbh=1
高兴， 感谢 Yang_(扬帆破浪) ,谢谢各位朋友Top
25 楼wanwangzhiwang（万王之王）回复于 2002-08-26 17:02:19 得分 0
select A.gysbh,A.gysname,T.summoney-D.ytmoney as cmoney
from gysb A,thdb D,(select gysbh,sum(sl*dj) as summoney from jhdb B,jhmxb C where B.jhdh=C.jhdh group by gysbh) as T
where A.gysbh=T.gysbh and A.gysbh=D.gysbh
测试通过！Top
26 楼wanwangzhiwang（万王之王）回复于 2002-08-26 17:04:59 得分 0
考，原来问题已经解决了
faint，白忙活了
：（Top
27 楼Tongls（空中的风月）回复于 2002-08-26 17:05:39 得分 0
很简单啦！如下：
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
Top
28 楼liutaoboy（风间苍月）回复于 2002-08-26 17:54:24 得分 0
select a.gysbh,a.gysname,c.je-d.ytmoney Cmoney from
a,d,(select sum(sl*dj) je,gysbh from b,c where c.jhdh=b.jhdh group by gysbh) c
where a.gysbh=d.gysbh and a.gysbh=c.gysbh
我慢了，呜呜。。。。。。Top
