如何在tsql中使用distinct [英] how use distinct in tsql

问题描述

iam将此表与此查询一起使用

 FUserName FScoreID FGrantedScoreID 
 u157 2 6 
 u157 2 7 
 u4 2 10 
 u4 2 11 
 u4 2 12 
 u6 2 14 
 u6 2 15 
 u6 2 18 

我需要这个结果

 <   pre     lang   =   HTML >  
 FUserName FScoreID FGrantedScoreID 
 u157 2 6 
 u4 2 10 
 u6 2 14 
 
 <   / pre  >  

此查询

  SELECT   DISTINCT  FUserName，FScoreID， [FGrantedScoreID] 
 
  FROM  [HrstPortal]。[dbo]。[VDB_Scores] 
 其中 FScoreID = 2 

解决方案

上述查询只返回一行，因为整个表中的FScoreID是相同的。



您可以创建两个查询。可以选择DISTINCT数据，第二个可以在循环内使用，以查看每个DISTINCT数据的数据。



试试这个，

  SELECT   DISTINCT  FUserName，[FGrantedScoreID]   -   选择 
  FROM  [HrstPortal]。[dbo]。[VDB_Scores]   -  -   表 
  WHERE  FScoreID = 2   -   约束 

现在这个结果，您可以循环并运行FUserName值的查询，并获取每个DISTINCT结果的所有FScoreID值;正如你在HTML表格中所做的那样。

问题是没有简单的方法来获取每个组的第一行，而没有任何表格中的顺序除非添加了ORDER BY子句，否则SQL可以自由地按照它认为合适的任何顺序返回行。

但是......请看这里： http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-组 [ ^ ]

试试这个：

 选择 t1。* 来自 tablename t1 
  inner   join  
（
 选择 FUserName，MIN（FGrantedScoreID）MinFGrantedScoreID 来自 tablename  group   by  FUserName 
） t2 
  on  t1.FUserName = t2.FUserName 
 其中 t1.FGrantedScoreID = t2.MinFGrantedScoreID 

iam use this table with this query

FUserName	FScoreID	FGrantedScoreID
u157	            2                6
u157	            2	              7
u4	            2	             10
u4	            2	             11
u4	            2	             12
u6	            2	            14
u6	            2	            15
u6	            2	            18

iam need this result

<pre lang="HTML">
FUserName   FScoreID    FGrantedScoreID
u157            2                6
u4              2                10
u6              2                14

</pre>

this query

SELECT DISTINCT FUserName,FScoreID , [FGrantedScoreID]

  FROM [HrstPortal].[dbo].[VDB_Scores]
 where FScoreID=2

解决方案

The above query would return only one row, because the FScoreID is same throughout the table.

You can create two queries. One would select the DISTINCT data, and the second one can be used inside a loop, to see for the data present for each of this DISTINCT data.

Try this,

SELECT DISTINCT FUserName, [FGrantedScoreID] -- Select
FROM [HrstPortal].[dbo].[VDB_Scores] -- Table 
WHERE FScoreID=2 -- Constraint

Now for this result, you can loop and run a query for the FUserName value, and get all of the FScoreID values for each of the DISTINCT result; as you're doing in the HTML table.

The problem is that there is no easy way to get the "first" row of each "group" without having anything in the table to order it by - since SQL is at liberty to return rows in any order it sees fit unless an ORDER BY clause is added.
But...see here: http://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group[^]

Try this:

select t1.* from tablename t1
inner join
(
  select FUserName,MIN(FGrantedScoreID) MinFGrantedScoreID from tablename group by FUserName
) t2
on t1.FUserName=t2.FUserName
where t1.FGrantedScoreID=t2.MinFGrantedScoreID

这篇关于如何在tsql中使用distinct的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！