如何使用内部联接在SQL查询中区分一列的值 [英] How to make distinct for value of one column in SQL query with inner join

问题描述

我需要在内连接三个表中使一列不同

我需要使videoName与其他列不同

如何编写查询



请帮助



我尝试过：



SElECT distinct t1.MID，distinct t1.VideoName，t2。*，t3.UniID，t3.user_type from dbo.Material AS t1 INNER JOIN dbo.ModuleRelation as t2 ON t2.MID = t1 .MID INNER JOIN dbo.Users AS t3 ON t3.UniID = t2.UId其中t3.user_type ='Staff'和t1.IsDeleted = 0 AND t2.MID ='CM1034D';



和这个

SElECT与tbo不同的t1.MID，t4.VideoName，t2。*，t3.UniID，t3.user_type来自dbo。材料AS t1 INNER JOIN dbo.ModuleRelation为t2 ON t2.MID = t1.MID INNER JOIN dbo.Users AS t3 ON t3.UniID = t2.UId

INNER JOIN（选择max（MID）as MID，videoname来自dbo.Material group by Videoname）AS t4 ON t1.MID = t4.MId

其中t3.user_type ='Staff'和t1.IsDeleted = 0 AND t2.MID ='CM1034D ';



但是视频名称仍然重复

解决方案

只有一列时这种情况不同即选择'VideoName'。如果需要不同的'VideoName'和其他列，那么它将带来所有列的不同组合，而不是一列的不同。

使用group by而不是Distinct：_

  SElECT  t1.MID，t1.VideoName，t2。*，t3.UniID，t3.user_type 来自 dbo.Material  AS  t1  INNER   JOIN  dbo.ModuleRelation  As  t2  ON  t2.MID = t1 .MID  INNER   JOIN  dbo.Users  AS  t3  ON  t3.UniID = t2.UId 其中 t3.user_type = '  Staff' 和 t1.IsDeleted =  0   AND  t2.MID = '  CM1034D' 组 按 t1.VideoName; 

I need to make one column distinct in inner join three tables
I need to make videoName distinct only not other columns
how can I write the query

please help

What I have tried:

SElECT distinct t1.MID, distinct t1.VideoName, t2.*,t3.UniID,t3.user_type from dbo.Material AS t1 INNER JOIN dbo.ModuleRelation As t2 ON t2.MID=t1.MID INNER JOIN dbo.Users AS t3 ON t3.UniID=t2.UId where t3.user_type='Staff' and t1.IsDeleted = 0 AND t2.MID='CM1034D';

and this
SElECT distinct t1.MID, t4.VideoName, t2.*,t3.UniID,t3.user_type from dbo.Material AS t1 INNER JOIN dbo.ModuleRelation As t2 ON t2.MID=t1.MID INNER JOIN dbo.Users AS t3 ON t3.UniID=t2.UId
INNER JOIN (select max(MID) as MID,videoname from dbo.Material group by Videoname) AS t4 ON t1.MID=t4.MId
where t3.user_type='Staff' and t1.IsDeleted = 0 AND t2.MID='CM1034D';

but video name still duplicated

解决方案

Distinct in such condition will work when only one column i.e. 'VideoName' is selected. If distinct 'VideoName' is required along with other columns then it will bring distinct combination of all the columns instead of distinct of one column.

Use group by Instead of Distinct:_

SElECT t1.MID, t1.VideoName, t2.*,t3.UniID,t3.user_type from dbo.Material AS t1 INNER JOIN dbo.ModuleRelation As t2 ON t2.MID=t1.MID INNER JOIN dbo.Users AS t3 ON t3.UniID=t2.UId where t3.user_type='Staff' and t1.IsDeleted = 0 AND t2.MID='CM1034D' Group by t1.VideoName; 

这篇关于如何使用内部联接在SQL查询中区分一列的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！