日期:2014-5-18 浏览次数:20041次 点赞次数:20

数据库两个表联合查询,求高手解答.急急急
A表:

3711021994 张三 ………………
3711021989 李四 ………………

B表:

3711021994 男 2008毕业 2012.06.27
3711021994 女 2010毕业 2012.06.28

联合查询
3711021994 张三 女 2010毕业
3711021989 李四 NULL NULL

不要这种
3711021994 张三 男 2008毕业
3711021994 张三 女 2010毕业
3711021989 李四 NULL NULL

查询出张三的A表记录和最后一次B表的记录,没有则为空

------解决方案--------------------
SQL code
SELECT * FROM [a] 
LEFT JOIN [b]  AS t
ON a.id=t.id 
AND NOT EXISTS(SELECT 1 FROM b WHERE [id]=t.[id] AND [date]>t.[date])

------解决方案--------------------
SQL code
SELECT  *
FROM    ta a
        INNER JOIN tb b ON a.uid = b.uid
WHERE   NOT EXISTS ( SELECT 1
                     FROM   tb
                     WHERE  uid = b.uid
                            AND [date] > t.[date] )

------解决方案--------------------
SQL code

IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'A')
BEGIN
    DROP TABLE A
END
GO
CREATE TABLE A
(
    ID VARCHAR(10),
    Name VARCHAR(10)
)
GO
INSERT INTO A
SELECT '3711021994','张三' UNION
SELECT '3711021989','李四'
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'B')
BEGIN
    DROP TABLE B
END
GO
CREATE TABLE B
(
    ID VARCHAR(10),
    Sex VARCHAR(10),
    Gra VARCHAR(10),
    Date VARCHAR(10)
)
GO
INSERT INTO B
SELECT '3711021994', '男', '2008毕业', '2012.06.27' UNION
SELECT '3711021994', '女', '2010毕业', '2012.06.28'
GO

SELECT A.ID,
A.Name,
       Sex,
       Gra,
       Date
FROM A LEFT OUTER JOIN (
select ID,
       Sex,
       Gra,
       Date
from B AS t
where (select count(*) from B where ID=t.ID and Date>t.Date )<1) AS C ON A.ID = C.ID

------解决方案--------------------
SQL code
--> 测试数据:[A]
IF OBJECT_ID('[A]') IS NOT NULL DROP TABLE [A]
GO 
CREATE TABLE [A]([id] BIGINT,[name] VARCHAR(4))
INSERT [A]
SELECT 3711021994,'张三' UNION ALL
SELECT 3711021989,'李四'

--> 测试数据:[B]
IF OBJECT_ID('[B]') IS NOT NULL DROP TABLE [B]
GO 
CREATE TABLE [B]([id] BIGINT,[sex] VARCHAR(2),[graduate] VARCHAR(8),[date] DATETIME)
INSERT [B]
SELECT 3711021994,'男','2008毕业','2012.06.27' UNION ALL
SELECT 3711021994,'女','2010毕业','2012.06.28'
--------------开始查询--------------------------

SELECT * FROM [a] 
LEFT JOIN [b]  AS t
ON a.id=t.id 
AND NOT EXISTS(SELECT 1 FROM b WHERE [id]=t.[id] AND [date]>t.[date])
----------------结果----------------------------
/* 
id                   name id                   sex  graduate date
-------------------- ---- -------------------- ---- -------- -----------------------
3711021994           张三   3711021994           女    2010毕业   2012-06-28 00:00:00.000
3711021989           李四   NULL                 NULL NULL     NULL

(2 行受影响)


*/