Nutz
  1. 主页/
  2. 问答
NutzCN Logo
问答 求大神帮忙 sql哪里有问题
发布于 2473天前 作者 wx_0quujkao0it5ekj89bt6 2098 次浏览 复制 上一个帖子 下一个帖子
标签: 无 
 @Override
    public int queryPlayerWin(int id, int uid) {

        String sql = "SELECT ((";
        sql+=" SELECT count(*) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id";
        if (uid > 0) {
            sql += " AND po.user_id = uid";
        }
        sql += " AND pp.balances > 0)";
        sql +=" / (SELECT count(po.id) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id";
        if (uid > 0) {
            sql += " AND po.user_id = uid))";
        }
        sql += " * 100 FROM DUAL";
        return getInt(sql, new String[]{"pid", "uid"}, new Object[]{id, uid});


Caused by: java.sql.SQLException: Column 'id' in where clause is ambiguous
Query is: SELECT (( SELECT count(*) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id AND po.user_id = uid AND pp.balances > 0) / (SELECT count(po.id) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id AND po.user_id = uid)) * 100 FROM DUAL
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:113) ~[mariadb-java-client-2.0.3.jar:?]
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:168) ~[mariadb-java-client-2.0.3.jar:?]
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:337) ~[mariadb-java-client-2.0.3.jar:?]
	... 59 more
18:09:25.815 WARN  org.nutz.mvc.impl.processor.FailProcessor - Error@/player/findplaywin :
org.nutz.dao.DaoException: !Nutz SQL Error: 'SELECT (( SELECT count(*) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id AND po.user_id = uid AND pp.balances > 0) / (SELECT count(po.id) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id AND po.user_id = uid)) * 100 FROM DUAL'
PreparedStatement: 
'SELECT (( SELECT count(*) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id AND po.user_id = uid AND pp.balances > 0) / (SELECT count(po.id) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id AND po.user_id = uid)) * 100 FROM DUAL'
CaseMessage=(conn:200) Column 'id' in where clause is ambiguous
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:104) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:66) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.interceptor.DaoLogInterceptor.filter(DaoLogInterceptor.java:22) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.DaoInterceptorChain.doChain(DaoInterceptorChain.java:64) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.DaoInterceptorChain.invoke(DaoInterceptorChain.java:139) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.sql.run.NutDaoRunner.runCallback(NutDaoRunner.java:158) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.sql.run.NutDaoRunner._runWithoutTransaction(NutDaoRunner.java:125) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.sql.run.NutDaoRunner._run(NutDaoRunner.java:92) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.sql.run.NutDaoRunner.run(NutDaoRunner.java:81) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.DaoSupport.run(DaoSupport.java:240) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.DaoSupport._exec(DaoSupport.java:252) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.DaoSupport.execute(DaoSupport.java:236) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.NutDao.execute(NutDao.java:1008) ~[nutz-1.r.62.jar:1.r.62]
	at team.chance.thpoker.module.service.impl.BaseServiceImpl.getInt(BaseServiceImpl.java:336) ~[classes/:?]
	at team.chance.thpoker.module.service.impl.PlayerServiceImpl.queryPlayerWin(PlayerServiceImpl.java:301) ~[classes/:?]
	at team.chance.thpoker.module.web.controller.PlayerController.findPlayerWin(PlayerController.java:183) ~[classes/:?]
	at team.chance.thpoker.module.web.controller.PlayerController$FM$findPlayerWin$d33b191e7555d50f55031f38915dfa1c.invoke(PlayerController.java) ~[nutz-1.r.62.jar:?]
	at org.nutz.mvc.impl.processor.MethodInvokeProcessor.process(MethodInvokeProcessor.java:31) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.AdaptorProcessor.process(AdaptorProcessor.java:33) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.ActionFiltersProcessor.process(ActionFiltersProcessor.java:58) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.ModuleProcessor.process(ModuleProcessor.java:123) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.EncodingProcessor.process(EncodingProcessor.java:27) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.AbstractProcessor.doNext(AbstractProcessor.java:44) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.processor.UpdateRequestAttributesProcessor.process(UpdateRequestAttributesProcessor.java:15) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.NutActionChain.doChain(NutActionChain.java:44) [nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.impl.ActionInvoker.invoke(ActionInvoker.java:67) [nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.ActionHandler.handle(ActionHandler.java:31) [nutz-1.r.62.jar:1.r.62]
	at org.nutz.mvc.NutFilter.doFilter(NutFilter.java:202) [nutz-1.r.62.jar:1.r.62]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) [catalina.jar:8.0.38]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) [catalina.jar:8.0.38]
	at org.apache.logging.log4j.web.Log4jServletFilter.doFilter(Log4jServletFilter.java:71) [log4j-web-2.8.2.jar:2.8.2]
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:240) [catalina.jar:8.0.38]
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:207) [catalina.jar:8.0.38]
	at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:197) [catalina.jar:8.0.38]
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:106) [catalina.jar:8.0.38]
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:502) [catalina.jar:8.0.38]
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:141) [catalina.jar:8.0.38]
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:79) [catalina.jar:8.0.38]
	at org.apache.catalina.valves.RequestFilterValve.process(RequestFilterValve.java:313) [catalina.jar:8.0.38]
	at org.apache.catalina.valves.RemoteAddrValve.invoke(RemoteAddrValve.java:86) [catalina.jar:8.0.38]
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:616) [catalina.jar:8.0.38]
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:88) [catalina.jar:8.0.38]
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:528) [catalina.jar:8.0.38]
	at org.apache.coyote.http11.AbstractHttp11Processor.process(AbstractHttp11Processor.java:1100) [tomcat-coyote.jar:8.0.38]
	at org.apache.coyote.AbstractProtocol$AbstractConnectionHandler.process(AbstractProtocol.java:687) [tomcat-coyote.jar:8.0.38]
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.doRun(AprEndpoint.java:2508) [tomcat-coyote.jar:8.0.38]
	at org.apache.tomcat.util.net.AprEndpoint$SocketProcessor.run(AprEndpoint.java:2497) [tomcat-coyote.jar:8.0.38]
	at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1142) [?:1.8.0_77]
	at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:617) [?:1.8.0_77]
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:8.0.38]
	at java.lang.Thread.run(Thread.java:745) [?:1.8.0_77]
