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

Question

sql

Answer 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
*/

Answer 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 行受影响)
*/

Answer 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
*/

Answer 4

CREATE TABLE TA
(
id INT PRIMARY KEY,[name] NVARCHAR(20)
)
GOCREATE TABLE TB
(
id INT,AID INT, 
[type] VARCHAR(4),
[date] DATETIME,
FOREIGN KEY(AID) REFERENCES TA(ID)
)
GOINSERT INTO TA
SELECT 1,N'小明' UNION ALL
SELECT 2,N'大明' UNION ALL
SELECT 3,N'三明' UNION ALL
SELECT 4,N'四明' 
GOINSERT 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筛选的话，将结果当子查询，再查即可

Answer 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

Answer 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.numName ID          date       Type
---- ----------- ---------- ----
大明   3           2013-05-01 a
三明   5           2013-12-01 a
四明   8           2013-02-02 b
小明   2           2013-01-02 b

Answer 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
*/

Answer 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'

Answer 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