Having troubles figuring out a query here and need some input. I have a query that is joining multiple tables, and returns multiple rows for each unique value (VIN)
麻烦在这里搞清楚查询并需要一些输入。我有一个连接多个表的查询，并为每个唯一值（VIN）返回多行
SELECT
T1.VIN
,T1.station
,T1.complete_time
,T2.location
,T1.previous_status
FROM DBName.dbo.table_name1 T1 WITH (NOLOCK)
LEFT OUTER JOIN DBName.dbo.table_name2 T2 WITH (NOLOCK)
ON T2.object_key = T1.queue_key
LEFT OUTER JOIN DBName.dbo.table_name3 T3 WITH (NOLOCK)
ON T3.op_name = T1.op_name
LEFT OUTER JOIN DBName.dbo.table_name4 T4 WITH (NOLOCK)
ON T1.VIN = T4.VIN
WHERE T1.VIN IN
(
'ABC12301'
,'ABC12302'
,'ABC12303'
,'ABC12304'
,'ABC12305'
,'ABC12306'
,'ABC12307'
)
ORDER BY T1.complete_time desc
Here is what my output is. It will show all returned rows for each unique VIN
这是我的输出。它将显示每个唯一VIN的所有返回行
VIN Station Complete_Time Location Previous_Status
ABC12301 SOLD 2017-02-22 09:07:00 Y 800
ABC12301 PIT 2017-02-22 09:05:00 P 610
ABC12301 Drag 2017-02-22 09:03:00 P 610
ABC12301 Rain 2017-02-22 09:03:00 P 610
ABC12301 Trim 2017-02-22 09:01:00 P 610
ABC12302 SOLD 2017-02-22 08:59:00 Y 800
ABC12302 PIT 2017-02-22 08:57:00 P 610
ABC12302 Drag 2017-02-22 08:56:00 P 610
ABC12302 Rain 2017-02-22 08:54:00 P 610
ABC12302 Trim 2017-02-22 08:50:00 P 610
ABC12303 SOLD 2017-02-22 08:49:00 Y 800
ABC12303 PIT 2017-02-22 08:47:00 P 610
ABC12303 Drag 2017-02-22 08:46:00 P 610
ABC12303 Rain 2017-02-22 08:44:00 P 610
ABC12303 Trim 2017-02-22 08:40:00 P 610
.
.
.
.etc.............
What I'd like this query to do is only show the first 3 rows for each unique VIN, like the example below
我希望这个查询要做的只是显示每个唯一VIN的前3行，如下例所示
VIN Station Complete_Time Location Previous_Status
ABC12301 SOLD 2017-02-22 09:07:00 Y 800
ABC12301 PIT 2017-02-22 09:05:00 P 610
ABC12301 Drag 2017-02-22 09:03:00 P 610
ABC12302 SOLD 2017-02-22 08:59:00 Y 800
ABC12302 PIT 2017-02-22 08:57:00 P 610
ABC12302 Drag 2017-02-22 08:56:00 P 610
ABC12303 SOLD 2017-02-22 08:49:00 Y 800
ABC12303 PIT 2017-02-22 08:47:00 P 610
ABC12303 Drag 2017-02-22 08:46:00 P 610
1 个解决方案
#1
4
If it is SQL-Server, then this might work. Also, since you are doing the left join on T3,T4 without pulling any data from them, are they really necessary? Should they be a INNER JOINs? Lastly, I agree with Sean Lange's opinion above.
如果它是SQL-Server，那么这可能会起作用。另外，由于你在T3上进行左连接，T4没有从中提取任何数据，它们真的有必要吗？他们应该是内部联盟吗？最后，我同意Sean Lange的观点。
WITH cte AS (
SELECT
T1.VIN
,T1.station
,T1.complete_time
,T2.location
,T1.previous_status
,ROW_NUMBER() OVER (PARTITION BY T1.VIN ORDER BY T1.complete_time) AS RowNum
FROM DBName.dbo.table_name1 T1 --WITH (NOLOCK)
LEFT OUTER JOIN DBName.dbo.table_name2 T2 --WITH (NOLOCK)
ON T2.object_key = T1.queue_key
LEFT OUTER JOIN DBName.dbo.table_name3 T3 --WITH (NOLOCK)
ON T3.op_name = T1.op_name
-- Is T3 needed?
LEFT OUTER JOIN DBName.dbo.table_name4 T4 --WITH (NOLOCK)
ON T1.VIN = T4.VIN
-- Is T4 needed?
WHERE T1.VIN IN
(
'ABC12301'
,'ABC12302'
,'ABC12303'
,'ABC12304'
,'ABC12305'
,'ABC12306'
,'ABC12307'
)
)
SELECT *
FROM
cte
WHERE
RowNum < 4;
#1
4
If it is SQL-Server, then this might work. Also, since you are doing the left join on T3,T4 without pulling any data from them, are they really necessary? Should they be a INNER JOINs? Lastly, I agree with Sean Lange's opinion above.
如果它是SQL-Server，那么这可能会起作用。另外，由于你在T3上进行左连接，T4没有从中提取任何数据，它们真的有必要吗？他们应该是内部联盟吗？最后，我同意Sean Lange的观点。
WITH cte AS (
SELECT
T1.VIN
,T1.station
,T1.complete_time
,T2.location
,T1.previous_status
,ROW_NUMBER() OVER (PARTITION BY T1.VIN ORDER BY T1.complete_time) AS RowNum
FROM DBName.dbo.table_name1 T1 --WITH (NOLOCK)
LEFT OUTER JOIN DBName.dbo.table_name2 T2 --WITH (NOLOCK)
ON T2.object_key = T1.queue_key
LEFT OUTER JOIN DBName.dbo.table_name3 T3 --WITH (NOLOCK)
ON T3.op_name = T1.op_name
-- Is T3 needed?
LEFT OUTER JOIN DBName.dbo.table_name4 T4 --WITH (NOLOCK)
ON T1.VIN = T4.VIN
-- Is T4 needed?
WHERE T1.VIN IN
(
'ABC12301'
,'ABC12302'
,'ABC12303'
,'ABC12304'
,'ABC12305'
,'ABC12306'
,'ABC12307'
)
)
SELECT *
FROM
cte
WHERE
RowNum < 4;