日期:2014-05-18  浏览次数:20645 次

关于多表合并连接查询,如何去掉重复的结果?
例如:
表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 ) 
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 行)
*/

------解决方案--------------------
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