为什么这语句找不出数据呢。该如何处理
日期：2014-05-18 浏览次数：20585 次
为什么这语句找不出数据呢。
目的是
GroupCode, ProductCode, ProjectCode, ProjectStartTime, ProjectEndTime
这个月与上一个月的数据，如果上面5个字段都一样，则将上一月的数据查询出来。
下面是有数据的：
2000101214 30189038736 20091001125166 2012-01-01 00:00:00.000 2012-05-01 00:00:00.000 2012-03-01 00:00:00.000 b0d590e1-c683-40c5-8bb8-2dd5f41af99c
2000101214 30189038736 20091001125166 2012-01-01 00:00:00.000 2012-05-01 00:00:00.000 2012-04-01 00:00:00.000 d920876b-8e1c-4907-8152-aa1de3786a0c
------解决方案--------------------
try:
- SQL code
declare @CompanyDate datetime ;
set @CompanyDate = '2012-04-08' ;
select GroupCode, ProductCode, ProjectCode, ProjectStartTime, ProjectEndTime, CreateTime, Id
from MarketingCaseExpire_Bak where
datediff(mm,CreateTime,@CompanyDate)=1 and
exists
(
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个字段都一样，则将上一月的数据查询出来。
下面是有数据的：
2000101214 30189038736 20091001125166 2012-01-01 00:00:00.000 2012-05-01 00:00:00.000 2012-03-01 00:00:00.000 b0d590e1-c683-40c5-8bb8-2dd5f41af99c
2000101214 30189038736 20091001125166 2012-01-01 00:00:00.000 2012-05-01 00:00:00.000 2012-04-01 00:00:00.000 d920876b-8e1c-4907-8152-aa1de3786a0c
------解决方案--------------------
try:
- SQL code
declare @CompanyDate datetime ;
set @CompanyDate = '2012-04-08' ;
select GroupCode, ProductCode, ProjectCode, ProjectStartTime, ProjectEndTime, CreateTime, Id
from MarketingCaseExpire_Bak b where
datediff(mm,CreateTime,@CompanyDate)=1 and
exists
(
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.Id
from MarketingCaseExpire_Bak a inner join MarketingCaseExpire_Bak b on a.GroupCode=b.GroupCode
and a.ProductCode = b.ProductCode
and a.ProjectCode=b.ProjectCode
and 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.id
where datediff(mm,CreateTime,@CompanyDate)=1
免责声明： 本文仅代表作者个人观点，与爱易网无关。其原创性以及文中陈述文字和内容未经本站证实，对本文以及其中全部或者部分内容、文字的真实性、完整性、及时性本站不作任何保证或承诺，请读者仅作参考，并请自行核实相关内容。
