加入SqlServer时出现问题 [英] Problem in Join in SqlServer

问题描述

在@MonthTable中我有1月12日和2005年的月份。并且使用glpost表进行左联接

。我需要在[date]列的monthno和月份匹配或不是每次都匹配数据应该来了。

但这个查询返回匹配的record.whats错误的查询。

谢谢

In @MonthTable i have monthno from 1 to 12 and year 2005.and make left join with glpost table
.I need when monthno and month of [date] column match or not every time all data should come.
but this query return on matched record.whats wrong with that query.
Thanks

Declare @MonthTable Table (MonthNo int, YearNo int)

SELECT MT.MonthNo, MT.YearNo, D.ID, Sum(P.DebitAmt), Sum(P.CreditAmt)
 FROM  @MonthTable [MT]
Left Join GLPOST [P] on MT.MonthNo = Month(P.[Date]
 Join  GLData [D] on P.GLDATAID = D.ID
Where D.ID=610
Group By MT.MonthNo, MT.YearNo, D.ID
Order by D.ID, MT.yearNo, MT.MonthNo

推荐答案

您提供的SQL代码中似乎存在拼写错误;在线：

There seems to be a typo in the SQL code you provided; on line:

Left Join GLPOST [P] on MT.MonthNo = Month(P.[Date]

缺少右括号。

应该读：

There is a closing parenthesis missing.
One should read:

Left Join GLPOST [P] on MT.MonthNo = Month(P.[Date])

希望这会有所帮助。







自 D.ID 包含在GROUP BY子句中，我会为此列寻找HAVING而不是WHERE子句。此外，您不需要此处的[D]表（唯一的列）您从中检索可以从P.GLDATAID获得。因此：

Hope this helps.



Since D.ID is included in GROUP BY clause, I would go for a HAVING instead of a WHERE clause for this column. Also, you do not really need the [D] table here (the only column you are retrieving from it can be obtained from P.GLDATAID). Thus:

SELECT MT.MonthNo, MT.YearNo, P.GLDATAID, Sum(P.DebitAmt), Sum(P.CreditAmt)
FROM  @MonthTable [MT]
  LEFT JOIN GLPOST [P] on MT.MonthNo = Month(P.[Date])
HAVING P.GLDATAID=610
GROUP BY MT.MonthNo, MT.YearNo, P.GLDATAID
ORDER BY P.GLDATAID, MT.yearNo, MT.MonthNo

[/编辑]

[/Edit]

这篇关于加入SqlServer时出现问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！