为什么这语句找不出数据呢。该如何处理

为什么这语句找不出数据呢。

SQL code

declare @CompanyDate datetime  ;set @CompanyDate = '2012-04-08'  ;select GroupCode, ProductCode, ProjectCode, ProjectStartTime, ProjectEndTime, CreateTime, Idfrom MarketingCaseExpire_Bak  where datediff(mm,CreateTime,@CompanyDate)=1 andexists(    select * from MarketingCaseExpire_Bak b where GroupCode=b.GroupCode and ProductCode = b.ProductCode     and ProjectCode=b.ProjectCode    and convert(nvarchar(10),ProjectStartTime,120)= convert(nvarchar(10),b.ProjectStartTime,120)    and convert(nvarchar(10),ProjectEndTime,120)= convert(nvarchar(10),b.ProjectEndTime,120)    and convert(nvarchar(7),b.CreateTime,120)=convert(nvarchar(7),@CompanyDate,120)    and id<>b.id)

目的是
GroupCode, ProductCode, ProjectCode, ProjectStartTime, ProjectEndTime
这个月与上一个月的数据，如果上面5个字段都一样，则将上一月的数据查询出来。
下面是有数据的：
200010121430189038736200910011251662012-01-01 00:00:00.0002012-05-01 00:00:00.0002012-03-01 00:00:00.000b0d590e1-c683-40c5-8bb8-2dd5f41af99c
200010121430189038736200910011251662012-01-01 00:00:00.0002012-05-01 00:00:00.0002012-04-01 00:00:00.000d920876b-8e1c-4907-8152-aa1de3786a0c

[解决办法]
try:

SQL code

declare @CompanyDate datetime  ;set @CompanyDate = '2012-04-08'  ;select GroupCode, ProductCode, ProjectCode, ProjectStartTime, ProjectEndTime, CreateTime, Idfrom MarketingCaseExpire_Bak b where datediff(mm,CreateTime,@CompanyDate)=1 andexists(    select * from MarketingCaseExpire_Bak where GroupCode=b.GroupCode and ProductCode = b.ProductCode     and ProjectCode=b.ProjectCode    and convert(nvarchar(10),ProjectStartTime,120)= convert(nvarchar(10),b.ProjectStartTime,120)    and convert(nvarchar(10),ProjectEndTime,120)= convert(nvarchar(10),b.ProjectEndTime,120)    and convert(nvarchar(7),b.CreateTime,120)=convert(nvarchar(7),@CompanyDate,120)    and id<>b.id)
[解决办法]
代码改成这样试试：
SQL code
declare @CompanyDate datetime  ;set @CompanyDate = '2012-04-08'  ;select a.GroupCode, a.ProductCode, a.ProjectCode, a.ProjectStartTime, a.ProjectEndTime, a.CreateTime, a.Idfrom MarketingCaseExpire_Bak a inner join MarketingCaseExpire_Bak b on a.GroupCode=b.GroupCode and a.ProductCode = b.ProductCode and a.ProjectCode=b.ProjectCodeand convert(nvarchar(10),a.ProjectStartTime,120)= convert(nvarchar(10),b.ProjectStartTime,120)and convert(nvarchar(10),a.ProjectEndTime,120)= convert(nvarchar(10),b.ProjectEndTime,120)and convert(nvarchar(7),b.CreateTime,120)=convert(nvarchar(7),@CompanyDate,120)and a.id<>b.idwhere datediff(mm,CreateTime,@CompanyDate)=1 

 

查看更多 下一篇