求SQL语句，一表数据关联另一张表里最大的一条

有表A 
ID    Name
1     小明
2     大明
3     三明
4     四明


表B 
ID     AID   Type    date
1      1     a       2013-01-01
2      1     b       2013-01-02
3      2     a       2013-05-01
4      2     b       2013-03-02
5      3     a       2013-12-01
6      3     b       2013-01-08
7      3     a       2013-03-01
8      4     b       2013-02-02

表B的 AID是表A的外键  

现要求查出
A表的Name        B表ID    B表的最后一个时间的Data     B表的最后一个时间的Type
小明               2      2013-01-02                       b
大明               3      2013-05-01                       a
三明               5     2013-12-01                        a
四明               8      2013-02-02                       b


表A关联表B的相应最大时间的记录，并且可以根据 表B的Type进行筛选数据


在线等

9 个解决方案

#1

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2013-11-20 17:21:22
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[Name] varchar(4))
insert [A]
select 1,'小明' union all
select 2,'大明' union all
select 3,'三明' union all
select 4,'四明'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[AID] int,[Type] varchar(1),[date] datetime)
insert [B]
select 1,1,'a','2013-01-01' union all
select 2,1,'b','2013-01-02' union all
select 3,2,'a','2013-05-01' union all
select 4,2,'b','2013-03-02' union all
select 5,3,'a','2013-12-01' union all
select 6,3,'b','2013-01-08' union all
select 7,3,'a','2013-03-01' union all
select 8,4,'b','2013-02-02'
--------------开始查询--------------------------



select a.NAME,b.id,b.[date],b.[type]
from [A] INNER JOIN b  ON a.id=b.aid
WHERE EXISTS (SELECT 1 FROM (SELECT aid,MAX([date])[date] FROM  b GROUP BY aid) c WHERE b.aid=c.aid AND c.[date]=b.[date])
--AND b.TYPE=xxxx



----------------结果----------------------------
/* 
NAME id          date                    type
---- ----------- ----------------------- ----
小明   2           2013-01-02 00:00:00.000 b
大明   3           2013-05-01 00:00:00.000 a
三明   5           2013-12-01 00:00:00.000 a
四明   8           2013-02-02 00:00:00.000 b
*/

#2

select 
----------------------------------------------------------------
-- Author  :fredrickhu(小F，向高手学习)
-- Date    :2013-11-20 17:27:57
-- Verstion:
--      Microsoft SQL Server 2012 - 11.0.2100.60 (X64) 
--	Feb 10 2012 19:39:15 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[Name] varchar(4))
insert [A]
select 1,'小明' union all
select 2,'大明' union all
select 3,'三明' union all
select 4,'四明'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[AID] int,[Type] varchar(1),[date] datetime)
insert [B]
select 1,1,'a','2013-01-01' union all
select 2,1,'b','2013-01-02' union all
select 3,2,'a','2013-05-01' union all
select 4,2,'b','2013-03-02' union all
select 5,3,'a','2013-12-01' union all
select 6,3,'b','2013-01-08' union all
select 7,3,'a','2013-03-01' union all
select 8,4,'b','2013-02-02'
--------------开始查询--------------------------
SELECT a.NAME,t.id,t.[date],t.[type] FROM a INNER JOIN b t ON a.id=t.aid WHERE NOT EXISTS(SELECT 1 FROM b WHERE aid=t.aid AND [date]>t.[date])
----------------结果----------------------------
/* NAME id          date                    type
---- ----------- ----------------------- ----
小明   2           2013-01-02 00:00:00.000 b
大明   3           2013-05-01 00:00:00.000 a
三明   5           2013-12-01 00:00:00.000 a
四明   8           2013-02-02 00:00:00.000 b

(4 行受影响)
*/

#3

是这样吗：

if object_id('[A]') is not null drop table [A]
go 

create table [A]([ID] int,[Name] varchar(4))
insert [A]
select 1,'小明' union all
select 2,'大明' union all
select 3,'三明' union all
select 4,'四明'


if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[AID] int,[Type] varchar(1),[date] datetime)
insert [B]
select 1,1,'a','2013-01-01' union all
select 2,1,'b','2013-01-02' union all
select 3,2,'a','2013-05-01' union all
select 4,2,'b','2013-03-02' union all
select 5,3,'a','2013-12-01' union all
select 6,3,'b','2013-01-08' union all
select 7,3,'a','2013-03-01' union all
select 8,4,'b','2013-02-02'



