研究生啊研究生，来吧,该怎么处理

研究生啊研究生，来吧
表A A01 A02 A03 A04
   
  A ZS0001 - 20111201
  B - ZS0002 20120201
  C ZS0003 ZS0003 20120321
  D ZS0004 - 20120301
  E ZS0005 ZS0005 20120301

表B B01 B02 B03 B04

  A ZS0001 2011 12
  B ZS0002 2012 2
  C ZS0003 2012 3
  D ZS0004 2012 3
  E ZS0005 2012 3  

有没有办法把A01并上A02或者A03中不为空的一个再并上A04，然后等于B01||B02||B03||B04，有三个难点，第一就是取A02和A03中不为空的来并，第二就是A04为日期字段，取月份的话会带上0，但是B04没有0，，第三A04对应的是B03||B04减掉一个月份，比如说A04=20111201对应的是B03=2011，B04=11，如果A04=20120101的话，那么对应的B03=2011，B04=12，难啊，难啊，救命啊

[解决办法]
第一个难点 isnull(A01,A02)
第二第三 判断 A04 <> Case when B04 =12 then B03+1 else B03 + Right('0'+cast(1+1 as varchar),2)
是不是就可以啦

[解决办法]

SQL code

--> 测试数据：[A1]if object_id('[A1]') is not null drop table [A1]create table [A1]([A01] varchar(1),[A02] varchar(6),[A03] varchar(8),[A04] datetime)insert [A1]select 'A','ZS0001',null,'20111201' union allselect 'B',null,'ZS0002','20120201' union allselect 'C','ZS0003','ZS0003','20120321' union allselect 'D','ZS0004',null,'20120301' union allselect 'E','ZS0005','ZS0005','20120301'select [A01] as [B01],case when [A02] is null then [A03] else [A02] end as [B02],LTRIM(YEAR([A04])) as [B03],ltrim(MONTH([A04])) as [B04]from A1/*B01    B02    B03    B04A    ZS0001    2011    12B    ZS0002    2012    2C    ZS0003    2012    3D    ZS0004    2012    3E    ZS0005    2012    3*/
[解决办法]
SQL code
create table A(A01 varchar2(20),A02 varchar2(20),A03 varchar2(20),A04 date)insert into a values('A', 'ZS0001', null     ,to_date('20111201','YYYYMMDD'))insert into a values('B', null    , 'ZS0002' ,to_date('20120201','YYYYMMDD'))insert into a values('C', 'ZS0003', 'ZS0003' ,to_date('20120321','YYYYMMDD'))insert into a values('D', 'ZS0004', null     ,to_date('20120301','YYYYMMDD'))insert into a values('E', 'ZS0005', 'ZS0005' ,to_date('20120301','YYYYMMDD'))create table B(B01 varchar2(20),B02 varchar2(20),B03 int,B04 int)insert into b values('A', 'ZS0001', 2011 ,12)insert into b values('B', 'ZS0002', 2012 ,2)insert into b values('C', 'ZS0003', 2012 ,3)insert into b values('D', 'ZS0004', 2012 ,3)insert into b values('E', 'ZS0005', 2012 ,3 )select * from b--你是只比对月份则如下：select a.a01, (case when a.a02 = '' and a.a02 is null then a.a03 else a.a02 end) a02_03,to_char(a.a04,'yyyymmdd') a04, b.* from a , bwhere trunc(months_between(a.a04,to_date(to_char(b.b03) || LPAD(to_char(b.b04),2,'0') || '01','YYYYMMDD'))) = 1/*A01 A02_03 A04      B01 B02    B03  B04C   ZS0003 20120321 B   ZS0002 2012 2 D   ZS0004 20120301 B   ZS0002 2012 2 E   ZS0005 20120301 B   ZS0002 2012 2 3 rows selected.*/--不知道是否是a01-->b01 and a02(a03)-->b02 and 月份也相等，则如下：select a.a01, (case when a.a02 = '' and a.a02 is null then a.a03 else a.a02 end) a02_03,to_char(a.a04,'yyyymmdd') a04, b.* from a , bwhere a.a01 = b.b01 and (case when a.a02 = '' and a.a02 is null then a.a03 else a.a02 end) = b.b02 andtrunc(months_between(a.a04,to_date(to_char(b.b03) || LPAD(to_char(b.b04),2,'0') || '01','YYYYMMDD'))) = 1/*A01 A02_03 A04      B01 B02    B03  B04no rows selected*/drop table adrop table b