SQL-不同的一个Col，选择多个其他？ [英] SQL - Distinct One Col, Select Multiple other?

问题描述

我一直试图低估编写MS SQL 2005查询的最佳方法，该方法几乎可以执行以下操作...

 从表1选择不同的col1，col2，col3 
 
  

我基本上想执行在col1上不同，但我不希望在Col2 / 3上使用Dististinc，我只想要这些值。

我知道我无法以这种方式编写查询，

有人能指出我正确的方向吗？我确实尝试过将值重新加入，但这没有用，因为我必须在不重复选择中指定额外加入的cols，反过来又对这些值执行了不重复，即。

 从表1中选择不同的t1.col1，t2.col2，t3.col3 
 t1 
右联接（从表1中选择col1，col2，col3）t2 $ b t1.col1上的$ b = t2.col1 
  

经过编辑以更好地解释。.

 选择不同的t1.Hostname，t2.IP，t2.ActionDateTime，t2.Action 
从tblUserActions t1 
右加入（从tblUserActions中选择主机名，IP，ActionDateTime）t2上的t2 
.Hostname = t2.Hostname 
  

基本上，该表是成千上万个用户操作的列表，并且我试图在主机名上列出不同的名称，因此我应该只说10行，因为那是多少个不同的主机名。然后根据这些主机名，我还想将最新的记录数据加入返回的行中，所以我想返回：

 主机名，IP，ActionDateTime，操作
 1 Host1，165.123.123.1，2012-06-14 02:07:08，登录
 2 Host2，165.123.123.2，2012-06-14 03： 07:08，注销
 3 Host3，165.123.123.3，2012-06-14 04:07:08，登录
 4 Host4，165.123.123.4，2012-06-14 05:07:08，注销
等... 
  

任何帮助/指针都很棒！欢呼。

解决方案

听起来，我想这就是您要追求的：

 与CTE为
（选择主机名，
 IP，
 ActionDate，
 Action，
 ROW_NUMBER（ ）OVER（按主机名排序或按ActionDate DESC排序）AS RowNumber 
从表
）
选择主机名，
 IP，
 ActionDate，
动作
 FROM CTE 
 WHERE RowNumber = 1 
  

这将仅返回主机名的唯一值，则其他列返回的值基于 ROW_NUMBER（）窗口函数中的 ORDER BY 子句。 / p>

您可能需要更改 ORDER BY 以适合您的确切要求，我认为最新的操作可能是最有可能的

I've been trying to understate the best way to write an MS SQL 2005 query which pretty much does the following...

select distinct col1, col2, col3
from table1

Basically I want to perform Distinct on col1 but I dont want Dististinc on Col2/3, I just want there values.

I understand its not possible to write the query this way as I think I read the Distinct is applied to the row and not the col?

Could anyone please point me in the right direction? I did try right joing the values back in but this didnt work as I had to specify the extra joined cols in the distinct select which in turn performed the distinct on these, i.e..

select distinct t1.col1, t2.col2, t3.col3
from table1 t1
right join (select col1, col2, col3 from table1) t2
on t1.col1 = t2.col1

Edited to explain better..

select distinct t1.Hostname, t2.IP, t2.ActionDateTime, t2.Action
from tblUserActions t1
right join (select Hostname, IP, ActionDateTime from tblUserActions) t2
on t1.Hostname = t2.Hostname

Basically this table is a list of thousands of user actions and im trying to list distinct on the Hostname so I should only receive say 10 rows as thats how many different Hostnames there are. Then based on these hostnames I want to also join the most recent record data to the rows returned, so I'd like to return:

  Hostname, IP, ActionDateTime, Action
1 Host1, 165.123.123.1, 2012-06-14 02:07:08, Logon
2 Host2, 165.123.123.2, 2012-06-14 03:07:08, Logoff
3 Host3, 165.123.123.3, 2012-06-14 04:07:08, Logon
4 Host4, 165.123.123.4, 2012-06-14 05:07:08, Logoff
etc...

Any help/pointers would be great! Cheers.

解决方案

By the sounds of it I think this is what you are after:

WITH CTE AS
(   SELECT  HostName,
            IP,
            ActionDate,
            Action,
            ROW_NUMBER() OVER(PARTITION BY HostName ORDER BY ActionDate DESC) AS RowNumber
    FROM    Table
)
SELECT  HostName,
        IP,
        ActionDate,
        Action
FROM    CTE
WHERE   RowNumber = 1

This will return only unique values for host name, then the values returned the other columns are based on the ORDER BY clause in the ROW_NUMBER() Windowed function.

You may need to alter the ORDER BY to suit your exact reqirements, I assumed latest action was probably the most likely.

这篇关于SQL-不同的一个Col，选择多个其他？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！