T-SQL选择条件连接 [英] T-SQL Select join with condition

问题描述

让我们说我有3张桌子:

Lets say that i have a 3 tables:

1. 汽车

• 编号

CarColorHistory

• 编号
• CarID
• ColorID
• 修改日期

颜色:

• 编号
• ColorName

我想选择所有汽车及其颜色，但重要的是，汽车的颜色是CarColorHistory表中最后修改的颜色.

I want to select all cars and their colors but the important thing is, that color for the car is the last modified color from CarColorHistory table.

我需要使用join来做到这一点.

I need to use join to do this.

示例:

汽车:

1
2

CarColorhistory:

CarColorhistory:

1 1 1 26/03/2012  -> (actual color, can be take by date or id)
2 1 2 25/03/2012
3 2 2 25/03/2012

颜色:

1 Blue
2 Red

我需要得到结果:(汽车ID，colorName)

I need to get result: (car id, colorName)

1 Blue
2 Red

我尝试通过加入Cars表和CarColorHistory表来实现此目的，但是我得到了所有颜色的汽车.我只需要实际的颜色(最后添加).

I tried make it by joining Cars table and CarColorHistory table but I get cars for all colors. I need only actual color (last added).

请帮助

推荐答案

有几种方法可以得到结果.您可以使用子查询获取max(modificationdate):

There are several ways that you can get the result. You can use a subquery to get the max(modificationdate):

select c.id, r.colorname
from cars c
inner join CarColorhistory h1
  on c.id = h1.carid
inner join
(
  select max(modificationdate) MaxDate,
    carid
  from CarColorhistory
  group by carid
) h2
  on h1.carid = h2.carid
  and h1.modificationdate = h2.maxdate
inner join color r
  on h1.colorid = r.id

请参见带有演示的SQL小提琴

或者由于您使用的是SQL Server，因此可以使用排名函数 :

Or since you are using SQL Server you can use ranking functions:

select id, colorname
from
(
  select c.id, r.colorname,
    row_number() over(partition by c.id order by modificationdate desc) rn
  from cars c
  inner join CarColorhistory h1
    on c.id = h1.carid
  inner join color r
    on h1.colorid = r.id
) src
where rn = 1;

请参见带有演示的SQL小提琴

这篇关于T-SQL选择条件连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！