小白初学数据库,求教各位大神我要操作三个表,一个用户表(qzx_user),一个团队表(qzx_team),一个用户和任务的关联视图(qzx_v_user_team)用户完成任务后,关联视图里会有某人完成某个任务,获得多少时间(就是积分)的记录用子查询从关联表中计算出每个人的总时间然后将用户表和子查询出来的表外连接同时用户表里有个团队id,用这个id获得团队名称但就是where qzx_user.teamid=qzx_team.id 这一句话加到下面第二行后面就不行select qzx_user.*,qzx_team.name as teamname
from (qzx_user,qzx_team) //where qzx_user.teamid=qzx_team.id 这一句无法加进去
left join
(select userid,sum(gettime) as time from qzx_v_user_task group by userid) as usertime //子查询
on qzx_user,id=usertime.userid求解where怎么用,不知道我这么说表达的清楚不错误是 you have an error in your sql syntax;check the manual the manual that corresponds to your mysql server version for the right syntax to use near'left join (select userid,sum(gettime) as time from qzx_v_user_task group by userid)'没有where那一句 就可以执行
from (qzx_user,qzx_team) //where qzx_user.teamid=qzx_team.id 这一句无法加进去
left join
(select userid,sum(gettime) as time from qzx_v_user_task group by userid) as usertime //子查询
on qzx_user,id=usertime.userid求解where怎么用,不知道我这么说表达的清楚不错误是 you have an error in your sql syntax;check the manual the manual that corresponds to your mysql server version for the right syntax to use near'left join (select userid,sum(gettime) as time from qzx_v_user_task group by userid)'没有where那一句 就可以执行
(select qzx_user.*,qzx_team.name as teamname from qzx_user,qzx_team where qzx_user.teamid=qzx_team.id)qu
left join
(select userid,sum(gettime) as time from qzx_v_user_task group by userid) as usertime
on qu.id = usertime.userid
这样可以 好神奇 那个还有个问题 第一个子查询连接第二个表的时候 如果第二个表没有就会是null 可不可以没有就是0啊 要不然我还得在jdbc里判断一次,替换成0.
这样可以 好神奇 那个还有个问题 第一个子查询连接第二个表的时候 如果第二个表没有就会是null 可不可以没有就是0啊 要不然我还得在jdbc里判断一次,替换成0.用isnull判断,如下:
SELECT IFNULL(a1,'a') a1 FROM `test1`
+-------+------+
| a_new | a1 |
+-------+------+
| 1 | 1 |
| 2 | 2 |
| a | NULL |
+-------+------+
3 rows in set (0.00 sec)mysql>
这样可以 好神奇 那个还有个问题 第一个子查询连接第二个表的时候 如果第二个表没有就会是null 可不可以没有就是0啊 要不然我还得在jdbc里判断一次,替换成0.
select qu.*,ifnull(usertime.time,0)time from
(select qzx_user.*,qzx_team.name as teamname from qzx_user,qzx_team where qzx_user.teamid=qzx_team.id)qu
left join
(select userid,sum(gettime) as time from qzx_v_user_task group by userid) as usertime
on qu.id = usertime.userid