select name,ID,DATE,TYPE
from 
(
select a.Name,b.ID,b.Type,b.date,
       ROW_NUMBER() over(partition by b.aid order by date desc) as rownum
from a
inner join B
        on a.ID = b.AID
)t
where rownum = 1
/*
name	ID	DATE	TYPE
小明	2	2013-01-02 00:00:00.000	b
大明	3	2013-05-01 00:00:00.000	a
三明	5	2013-12-01 00:00:00.000	a
四明	8	2013-02-02 00:00:00.000	b
*/

#4

CREATE TABLE TA
(
id INT PRIMARY KEY,[name] NVARCHAR(20)
)
GO

CREATE TABLE TB
(
id INT,AID INT, 
[type] VARCHAR(4),
[date] DATETIME,
FOREIGN KEY(AID) REFERENCES TA(ID)
)
GO

INSERT INTO TA
SELECT 1,N'小明' UNION ALL
SELECT 2,N'大明' UNION ALL
SELECT 3,N'三明' UNION ALL
SELECT 4,N'四明' 
GO

INSERT INTO TB
SELECT 1,1,'a','2013-01-01' UNION ALL
SELECT 2,1,'b','2013-01-02' UNION ALL
SELECT 3,2,'a','2013-05-01' UNION ALL
SELECT 4,2,'b','2013-03-02' UNION ALL
SELECT 5,3,'a','2013-12-01' UNION ALL
SELECT 6,3,'b','2013-01-08' UNION ALL
SELECT 7,3,'a','2013-03-01' UNION ALL
SELECT 8,4,'b','2013-02-02' 
GO

;WITH temp AS(
SELECT AID,MAX(date) maxdate
FROM TB
GROUP BY AID)

SELECT 
	c.[name],b.id,
	convert(CHAR(10),b.[date],120) [date],
	b.type
FROM TB b 
	INNER JOIN temp a ON b.AID = a.AID AND b.date = a.maxdate
	INNER JOIN TA c ON b.AID = c.id
ORDER BY id

/*
name                 id          date       type
-------------------- ----------- ---------- ----
小明                   2           2013-01-02 b
大明                   3           2013-05-01 a
三明                   5           2013-12-01 a
四明                   8           2013-02-02 b

(4 行受影响)
*/

要根据type筛选的话，将结果当子查询，再查即可

#5

or this?

 create table #A([ID] int,[Name] varchar(4))
 insert #A
 select 1,'小明' union all
 select 2,'大明' union all
 select 3,'三明' union all
 select 4,'四明'  
  create table #B([ID] int,[AID] int,[Type] varchar(1),[date] datetime)
  insert #B 
  select 1,1,'a','2013-01-01' union all
  select 2,1,'b','2013-01-02' union all
  select 3,2,'a','2013-05-01' union all
  select 4,2,'b','2013-03-02' union all
  select 5,3,'a','2013-12-01' union all
  select 6,3,'b','2013-01-08' union all
  select 7,3,'a','2013-03-01' union all
  select 8,4,'b','2013-02-02'
  
  select a.name,ab.id ,ab.date ,ab.Type  
  from #A  a
  cross apply (select top 1 * from #B  b where a.ID =b.AID 
  order by date desc ) ab
  
  drop table #A 
  drop table #B

#6

;with tb
as
(
select [B].ID,[A].Name,[B].Type,CONVERT(VARCHAR(10),[B].date,120)'date' ,
ROW_NUMBER()over(partition by  [A].name order by date) 'num'
from [B] left join [A]
ON [A].ID=[B].AID

)
select tb.Name,tb.ID,tb.date,tb.Type from tb,(
select a1.Name,MAX(num)'num' from tb a1
group by Name)a
where a.Name=tb.Name and a.num=tb.num

Name ID          date       Type
---- ----------- ---------- ----
大明   3           2013-05-01 a
三明   5           2013-12-01 a
四明   8           2013-02-02 b
小明   2           2013-01-02 b

#7

