帮助选择MS Sql select语句

I have a table like the following table:

我有如下表格:

UserID  Num1    Num2    Code    Name    Cat
7293    32:16.0 50:22.0 3   Jim 33
7293    32:16.0 59:28.0 4   Jim 12
7316    32:16.0 55:32.0 4   Karen   33
7316    32:16.0 28:31.0 4   Karen   7
7287    32:16.0 01:27.0 2   Mike    33
7299    32:16.0 18:53.0 4   Sue 16
7302    32:17.0 54:54.0 4   Bill    28
7302    32:17.0 01:26.0 4   Bill    10
7302    32:17.0 01:26.0 3   Bill    32

and I am trying to select unique rows. I need the output to be this:

我试着选择唯一的行。我需要输出如下:

UserID  Num1    Num2    Code    Name    Cat
7293    32:16.0 50:22.0 3   Jim 33
7316    32:16.0 28:31.0 4   Karen   7
7287    32:16.0 01:27.0 2   Mike    33
7299    32:16.0 18:53.0 4   Sue 16
7302    32:17.0 54:54.0 4   Bill    28

I am no SQL expert so any help would be greatly appreciated.

我不是SQL专家，所以我非常感谢您的帮助。

I tried using a SELECT statement with DISTINCT, but that only gives me one column, I need the entire row. If I put multiple fields in the SELECT with the DISTINCT it returns all the rows.

我尝试使用一个SELECT语句，但它只给了我一个列，我需要整个行。如果我在SELECT中放入多个字段，它会返回所有行。

Thanks for the assistance.

谢谢你的帮助。

Ok, to answer some of the replies I recieved:

好，来回答我的一些回复:

1) I have tried many queries, but all based around: SELECT DISTINCT UserID FROM MyTable SELECT DISTINCT * FROM MyTable SELECT DISTINCT UserID, Num1, Num2, Code, Name, Cat FROM MyTable

1)我尝试过很多查询，但是都是基于:从MyTable中选择不同的UserID从MyTable中选择不同的*从MyTable中选择不同的UserID, Num1, Num2, Code, Name, Cat

Again, I am not a SQL Expert and everything I read about DISTINCT there is no real example of whatI need.

再说一次，我不是一个SQL专家，我所读到的关于不同的东西都没有真正需要的例子。

2) However, reading the replies gave me a thought, since I really only need a few columns, such as UserID, Name,

2)然而，阅读回复给了我一个想法，因为我真的只需要几个列，比如UserID, Name，

Can I use DISTINCT and specify those columns only? Hmm, I wil give it a try.

我可以使用不同的并指定那些列吗?嗯，我想试试。

3) I am not sure what the Min, Max stuff is in the replies, but I will try it and hopefully in the process gain a better understanding of the SQL syntax.

3)我不确定答复中包含的最小值和最大值，但我将尝试一下，希望在这个过程中更好地理解SQL语法。

Thanks again for the help.

再次感谢您的帮助。

5 个解决方案

#1

you need to aggregate the fields you don't want to group on, like:

您需要聚合不想分组的字段，比如:

select UserID,  min(Num1),  min(Num2), min(Code), min(Name), max(Cat)
from tbl
group by userID

#2

I don't know if it is the best way, but one way is to use grouping:

我不知道这是否是最好的方法，但一种方法是使用分组:

select UserId, min(Num1), min(Num2), min(Code), min(Name), min(Cat)
from tbl group by UserId

Well, this will give you Jim 12 instead of Jim 33, but I guess you can figure out an aggregate function that gives you the right row.

这将得到吉姆12而不是吉姆33，但我猜你可以算出一个集合函数它会给出正确的行。

#3

1) If this is homework, please mark it as such, and then show some of your prior attempts (people are much more willing to help those who have put in honest effort and are stuck, as opposed to looking for free answers). You WILL get more respect, help, and credit if you've shown that you've exhausted every possibility you can think of.

1)如果这是家庭作业，请把它标记成这样，然后展示你之前的一些尝试(人们更愿意帮助那些付出诚实努力却被困住的人，而不是寻找免费的答案)。如果你已经展示出你已经用尽了你所能想到的每一种可能性，你将会得到更多的尊重、帮助和信任。

2) Is the exact input and exact output correct? It strikes me as strange that Karen's Cat was 7 and not 33 in the 'answer'. Each other user has their first value selected.

2)准确的输入和准确的输出是否正确?奇怪的是，凯伦的猫只有7岁，而不是33岁。每个其他用户都选择了他们的第一个值。

#4

It would appear that you want the "TOP 1" result for each UserID.

看起来您想要每个用户id的“TOP 1”结果。

To do so on the code side would be easier than SQL side but it is possible.

在代码方面这样做比在SQL方面更容易，但这是可能的。

One such way is to Generate a list of distinct UserID's:

一种这样的方法是生成一个不同用户id的列表:

SELECT DISTINCT UserID FROM @YourTable

Use a cursor to iterate over each ID and insert each "TOP 1" record into a temp table:

使用光标遍历每个ID，并将每个“TOP 1”记录插入临时表:

DECLARE @TempTable TABLE (
    UserID INT, 
    Num1 VARCHAR(10), 
    Num2 VARCHAR(10), 
    Code INT, 
    Name VARCHAR(10), 
    Cat INT
)

DECLARE @ID INT
DECLARE MyCursor CURSOR FOR
SELECT DISTINCT UserID FROM @YourTable

OPEN MyCursor
FETCH NEXT FROM MyCursor INTO @ID

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @TempTable
    SELECT TOP 1 * FROM @YourTable WHERE UserID = @ID   

    FETCH NEXT FROM MyCursor
    INTO @ID
END

CLOSE MyCursor
DEALLOCATE MyCursor

Now, your desired records should be in @TempTable

现在，您希望的记录应该在@TempTable中。

SELECT * FROM @TempTable

7287    32:16.0 01:27.0 2   Mike    33
7293    32:16.0 50:22.0 3   Jim 33
7299    32:16.0 18:53.0 4   Sue 16
7302    32:17.0 54:54.0 4   Bill    28
7316    32:16.0 55:32.0 4   Karen   33

*Note: My datatype mappings probably don't match yours.

*注意:我的数据类型映射可能与您的不匹配。

#5

This query makes no assumption as to what ranks as the row from each user you want. To rank the query to accept the 'certain' top value you will have to alter the order by clause in the 'OVER' statement for the 'ROW_NUMBER()' function

这个查询不假设每个用户的行是多少。要对查询进行排序以接受'certain' top值，您必须在'OVER'语句中修改'ROW_NUMBER()'函数的'OVER'语句中的order by子句

with partTable (rw_num,UserID,Num1,Num2,Code,Name,Category) as 
    (select 
    ROW_NUMBER() over(partition by UserID order by UserID) as rw_num
    ,UserID
    ,Num1
    ,Num2
    ,Code
    ,Name
    ,Category
    from table_1)
    select 
    UserID
    ,Num1
    ,Num2
    ,Code
    ,Name
    ,Category
    from partTable where rw_num = 1

#1