一个数据库（提料申请）tl_tld
tl_Id(ID),tl_number(提料数量)
（到料数据）tl_done
d_ID(ID),tl_ID(提料申请的ID),tl_cnumber（到料数量）
一个提料申请可有多次到料数据现在要显示提料申请信息，但是提料数量要显示成：提料数量-到料数量
select a.tl_Id,(a.tl_number-b.tl_cnumber)as tl_number from tl_tld as a LEFT JOIN (select tl_Id,count(tl_cnumber)as tl_cnumber from tl_done GROUP by tl_Id)as b on a.tl_Id = b.tl_Id这样写一直提示错误，请高手指点fredrickhu
(小F)
等 级：
更多勋章
#1楼 得分：30回复于：2011-08-21 23:50:25
SQL code
select
a.tl_Id,(isnull(a.tl_number,0)-isnull(b.tl_cnumber,0))as tl_number
from
tl_tld as a
LEFT JOIN
(select tl_Id,count(tl_cnumber)as tl_cnumber from tl_done GROUP by tl_Id)as b
on
a.tl_Id = b.tl_Id语句没有看 出有什么问题 请问报的是什么错?
fancy0529
等 级：
#3楼 得分：0回复于：2011-08-21 23:58:09
DBConn.executeQuery():You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0)-isnull(b.tl_cnumber,0))as tl_number from tl_tld as a LEFT JO我用的是mysql，是不是数据库的问题
tl_Id(ID),tl_number(提料数量)
（到料数据）tl_done
d_ID(ID),tl_ID(提料申请的ID),tl_cnumber（到料数量）
一个提料申请可有多次到料数据现在要显示提料申请信息，但是提料数量要显示成：提料数量-到料数量
select a.tl_Id,(a.tl_number-b.tl_cnumber)as tl_number from tl_tld as a LEFT JOIN (select tl_Id,count(tl_cnumber)as tl_cnumber from tl_done GROUP by tl_Id)as b on a.tl_Id = b.tl_Id这样写一直提示错误，请高手指点fredrickhu
(小F)
等 级：
更多勋章
#1楼 得分：30回复于：2011-08-21 23:50:25
SQL code
select
a.tl_Id,(isnull(a.tl_number,0)-isnull(b.tl_cnumber,0))as tl_number
from
tl_tld as a
LEFT JOIN
(select tl_Id,count(tl_cnumber)as tl_cnumber from tl_done GROUP by tl_Id)as b
on
a.tl_Id = b.tl_Id语句没有看 出有什么问题 请问报的是什么错?
fancy0529
等 级：
#3楼 得分：0回复于：2011-08-21 23:58:09
DBConn.executeQuery():You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0)-isnull(b.tl_cnumber,0))as tl_number from tl_tld as a LEFT JO我用的是mysql，是不是数据库的问题
mysql> select a.tl_Id,(a.tl_number-b.tl_cnumber)as tl_number
-> from tl_tld as a LEFT JOIN
-> (select tl_Id,count(tl_cnumber)as tl_cnumber from tl_done GROUP by tl_Id)
as b
-> on a.tl_Id = b.tl_Id
-> ;
Empty set (0.00 sec)
select a.tl_Id,(a.tl_number-b.tl_cnumber)as tl_number from tl_tld as a LEFT JOIN (select tl_Id,count(tl_cnumber)as tl_cnumber from tl_done GROUP by tl_Id)as b on a.tl_Id = b.tl_Id说明：1、语句如果忽略格式，应该是没错的，应该提供具体的需求，大家都会明白些。
2、到底是用count 还是 sum ？---------------------------------------------------------------------
SQL code
select
a.tl_Id,(isnull(a.tl_number,0)-isnull(b.tl_cnumber,0))as tl_number
from
tl_tld as a
LEFT JOIN
(select tl_Id,count(tl_cnumber)as tl_cnumber from tl_done GROUP by tl_Id)as b
on
a.tl_Id = b.tl_Id语句没有看 出有什么问题 请问报的是什么错?
fancy0529
等 级：
#3楼 得分：0回复于：2011-08-21 23:58:09
DBConn.executeQuery():You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '0)-isnull(b.tl_cnumber,0))as tl_number from tl_tld as a LEFT JO
说明：只说一点，mysql中没有isnull() ，可用ifnull() 替代。
select
a.tl_Id,(ifnull(a.tl_number,0)-ifnull(b.tl_cnumber,0))as tl_number
from
tl_tld as a
LEFT JOIN
(select tl_Id,sum(tl_cnumber) as tl_cnumber from tl_done GROUP by tl_Id)as b
on
a.tl_Id = b.tl_Idsql执行错误#1064，从数据库的响应：
you have an error in your sql syntax. check the manual that corresponds to your mysql server version for the right syntax to use near 'select tl_Id,sum(tl_cnumber) as tl_cnumber from tl_done GROUP b
有没有替代办法，就是不使用(select tl_Id,sum(tl_cnumber) as tl_cnumber from tl_done GROUP by tl_Id)as b这种情况，直接用在两个存在的表中操作。我发现我只要在sql套用select就出这个错误。