--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[Name] varchar(4))
insert [A]
select 1,'小明' union all
select 2,'大明' union all
select 3,'三明' union all
select 4,'四明'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[AID] int,[Type] varchar(1),[date] datetime)
insert [B]
select 1,1,'a','2013-01-01' union all
select 2,1,'b','2013-01-02' union all
select 3,2,'a','2013-05-01' union all
select 4,2,'b','2013-03-02' union all
select 5,3,'a','2013-12-01' union all
select 6,3,'b','2013-01-08' union all
select 7,3,'a','2013-03-01' union all
select 8,4,'b','2013-02-02'

----------------查询----------------------------

SELECT d.Name,e.ID,e.date,e.Type FROM (SELECT * FROM  [A] a,
(SELECT  AID,MAX(date) date FROM [B] b GROUP BY b.AID)c
 WHERE a.ID=c.AID)d INNER JOIN dbo.B e
ON d.AID=e.AID AND d.date=e.date

----------------结果----------------------------
/* 
NAME    id          date            type
----   ---- ----------------------- ----

小明	2	2013-01-02 00:00:00.000	b
大明	3	2013-05-01 00:00:00.000	a
三明	5	2013-12-01 00:00:00.000	a
四明	8	2013-02-02 00:00:00.000	b
*/

#8

IF OBJECT_ID('A') IS NOT NULL 
    DROP TABLE A
go 
CREATE TABLE A ( ID INT, Name VARCHAR(4) )
INSERT  A
        SELECT  1 ,
                '小明'
        UNION ALL
        SELECT  2 ,
                '大明'
        UNION ALL
        SELECT  3 ,
                '三明'
        UNION ALL
        SELECT  4 ,
                '四明'
IF OBJECT_ID('B') IS NOT NULL 
    DROP TABLE B
go 
CREATE TABLE B
    (
      ID INT ,
      AID INT ,
      Type VARCHAR(1) ,
      date DATETIME
    )
INSERT  B
        SELECT  1 ,
                1 ,
                'a' ,
                '2013-01-01'
        UNION ALL
        SELECT  2 ,
                1 ,
                'b' ,
                '2013-01-02'
        UNION ALL
        SELECT  3 ,
                2 ,
                'a' ,
                '2013-05-01'
        UNION ALL
        SELECT  4 ,
                2 ,
                'b' ,
                '2013-03-02'
        UNION ALL
        SELECT  5 ,
                3 ,
                'a' ,
                '2013-12-01'
        UNION ALL
        SELECT  6 ,
                3 ,
                'b' ,
                '2013-01-08'
        UNION ALL
        SELECT  7 ,
                3 ,
                'a' ,
                '2013-03-01'
        UNION ALL
        SELECT  8 ,
                4 ,
                'b' ,
                '2013-02-02'
GO
SELECT a.NAME,b.id,b.date,b.TYPE FROM a 
JOIN b ON a.id = b.aid
WHERE b.date in (SELECT MAX(date) FROM b JOIN a ON b.aid = a.id GROUP BY a.NAME)
--AND b.TYPE = 'a'

#9

DECLARE @t  TABLE 
(
id INT,
n VARCHAR(30)
)

DECLARE @t1 TABLE 
(
id INT,
aid INT,
TYPE1 VARCHAR(10),
date DATETIME
)

INSERT INTO @t 
SELECT 1   ,  '小明' UNION ALL
SELECT 2  ,   '大明' UNION ALL
SELECT 3  ,   '三明' UNION ALL
SELECT 4  ,   '四明' 

INSERT INTO @t1 
 SELECT 1   ,   1  ,   'a'   ,    '2013-01-01' UNION ALL
 SELECT 2  ,    1  ,   'b'  ,     '2013-01-02' UNION ALL
 SELECT 3  ,    2 ,    'a'  ,    '2013-05-01' UNION ALL
 SELECT 4  ,    2  ,   'b'   ,    '2013-03-02' UNION ALL
 SELECT 5   ,   3  ,  'a'     ,  '2013-12-01' UNION ALL 
 SELECT 6   ,   3   ,  'b'  ,    '2013-01-08' UNION ALL 
 SELECT 7   ,   3   ,  'a'     ,  '2013-03-01' UNION ALL
 SELECT 8  ,    4  ,   'b'    ,   '2013-02-02' 
 
 ;WITH a AS 
 (
 SELECT id,aid,type1,date,ROW_NUMBER() OVER( PARTITION BY aid ORDER BY date DESC) AS a1  FROM @t1 )
 
 SELECT n, a.id,a.date ,type1 FROM a INNER JOIN @t ON a.aid=[@t].id  where a1=1