尝试优化我的SQL（内部联接和组）时出现问题 [英] problems while trying to optimize my SQL(inner join and group)

问题描述

我在加入和分组两个表时遇到问题。我正在使用 ms sql server 2005 express 。







我也尝试过 GROUP BY otd.task，otd.date_request，ot.approved_by 但它给​​了我这个错误：

 列 '  otd.userid' < span class =code-keyword> 无效  选择列表，因为它< span class =code-keyword>       =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 ASC

You 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_by
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

Try 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（内部联接和组）时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！