尝试优化我的SQL（内部联接和组）时出现问题

本文介绍了尝试优化我的SQL（内部联接和组）时出现问题的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我在加入和分组两个表时遇到问题。我正在使用 ms sql server 2005 express 。 我也尝试过 GROUP BY otd.task，otd.date_request，ot.approved_by 但它给​​了我这个错误： 列 ' otd.userid' 无效 选择列表，因为它 =code-keyword>聚合 功能 或 GROUP BY 子句。 谢谢提前！解决方案 我认为您需要将date_request添加到连接条件中，否则你的联接不会产生独特的结果： SELECT otd。 userid，otd.task，otd.date_request，ot.approved_by FROM otd INNER JOIN ot ON otd.userid = ot.requested_by AND otd.date_request = ot.date_request WHERE otd.userid = ' xxx' AND CONVERT （ varchar ，otd.date_request， 101 ） BETWEEN ' 09/10/2013' AND ' 09/11/2013' AND ot.status = ' A' ORDER BY otd.date_request，ot.date_request ASC 你也可以使用不同的 SELECT distinct otd.userid，otd.task，otd.date_request，ot.approved_by FROM otd INNER JOIN ot ON otd.userid = ot.requested_by WHERE otd.userid ='xxx' AND CONVERT（varchar，otd.date_request，101）BETWEEN '09 / 10/2013'和'09 / 11/2013' AND ot.status ='A' ORDER BY otd.date_request，ot.date_request ASC 试试这个.... 选择otd.userid，otd.task，otd.date_request，ot.apporved_by来自otd left join ot on otd.userid = ot.requested_by where otd.userid =' xxx'和ot.date_request = otd.date_request按apporved_by排序 Im having a problem in joining and grouping two table. Im using ms sql server 2005 express .I also tried GROUP BY otd.task,otd.date_request,ot.approved_by but it gives me this error:Column 'otd.userid' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.Thank you in advance! 解决方案 I think you need to add date_request into the join criteria, otherwise your join does not generate unique results:SELECT otd.userid,otd.task,otd.date_request,ot.approved_by FROM otd INNER JOIN ot ON otd.userid = ot.requested_by AND otd.date_request = ot.date_request WHERE otd.userid ='xxx' AND CONVERT(varchar,otd.date_request,101) BETWEEN '09/10/2013' AND '09/11/2013' AND ot.status ='A'ORDER BY otd.date_request,ot.date_request ASCYou can also use distinct SELECT distinct otd.userid,otd.task,otd.date_request,ot.approved_by FROM otd INNER JOIN ot ON otd.userid = ot.requested_byWHERE otd.userid ='xxx' AND CONVERT(varchar,otd.date_request,101) BETWEEN '09/10/2013' AND '09/11/2013' AND ot.status ='A'ORDER BY otd.date_request,ot.date_request ASCTry this....select otd.userid,otd.task,otd.date_request,ot.apporved_by from otd left join ot on otd.userid=ot.requested_by where otd.userid='xxx' and ot.date_request=otd.date_request order by apporved_by 这篇关于尝试优化我的SQL（内部联接和组）时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！