日期:2014-05-18 浏览次数:20645 次
create table A(ih varchar(10),hc varchar(10),date varchar(10)) insert into A values('1', 'Q1', '2007-01-01') insert into A values('1', 'Q1', '2007-02-01') insert into A values('2', 'Q2', '2007-01-05') insert into A values('3', 'Q3', '2007-03-01') create table B(ih varchar(10),idx int,jg decimal(18,1)) insert into B values('1', 1, 10.2) insert into B values('1', 2, 10.3) insert into B values('1', 3, 9.8 ) insert into B values('2', 1, 11 ) insert into B values('2', 2, 10.2) insert into B values('3', 2, 12 ) go select ih = case when idx=(select min(idx) from ( select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih ) m1 where m4.ih=m1.ih) then ih else '' end , hc = case when idx=(select min(idx) from ( select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih ) m2 where m4.ih=m2.ih) then hc else '' end , date = case when idx=(select min(idx) from ( select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih ) m3 where m4.ih=m3.ih) then date else '' end , jg from ( select t1.* , b.idx,b.jg from (select t.* from a t where date = (select min(date) from a where ih = t.ih)) t1,b where t1.ih = b.ih ) m4 drop table A,B /* ih hc date jg ---------- ---------- ---------- -------------------- 1 Q1 2007-01-01 10.2 10.3 9.8 2 Q2 2007-01-05 11.0 10.2 3 Q3 2007-03-01 12.0 (所影响的行数为 6 行) */
------解决方案--------------------
--少了个条件 SELECT ih= CASE WHEN B.ID >= 2 THEN '' ELSE CAST(A.ih AS VARCHAR) END ,hc=CASE WHEN B.ID >= 2 THEN '' ELSE ISNULL(A.hc,'') END, DATE = CASE WHEN (SELECT DATE FROM ( SELECT ID = (SELECT COUNT(1) FROM T1 WHERE A.ih = ih AND date<=a.date),* FROM T1 A ) M WHERE M.ID-1 = A.ID AND A.ih = M.ih ) = A.DATE THEN '' ELSE ISNULL(A.DATE,'') END, B.jg FROM ( SELECT ID = (SELECT COUNT(1) FROM T1 WHERE A.ih = ih AND date<=a.date),* FROM T1 A ) A RIGHT JOIN ( SELECT ID = (SELECT COUNT(1) FROM T2 WHERE A.ih = ih AND idx<=a.idx),* FROM T2 A ) B ON A.ID = B.ID AND A.ih = B.ih