如何从SQL中的两个表中获取不同的记录 [英] How to get distinct records from two tables in SQL

问题描述

我有两张桌子

第一张桌子（DynamicIndicatorID（P），Question_ID，Quarter，Year，District）

P代表主键，F代表外键

第二个表（fileID，FileName，DynamicIndicatorID（F），Datecreated）



现在我的第一个表中有四行就像那样



（1,1,1,1,1）

（2,1,1,2,1）

（3,1,2,1,1）

（4,1,1,1,2）

在我的第二个表中我有三个行，我的第二个表基本上包含每个DynamicIndicatorID的文件，它可以包含每个DynamicIndicatorID的零或多行



如下所示



秒表

（1，First.Docx，1,3 / 3/2001），

（2，New.Docx，1 ，2016年3月3日），

（3，second.Docx，2,3 / 3/2001），



现在我想写一个查询，它可以给我第一张表中的所有记录{即四行}

我想添加FileName列，所以我需要这个记录

（DynamicIndicatorID，Question_ID，Quarter，Year，District，FileName）

（1,1,1,1,1，New.Docx），

（2,1,1,2,1，Second.Docx），

（3,1,1,1,1，没什么），

（4,1,1,1,1，没什么），

我需要展示第一个表中的所有行，我想显示每个指标的最新文件，就像上面我有两个文件，用于DynamicIndicatorID = 1我得到最新的一个New.Docx，而对于DynamicIndicatorID = 2我只有一个所以我显示那个和DynamicIndicatorID 2,3我没有任何文件，所以我不得不显示任何内容，这意味着我想从First表中获取所有行，并且我想显示每个dynamicindicatorID的最新文件，每个DynamicIndicatorID必须具有大多数文件应该是最新的文件。



我尝试了什么：



i试过了，它为每个人提供了最新文件指标，但它没有显示其他两个没有任何文件的记录

以下是我的查询

SELECT Distinct DI.DynamicIndicatorID，Di.District，tpl.filenameName，Di .Quarter，DI.District，DI.TFA，DI.TFB，DI.TFG，DI.TFW，DI.TFM from DynamicIndicatorsTargetsForPu

AS DI

Left Outer Join tblUploadedFiledetailsForPartners as tpl on tpl.IndicatorID = DI.DynamicIndicatorID



其中tpl.fileID =（从tblUploadedFiledetailsForPartners中选择max（fileID）为tpl，其中tpl.IndicatorID = DI.DynamicIndicatorID） AND DI.UserId = 61

I have two tables
First table(DynamicIndicatorID(P),Question_ID,Quarter,Year,District)
P stands for primary key and F stands for foreign key
second table(fileID,FileName,DynamicIndicatorID(F),Datecreated)

Now In my first table I have four rows just like that

(1 , 1, 1, 1 , 1)
(2 , 1, 1, 2 , 1)
(3 , 1, 2, 1 , 1)
(4 , 1, 1, 1 , 2)
and in my second table I have three rows , my second table basically containing the files for each DynamicIndicatorID , it can contain Zero OR Many rows for each DynamicIndicatorID

just like below

second Table
(1,First.Docx,1,3/3/2001),
(2,New.Docx,1,3/3/2016),
(3,second.Docx,2,3/3/2001),

Now I want to write a query which can give me all records from first table {i.e. four Rows}
I want to add the FileName column as well so I need this record
(DynamicIndicatorID,Question_ID,Quarter,Year,District,FileName)
(1,1,1,1,1,New.Docx),
(2,1,1,2,1,Second.Docx),
(3,1,1,1,1,Nothing),
(4,1,1,1,1,Nothing),
I need to show all rows from first table and I want to show the latest file for each indicator just like above i have two files for DynamicIndicatorID=1 i am getting the latest one New.Docx , and for DynamicIndicatorID=2 I just have one so i am showing that and for DynamicIndicatorID 2,3 I have not any file so I have to show nothing , The mean idea is that I want to get all rows from First table and I want to show the latest file for each dynamicindicatorID , every DynamicIndicatorID must have at most one file which should be the latest one .

What I have tried:

i have tried that , it is giving the good result the latest file for each indicator but it is not showing the other two records which dont have any file
below is my query
SELECT Distinct DI.DynamicIndicatorID,Di.District,tpl.filenameName ,Di.Quarter,DI.District,DI.TFA,DI.TFB,DI.TFG,DI.TFW,DI.TFM from DynamicIndicatorsTargetsForPu
AS DI
Left Outer Join tblUploadedFiledetailsForPartners as tpl on tpl.IndicatorID=DI.DynamicIndicatorID

Where tpl.fileID=(Select max(fileID) from tblUploadedFiledetailsForPartners as tpl where tpl.IndicatorID=DI.DynamicIndicatorID) AND DI.UserId=61

推荐答案

尝试执行此操作：



SELECT DI.DynamicIndicatorID，DI。来自DynamicIndicatorsTargetsForPu的Question_ID，DI.Quarter，DI.Year，DI.District，tpl.FileName

AS DI

左外连接tblUploadedFiledetailsForPartners为tpl.DynamicIndicatorID = DI上的tpl。 DynamicIndicatorID

AND tpl.FileId in（从tb中选择max（tb1.FileId） lUploadedFiledetailsForPartners as tb1 where tb1.DynamicIndicatorId = DI.DynamicIndicatorId）



希望这会有所帮助!!

Try executing this:

SELECT DI.DynamicIndicatorID,DI.Question_ID,DI.Quarter,DI.Year,DI.District,tpl.FileName from DynamicIndicatorsTargetsForPu
AS DI
Left Outer Join tblUploadedFiledetailsForPartners as tpl on tpl.DynamicIndicatorID=DI.DynamicIndicatorID
AND tpl.FileId in (select max(tb1.FileId) from tblUploadedFiledetailsForPartners as tb1 where tb1.DynamicIndicatorId = DI.DynamicIndicatorId)

Hope this helps!!

这篇关于如何从SQL中的两个表中获取不同的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！