首页 文章

SQL Group通过查询从同一个表中选择数据

提问于
浏览
3

我的表有以下数据,

ID name  devID
1  abc    101
2  def    111
3  ghi    121
4  abc    102
5  def    110

我想根据以下条件选择行(ID,name,devID):

一个 . 名称abc的devID值已增加1,因此结果中只显示更高值的记录(仅102)

湾名称def的devID值减1,应显示所有记录(111和110)

此外,我们将继续添加不同行的记录,每个名称在表中不会超过2行或最多3行,因此上述条件应始终为true .

请帮我解决这个问题 . 提前致谢 .

4 回答

  • 1

    如果我正确理解了您的问题,以下内容可以帮助您解决问题:

    SELECT *
    FROM table_data AS a
    WHERE a.devid >=
        (SELECT DEVID
         FROM table_data AS C
         WHERE c.ID =
             (SELECT max(b.ID)
              FROM table_data AS b
              GROUP BY b.name HAVING b.name = a.name)) ;
    

    SQL Fiddle: http://www.sqlfiddle.com/#!3/b14513/18

    此代码仅导致 DEVID 更大(或相等)的行与最后插入的名称为 NameDEVID 的行显示 .

    Results

    ID  NAME    DEVID
    2   def     111
    3   ghi     121
    4   abc     102
    5   def     110
    

    Update (查询可以进一步简化):

    SELECT *
    FROM table_data AS a
    WHERE a.devid >=
        (SELECT DEVID
         FROM table_data AS C
         WHERE c.ID =
             (SELECT max(b.ID)
              FROM table_data AS b
              where b.name = a.name)) ;
    

    索引也应该放在ID和devID中 .

  • 2

    我使用了增量方法 . 我没有真正看到另一种选择 . 这回报你需要的东西我相信:

    create table #t1
    (
        ID int identity,
        name varchar(3),
        devID int
    )
    
    insert into #t1(name,devID)
    values('abc',101),('def',111),('ghi',121),('abc',102),('def',110)
    
    
    create table #t2
    (
        ID int,
        name varchar(3),
        devID int
    )
    
    declare @count int = 1,
        @name1 varchar(3)
    while @count <= (select MAX(ID) from #t1)
    begin--1
        set @name1 = (select name from #t1 where ID = @count)
        if (@name1 not in (select distinct name from #t2)) or ((select devID from #t1 where ID = @count) < (select devID from #t2 where name = @name1))
        begin--2
            insert into #t2
                select *
                from #t1
                where ID = @count
        end--2
        else
        begin--2
            update #t2
                set devID = (select devID from #t1 where ID = @count)
                where name = @name1
        end--2
    
        set @count+=1
    end--1
    
    select *
    from #t2
    
    drop table #t1
    drop table #t2
    

    编辑:结果:

    ID          name devID
    ----------- ---- -----------
    1           abc  102
    2           def  111
    3           ghi  121
    5           def  110
    
    (4 row(s) affected)
    
  • 2

    使用 INNER JOIN 本身和 UNION 结果可能是一个很好的方法 .

    SQL Fiddle

    /* select all rows that match criteria A */
    SELECT d2.ID, d2.name, d2.devID
    FROM   data d1
           INNER JOIN data d2 ON d2.devID = d1.devID + 1 
                                 AND d2.ID > d1.ID
    UNION
    /* select first rows that match criteria B */
    SELECT d1.ID, d1.name, d1.devID
    FROM   data d1
           INNER JOIN data d2 ON d2.devID = d1.devID - 1 
                                 AND d2.ID > d1.ID
    UNION
    /* select second rows that match criteria B */
    SELECT d2.ID, d2.name, d2.devID
    FROM   data d1
           INNER JOIN data d2 ON d2.devID = d1.devID - 1 
                                 AND d2.ID > d1.ID
    
  • 2

    如果我理解正确,你只需要获得 latest devID(如下所示) .

    所以 why bother with Joins and stuff ,如果这个简单的方法也有效:

    SELECT DISTINCT(Name), (SELECT TOP 1 devID FROM Table t2 
    WHERE t2.Name=t1.Name Order By ID desc) FROM table t1
    

    你的记录:

    ID name  devID
    1  abc    101
    2  def    111
    3  ghi    121
    4  abc    102
    5  def    110
    

    您的预期结果(也用小提琴手检查)

    name  devID
    ghi    121
    abc    102
    def    110
    

相关问题