通过为非空字段提供空值来进行分组

I have a table as follows

我有一张桌子如下

id name tid seq aname ds
1  a     xy  10  G     E
1  a     xz  20  G     E
1  a     az  30  G     E
1  b     wq  10  G     E
1  b     as  20  G     E
2  c     qw  10  G     E
2  c     sd  20  G     E  
1  a     fg  34  S     F

Now i want the o/p as follows

现在我想要o / p如下

id name tid seq  
1  a    az  30

1  b    as  20       
2  c    sd  20

My query is as follows

我的查询如下

select id,name,tid,max(seq) 
from table 
group by id,name where aname='G' and ds='E'; 

But iam getting null values in tid field.Where did i go wrong?Please help.Thank you

但是我在tid领域得到了空值。我哪里出错了?请帮忙。谢谢

1 个解决方案

#1

One way of doing this is by using a subquery which gets the maximum seq for every group of ID and Name. The result of the subquery is then joined back on the table itself provided that it match on three columns: ID, Name, and seq, to give you the correct value of tid.

实现此目的的一种方法是使用子查询,该子查询获取每组ID和名称的最大seq。然后,子查询的结果将连接回表本身,前提是它匹配三列:ID,Name和seq,以便为您提供正确的tid值。

SELECT  a.*
FROM    tableName a
        INNER JOIN
        (
            SELECT  id, name, max(seq) max_seq
            FROM    tableName
            GROUP   BY ID,name 
        ) b ON a.ID = b.ID AND
                a.seq = b.max_seq AND
                a.name = b.name

• SQLFiddle Demo

OUTPUT

╔════╦══════╦═════╦═════╗
║ ID ║ NAME ║ TID ║ SEQ ║
╠════╬══════╬═════╬═════╣
║  1 ║ a    ║ az  ║  30 ║
║  1 ║ b    ║ as  ║  20 ║
║  2 ║ c    ║ sd  ║  20 ║
╚════╩══════╩═════╩═════╝