代码之家 › 专栏 › 技术社区 › Fez Vrasta

检查行的较大值,然后按ID对具有相同值的每一行进行分组

sql-server sql

Fez Vrasta · 技术社区 · 12 年前

我有这个数据库:

âââââââââââ¦âââââââ¦âââââââââââ¦ââââââââââââ¦âââââ¦ââââââââââ
â ID_Elab â Step â ID_Progr â ID_Causal â GI â Minutes â
â ââââââââââ¬âââââââ¬âââââââââââ¬ââââââââââââ¬âââââ¬ââââââââââ£
â       8 â    1 â        8 â        19 â  0 â     480 â
â       8 â    2 â     1391 â        19 â  0 â     480 â
â       8 â    3 â     1781 â        19 â  0 â     480 â
â      10 â    1 â       10 â        50 â  0 â     480 â
â      10 â    1 â       43 â        14 â  0 â     210 â
â      10 â    2 â       99 â        50 â  0 â     480 â
â      10 â    2 â      100 â        14 â  0 â     210 â
â      10 â    3 â      124 â        50 â  0 â     480 â
â      10 â    3 â      125 â        72 â  0 â     120 â
â      10 â    3 â      126 â        73 â  0 â      90 â
â      11 â    1 â        8 â        19 â  0 â     480 â
â      11 â    2 â     1391 â        19 â  0 â     480 â
âââââââââââ©âââââââ©âââââââââââ©ââââââââââââ©âââââ©ââââââââââ

我需要检查每组ID,哪个更大 Step 值,然后使用该值选择特定组的每一行 步 价值

上表将变为:

âââââââââââ¦âââââââ¦âââââââââââ¦ââââââââââââ¦âââââ¦ââââââââââ
â ID_Elab â Step â ID_Progr â ID_Causal â GI â Minutes â
â ââââââââââ¬âââââââ¬âââââââââââ¬ââââââââââââ¬âââââ¬ââââââââââ£
â       8 â    3 â     1781 â        19 â  0 â     480 â
â      10 â    3 â      124 â        50 â  0 â     480 â
â      10 â    3 â      125 â        72 â  0 â     120 â
â      10 â    3 â      126 â        73 â  0 â      90 â
â      11 â    2 â     1391 â        19 â  0 â     480 â
âââââââââââ©âââââââ©âââââââââââ©ââââââââââââ©âââââ©ââââââââââ

我试着跟随 this question 这是我的结果查询:

SELECT *
FROM testVela  a
JOIN (
            SELECT ID_Elab, MAX(Step) AS Step, ID_Progr, ID_Causal, GI, Minutes
            FROM testVela 
            GROUP BY ID_Elab, ID_Progr, ID_Causal, Minutes
        ) b
ON a.ID_Elab = b.ID_Elab AND a.Step = b.Step

但是这个查询返回了完全错误的信息。。。我该怎么办?

6 回复 | 直到 12 年前

Felix Pamittan 12 年前

create table #test_table(
    ID_Elab int,
    Step int,
    ID_Progr int,
    ID_Casusal int,
    GI int,
    Minutes int
)
insert into #test_table
select 8, 1, 8, 19, 0, 480 union all 
select 8, 2, 1391, 19, 0, 480 union all 
select 8, 3, 1781, 19, 0, 480 union all 
select 10, 1, 10, 50, 0, 480 union all
select 10, 1, 43, 14, 0, 210 union all 
select 10, 2, 99, 50, 0, 480 union all 
select 10, 2, 100, 14, 0, 210 union all 
select 10, 3, 124, 50, 0, 480 union all 
select 10, 3, 125, 72, 0, 120 union all 
select 10, 3, 126, 73, 0, 90 union all 
select 11, 1, 8, 19, 0, 480 union all 
select 11, 2, 1391, 19, 0, 480 

;with cte as(
    select
        *,
        rn = rank() over(partition by ID_Elab order by step desc)
    from #test_table
)
select
    ID_Elab,
    Step,
    ID_Progr,
    ID_Casusal,
    GI,
    Minutes
from cte
where
    rn = 1

drop table #test_table

Sorin 12 年前

mysql> select * FROM tst where step = (select max(step) from tst as B where tst.ID_Elab = B.ID_Elab);
+---------+------+----------+-----------+----+---------+
| ID_Elab | Step | ID_Progr | ID_Causal | GI | Minutes |
+---------+------+----------+-----------+----+---------+
|       8 |    3 |     1781 |        19 |  0 |     480 |
|      10 |    3 |      124 |        50 |  0 |     480 |
|      10 |    3 |      125 |        72 |  0 |     120 |
|      10 |    3 |      126 |        73 |  0 |      90 |
|      11 |    2 |     1391 |        19 |  0 |     480 |
+---------+------+----------+-----------+----+---------+
5 rows in set (0.01 sec)

hallie 12 年前

你已经到了,只需要删除一些字段。

SELECT *
FROM testVela  a
JOIN (
            SELECT ID_Elab, MAX(Step) AS Step
            FROM testVela 
            GROUP BY ID_Elab
        ) b
ON a.ID_Elab = b.ID_Elab AND a.Step = b.Step

Naresh Pansuriya 12 年前

简单解决方案

select * 
from testVela tbl 
where tbl.Step = (select Max(Step) from testVela   subtbl where subtbl.ID_Elab = tbl.ID_Elab) order by  tbl.ID_Elab

Navneet 12 年前

在SQL中,您将尝试

select 
    ID_Elab,
    Step,
    ID_Progr,
    ID_Casusal,
    GI,
    Minutes from 
(SELECT rank() over (partition by ID_Elab order by step desc) as rn,*
FROM testVela) as tbl 
where rn=1

-2

AHiggins 12 年前

请尝试以下代码:

create table temp(
        ID_Elab int,
        Step int,
        ID_Progr int,
        ID_Casusal int,
        GI int,
        Minutes int
    )
    insert into temp
    select 8, 1, 8, 19, 0, 480 union all 
    select 8, 2, 1391, 19, 0, 480 union all 
    select 8, 3, 1781, 19, 0, 480 union all 
    select 10, 1, 10, 50, 0, 480 union all
    select 10, 1, 43, 14, 0, 210 union all 
    select 10, 2, 99, 50, 0, 480 union all 
    select 10, 2, 100, 14, 0, 210 union all 
    select 10, 3, 124, 50, 0, 480 union all 
    select 10, 3, 125, 72, 0, 120 union all 
    select 10, 3, 126, 73, 0, 90 union all 
    select 11, 1, 8, 19, 0, 480 union all 
    select 11, 2, 1391, 19, 0, 480 


    select 
        ID_Elab,
        Step,
        ID_Progr,
        ID_Casusal,
        GI,
        Minutes from 
    (SELECT row_number  over (partition by ID_Elab order by step desc) as rn,*
    FROM temp) as tbl 
    where rn=1
    drop table temp