SQL 现在是按天查询怎么修改成按日期查询呢！

Question

sql数据

Answer 1

这样？SELECT  a.gongsimc ,
        a.card_kehu_no AS kehu_no ,
        a.card_no ,
        a.card_kehu_mc AS kehu_mc ,
        a.card_kehu_shouji AS kehu_dh ,
        a.che_no ,
        a.card_kind ,
        a.card_enddate ,
        lastdate ,
        DATEDIFF(day, ISNULL(lastdate, GETDATE() - 365), GETDATE()) days
FROM    ( SELECT    zhifu_card_no ,
                    MAX(lastdate) AS lastdate
          FROM      ( SELECT    zhifu_card_no ,
                                xche_jsrq lastdate
                      FROM      work_pz_sj
                      UNION
                      SELECT    zhifu_card_no ,
                                xc_rq lastdate
                      FROM      work_xiche_pz_sj
                      UNION
                      SELECT    zhifu_card_no ,
                                xiao_rq lastdate
                      FROM      xiaosh_pz_sj
                      UNION
                      SELECT    card_no AS zhifu_card_no ,
                                xche_jsrq lastdate
                      FROM      work_pz_sj
                      UNION
                      SELECT    card_no AS zhifu_card_no ,
                                xc_rq lastdate
                      FROM      work_xiche_pz_sj
                      UNION
                      SELECT    card_no AS zhifu_card_no ,
                                xiao_rq lastdate
                      FROM      xiaosh_pz_sj
                    ) aa
          GROUP BY  zhifu_card_no
        ) b
        LEFT JOIN card a ON b.zhifu_card_no = a.card_no
WHERE   a.card_no IS NOT NULL
        AND DATEDIFF(day, ISNULL(lastdate, GETDATE() - 365), GETDATE()) >= 30
        AND DATEDIFF(day, ISNULL(lastdate, GETDATE() - 365), GETDATE()) <= 90
        AND a.GongSiNo = '02'
AND a.到期日期 BETWEEN @起始日期 AND @结束日期 

Answer 2

是这样吗：
select a.gongsimc,a.card_kehu_no as kehu_no,a.card_no,a.card_kehu_mc as kehu_mc,a.card_kehu_shouji as 
kehu_dh,a.che_no,a.card_kind,a.card_enddate,lastdate,datediff(day,isnull(lastdate,getdate()-365),getdate()) days  
from  
( 
select zhifu_card_no,max(lastdate) as lastdate    
from 
( 
select zhifu_card_no ,xche_jsrq  lastdate from work_pz_sj  
union select zhifu_card_no,xc_rq lastdate from work_xiche_pz_sj   
union select zhifu_card_no,xiao_rq lastdate from xiaosh_pz_sj
union select card_no as zhifu_card_no,xche_jsrq  lastdate from work_pz_sj  
union select card_no as zhifu_card_no,xc_rq lastdate from work_xiche_pz_sj  
union select card_no as zhifu_card_no,xiao_rq lastdate from xiaosh_pz_sj
) aa  
group by zhifu_card_no
) 
b  
left join card a  on b.zhifu_card_no=a.card_no   
where a.card_no is not null and 
      --datediff(day,isnull(lastdate,getdate()-365),getdate())>=30 and 
      --datediff(day,isnull(lastdate,getdate()-365),getdate())<=90 and 
      and lastdate>='2013-01-01' 
      and lastdate<='2014-01-01'
      a.GongSiNo = '02'

Answer 3

修改一下，上面代码少了一个and：
select a.gongsimc,a.card_kehu_no as kehu_no,a.card_no,a.card_kehu_mc as kehu_mc,a.card_kehu_shouji as 
kehu_dh,a.che_no,a.card_kind,a.card_enddate,lastdate,datediff(day,isnull(lastdate,getdate()-365),getdate()) days  
from  
( 
select zhifu_card_no,max(lastdate) as lastdate    
from 
( 
select zhifu_card_no ,xche_jsrq  lastdate from work_pz_sj  
union select zhifu_card_no,xc_rq lastdate from work_xiche_pz_sj   
union select zhifu_card_no,xiao_rq lastdate from xiaosh_pz_sj
union select card_no as zhifu_card_no,xche_jsrq  lastdate from work_pz_sj  
union select card_no as zhifu_card_no,xc_rq lastdate from work_xiche_pz_sj  
union select card_no as zhifu_card_no,xiao_rq lastdate from xiaosh_pz_sj
) aa  
group by zhifu_card_no
) 
b  
left join card a  on b.zhifu_card_no=a.card_no   
where a.card_no is not null and 
      --datediff(day,isnull(lastdate,getdate()-365),getdate())>=30 and 
      --datediff(day,isnull(lastdate,getdate()-365),getdate())<=90 and 
      and lastdate>='2013-01-01' 
      and lastdate<='2014-01-01'
      and a.GongSiNo = '02'