例如：
表A
ih hc date
1 Q1 2007-1-1
1 Q1 2007-2-1
2 Q2 2007-1-5
3 Q3 2007-3-1
表B
ih idx jg
1 1 10.2
1 2 10.3
1 3 9.8
2 1 11
2 2 10.2
3 2 12
想要的结果：
ih hc date jg
1 Q1 2007-1-1 10.2
2007-2-1 10.3
9.8
2 Q2 2007-1-5 11
10.2
3 Q3 2007-3-1 12
如何实现？谢谢，在线等！
------解决方案--------------------
要求不明确。
ta中ih=1的2条记录,tb中ih=1的记录3条,join 出来6条。
idx=1的tb行与ih=1的ta的首行对应，
ih=1的tb行的其它行就与ih=1的第二行对应呢?
如果将这看成规定也可以，
那tb的idx=1的行对应到ta中同ih的首行，
其它行对应到ta中同ih的第二行。
那么ta中同ih的行有三行呢？又怎么取?
逻辑不明,不做回答
------解决方案--------------------
- SQL code
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 ) goselect 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 ) m4drop table A,B/*ih hc date jg ---------- ---------- ---------- -------------------- 1 Q1 2007-01-01 10.2 10.3 9.82 Q2 2007-01-05 11.0 10.23 Q3 2007-03-01 12.0（所影响的行数为 6 行）*/
------解决方案--------------------
- SQL code
--少了个条件 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