Caused by: java.sql.SQLIntegrityConstraintViolationException: (conn:200) Column 'id' in where clause is ambiguous
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.get(ExceptionMapper.java:152) ~[mariadb-java-client-2.0.3.jar:?]
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionMapper.getException(ExceptionMapper.java:118) ~[mariadb-java-client-2.0.3.jar:?]
	at org.mariadb.jdbc.MariaDbStatement.executeExceptionEpilogue(MariaDbStatement.java:245) ~[mariadb-java-client-2.0.3.jar:?]
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:343) ~[mariadb-java-client-2.0.3.jar:?]
	at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:513) ~[mariadb-java-client-2.0.3.jar:?]
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:108) ~[HikariCP-2.6.3.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java) ~[HikariCP-2.6.3.jar:?]
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:246) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:53) ~[nutz-1.r.62.jar:1.r.62]
	... 54 more
Caused by: java.sql.SQLException: Column 'id' in where clause is ambiguous
Query is: SELECT (( SELECT count(*) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id AND po.user_id = uid AND pp.balances > 0) / (SELECT count(po.id) FROM t_player p,t_player_poker pp,t_poker po WHERE p.id = pp.player_id AND pp.poker_id = po.id AND p.id = id AND po.user_id = uid)) * 100 FROM DUAL
	at org.mariadb.jdbc.internal.util.LogQueryTool.exceptionWithQuery(LogQueryTool.java:113) ~[mariadb-java-client-2.0.3.jar:?]
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:168) ~[mariadb-java-client-2.0.3.jar:?]
	at org.mariadb.jdbc.MariaDbStatement.executeInternal(MariaDbStatement.java:337) ~[mariadb-java-client-2.0.3.jar:?]
	at org.mariadb.jdbc.MariaDbStatement.executeQuery(MariaDbStatement.java:513) ~[mariadb-java-client-2.0.3.jar:?]
	at com.zaxxer.hikari.pool.ProxyStatement.executeQuery(ProxyStatement.java:108) ~[HikariCP-2.6.3.jar:?]
	at com.zaxxer.hikari.pool.HikariProxyStatement.executeQuery(HikariProxyStatement.java) ~[HikariCP-2.6.3.jar:?]
	at org.nutz.dao.impl.sql.run.NutDaoExecutor._runSelect(NutDaoExecutor.java:246) ~[nutz-1.r.62.jar:1.r.62]
	at org.nutz.dao.impl.sql.run.NutDaoExecutor.exec(NutDaoExecutor.java:53) ~[nutz-1.r.62.jar:1.r.62]
	... 54 more
18:09:25.818 DEBUG team.chance.thpoker.module.web.views.FailJson - 服务器错误

18:09:29.217 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:09:59.221 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:10:29.224 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:10:59.228 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:11:29.233 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:11:59.236 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:12:29.243 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:12:59.247 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:13:29.251 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:13:59.254 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:14:29.260 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:14:59.265 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:15:29.284 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:15:59.288 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:16:29.292 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:16:59.296 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:17:29.299 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:17:59.303 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:18:29.307 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:18:59.310 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:19:29.314 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:19:59.318 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:20:29.322 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:20:59.328 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:21:29.332 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:21:59.336 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:22:29.339 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:22:59.343 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:23:29.347 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:23:59.351 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:24:29.355 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:24:59.359 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:25:29.367 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:25:59.370 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
18:26:29.374 DEBUG com.zaxxer.hikari.pool.HikariPool - HikariPool-1 - Pool stats (total=10, active=0, idle=10, waiting=0)
6 回复

id uid是哪个表的字段，加上表别名

那是前端传过来的

sql里面id，uid哪个表的字段，加上表别名

return getInt(sql, new String[]{"pid", "uid"}, new Object[]{id, uid});那两个是用这个方法给的值

少写了@了，按你说的，那应该是个变量，例如

AND po.user_id = uid

应该写成

AND po.user_id = @uid

嗯呐 谢谢 感谢

添加回复
请先登陆
回到顶部