SQL Server选择不同 [英] SQL Server Select Distinct

问题描述

我想写这样的查询：

对于有这些列的表：
ColA ColB ColC，ColD

先选择（ColA，ColB，ColC，ColD）
不同（ColB，ColC）
从表
按ColD排序

查询应该由ColD排序表，然后按照ColB和ColC的组合（它们可能具有不同的数据类型）对结果进行分组，并返回第一行（包含所有列的

在MS SQL Server 2005中可能如何？

解决方案

这听起来像你想'最大每组'。

一种方法是使用窗口函数 ROW_NUMBER 对每个组中的行进行编号，然后选择行号为1的那些行：

  SELECT ColA，ColB，ColC，ColD 
 FROM 
（
 SELECT 
 ColA，ColB， ColC，Co lD，
 ROW_NUMBER（由ColB分区，ColC按ORD排序）从
 FROM table1 
）T1 
 WHERE rn = 1 
  

I want to write a query like this:

For a table that has these columns: ColA ColB ColC, ColD

select first(ColA, ColB, ColC, ColD) distinct(ColB, ColC) from table order by ColD

The query is supposed to order the table by ColD, then group the results by the combination of ColB and ColC (they may have different data types) and returns the first rows (with all the columns of the table) in the groups.

How is it possible in MS SQL Server 2005?

解决方案

It sounds like you want 'max per group'.

One way is to use the windowing function ROW_NUMBER to number the rows in each group, and then select only those rows with row number 1:

SELECT ColA, ColB, ColC, ColD
FROM
(
    SELECT
         ColA, ColB, ColC, ColD,
         ROW_NUMBER(PARTITION BY ColB, ColC ORDER BY ColD) AS rn
    FROM table1
) T1
WHERE rn = 1

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