SQL 两表联合查询后的结果

[lqa222]

一个主表，表结构如下
aid a b c
1 w n m
一个子表，表结构如下，存储主表的主键
id d e f aid
1 p o l 1
2 t y u 1
3 z x h 1
查询子表aid与主表aid相等的记录结果如下
aid a b c d e f
1 w n m p o l
1 w n m t y u
1 w n m z x h
我想要实现的结果为：
aid a b c d e f
1 w n m p o l
t y u
z x h
即：联合查询后重复的值去掉，如何用SQL语句来实现？

[jwdream2008]

帮顶！期待大侠！

[SQL77]

SELECT B.AID ,A,B,C,D,E,F FROM B LEFT JOIN A ON A.AID=B.AID 
AND NOT EXISTS(SELECT 1 FROM B T WHERE AID=B.AID AND ID>T.ID)

??试试

[SQL77]

SELECT B.AID ,
CASE WHEN NOT EXISTS(SELECT 1 FROM B T WHERE AID=B.AID AND ID>T.ID) THEN A ELSE NULL END AS A,
CASE WHEN NOT EXISTS(SELECT 1 FROM B T WHERE AID=B.AID AND ID>T.ID) THEN B ELSE NULL END AS B,
CASE WHEN NOT EXISTS(SELECT 1 FROM B T WHERE AID=B.AID AND ID>T.ID) THEN C ELSE NULL END AS C,
D,E,F 
FROM B LEFT JOIN A ON A.AID=B.AID 

这样应该行了

[lqa222]

2、3楼的解释都没有实现
不过还是谢谢
哪位大侠帮帮忙啊

[SQL77]

CREATE TABLE TB1(aid  INT,a CHAR,  b CHAR,  c CHAR)
INSERT TB1
SELECT 1   , 'w',  'n',  'm' 

CREATE TABLE TB2(ID  INT,D CHAR,  E CHAR,  F CHAR,AID INT)
INSERT TB2
SELECT 1   , 'P',  'O',  'L' ,1 UNION
SELECT 2   , 'T',  'Y',  'm' ,1 UNION
SELECT 3   , 'Z',  'X',  'm' ,1

--DROP TABLE TB2

SELECT * FROM TB1
SELECT * FROM TB2

SELECT 
CASE WHEN  NOT EXISTS(SELECT 1 FROM TB2  WHERE AID=B.AID AND B.ID>ID) THEN A.AID ELSE NULL END AS AID,
CASE WHEN  NOT EXISTS(SELECT 1 FROM TB2  WHERE AID=B.AID AND B.ID>ID) THEN A ELSE NULL END AS A,
CASE WHEN  NOT EXISTS(SELECT 1 FROM TB2  WHERE AID=B.AID AND B.ID>ID) THEN B ELSE NULL END AS B,
CASE WHEN  NOT EXISTS(SELECT 1 FROM TB2  WHERE AID=B.AID AND B.ID>ID) THEN C ELSE NULL END AS C,
D,E,F 
FROM TB2 B LEFT JOIN TB1 A ON A.AID=B.AID 


AID         A    B    C    D    E    F    
----------- ---- ---- ---- ---- ---- ---- 
1           w    n    m    P    O    L
NULL        NULL NULL NULL T    Y    m
NULL        NULL NULL NULL Z    X    m

[--小F--]

用isnull来做

[feixianxxx]

-- =========================================
-- -----------t_mac 小编-------------------
   --------------------希望有天成为大虾---- 
-- =========================================

IF OBJECT_ID('tb1') IS NOT NULL
DROP TABLE tb1
GO
CREATE TABLE tb1( aid int, a varchar(10), b varchar(10) ,c varchar(10))
go
insert tb1 SELECT 
1  ,  'w' , 'n' , 'm' 
go
IF OBJECT_ID('tb2') IS NOT NULL
DROP TABLE tb2
GO
CREATE TABLE tb2( id int, d varchar(10), e varchar(10) ,f varchar(10),aid int)
go
insert tb2 SELECT 
1  ,  'p' , 'o' , 'l'  ,  1 UNION ALL SELECT 
2  ,  't' , 'y' , 'u'  ,  1 UNION ALL SELECT 
3  ,  'z' , 'x' , 'h' ,   1 
go
select rtrim(tb2.aid) as aid ,
a,b,b,
d,e,f
from tb1  join tb2 on tb1.aid=tb2.aid
where id in (select MIN(id) from tb2 group by aid)
union all
select '' ,'','','',d,e,f
from tb2
where id not in (select MIN(id) from tb2 group by aid)
/*
(3 行受影响)
aid          a          b          b          d          e          f
------------ ---------- ---------- ---------- ---------- ---------- ----------
1            w          n          n          p          o          l
                                              t          y          u
                                              z          x          h

*/

[ChinaJiaBing]

declare @tb table (aid int,a nvarchar(10),b nvarchar(10),c nvarchar(10))
insert into @tb  select 1,'w','n','m'
declare @tb1 table (id int,d nvarchar(10),e nvarchar(10),f nvarchar(10),aid int)
insert into @tb1 select 1,'p','o','l',1
     union all   select 2,'t','y','u',1
     union all   select 3,'z','x','h',1
;With China as 
(
select a.*,b.id,b.d,b.e,b.f from @tb a join @tb1 b  on a.aid=b.aid
)
select aid= case when exists(select 1 from china where id<a.id) then '' else a.id  end,
       a= case when exists(select 1 from china where id<a.id) then '' else a end, 
       b= case when exists(select 1 from china where id<a.id) then '' else b  end, 
       c= case when exists(select 1 from china where id<a.id) then '' else c  end,d,e,f 
     from China a 

[lgqiu2008]

楼上的强大