web-gelistirme-sc.com

SQL sadece bir sütunda maksimum değere sahip satırları seçin

Belgeler için bu tabloya sahibim (burada basitleştirilmiş sürüm):

+------+-------+--------------------------------------+
| id   | rev   | content                              |
+------+-------+--------------------------------------+
| 1    | 1     | ...                                  |
| 2    | 1     | ...                                  |
| 1    | 2     | ...                                  |
| 1    | 3     | ...                                  |
+------+-------+--------------------------------------+

Kimlik başına bir satır ve yalnızca en büyük rev'i nasıl seçerim?
Yukarıdaki verilerle sonuç iki satır içermelidir: [1, 3, ...] ve [2, 1, ..]. Ben MySQL kullanıyorum.

Şu anda, resultset'teki eski devirleri tespit etmek ve üzerine yazmak için while döngüsündeki kontrolleri kullanıyorum. Ancak bu sonucu elde etmek için tek yöntem bu mu? Bir SQL çözümü yok mu?

Güncelle
Cevapların öne sürdüğü gibi bir SQL çözümü ve burada bir sqlfiddle demosu

Güncelleme 2
Yukarıdaki sqlfiddle 'ı ekledikten sonra, sorunun reddedilme oranının cevapların yükseltme oranını aştığını fark ettim. Bu niyet olmamıştır! Keman cevaplara, özellikle de kabul edilen cevaplara dayanır.

994
Majid Fouladpour

İlk bakışta...

İhtiyacınız olan tek şey MAX toplama işleviyle birlikte bir GROUP BY cümlesi:

SELECT id, MAX(rev)
FROM YourTable
GROUP BY id

Asla o kadar basit değil mi?

Az önce content sütununa da ihtiyacınız olduğunu fark ettim.

Bu, SQL'de çok yaygın bir sorudur: Bazı grup tanımlayıcı başına bir sütunda bazı maksimum değere sahip satır için tüm verileri bulun. Bunu kariyerim boyunca çok duydum. Aslında şu anki işimin teknik röportajında ​​cevapladığım sorulardan biriydi. 

Aslında, o kadar yaygın ki, StackOverflow topluluğu yalnızca şu gibi sorularla başa çıkmak için tek bir etiket yarattı: - grup başına en büyük-n-grup .

Temel olarak, bu sorunu çözmek için iki yaklaşımınız var:

Basit group-identifier, max-value-in-group ile katılma Alt sorgu

Bu yaklaşımda, öncelikle bir alt sorguda group-identifier, max-value-in-group (yukarıda çözülmüş) bulacaksınız. Sonra, tablonuzu hem group-identifier hem de max-value-in-group'da eşitlik içeren alt sorguya katın:

SELECT a.id, a.rev, a.contents
FROM YourTable a
INNER JOIN (
    SELECT id, MAX(rev) rev
    FROM YourTable
    GROUP BY id
) b ON a.id = b.id AND a.rev = b.rev

Sola kendi kendine katılıyor, bir araya getirme koşulları ve filtreler

Bu yaklaşımda, masayı kendisiyle birleştirmeyi bıraktınız. Elbette eşitlik group-identifier 'de gider. Ardından, 2 akıllı hareket: 

  1. İkinci birleşme koşulu, sol tarafın sağdaki değerden düşük olması
  2. 1. adımı yaptığınızda, gerçekte maksimum değere sahip satır (lar) sağ tarafta NULL olacaktır (bu bir LEFT JOIN, hatırladın mı?). Ardından, yalnızca sağ tarafın NULL olduğu satırları gösteren birleştirilen sonucu filtreleriz.

Yani sonunda:

SELECT a.*
FROM YourTable a
LEFT OUTER JOIN YourTable b
    ON a.id = b.id AND a.rev < b.rev
WHERE b.id IS NULL;

Sonuç

Her iki yaklaşım da aynı sonucu verir. 

max-value-in-group için group-identifier ile iki satırınız varsa, her iki satır da her iki yaklaşımda da sonuçlanır.

Her iki yaklaşım da SQL ANSI uyumludur, bu nedenle "tadına" bakmaksızın en sevdiğiniz RDBMS ile çalışacaktır.

Her iki yaklaşım da performans açısından kolaydır, ancak kilometreniz değişebilir (RDBMS, DB Yapısı, Dizinler, vb.). Öyleyse birinden diğerine bir yaklaşım seçtiğinizde, benchmark. Ve sizin için en anlamlı olanı seçtiğinizden emin olun.

1579
Adrian Carneiro

Tercihim mümkün olduğunca az kod kullanmak ...

IN.__ kullanarak bunu yapabilirsiniz.

SELECT * 
FROM t1 WHERE (id,rev) IN 
( SELECT id, MAX(rev)
  FROM t1
  GROUP BY id
)

aklıma daha az karmaşık ... okumak ve bakımı daha kolay.

204
Kevin Burton

Yine bir başka çözüm, ilişkili bir alt sorgu kullanmaktır:

select yt.id, yt.rev, yt.contents
    from YourTable yt
    where rev = 
        (select max(rev) from YourTable st where yt.id=st.id)

(İd, rev) dizinine sahip olmak alt sorguyu neredeyse basit bir arama gibi işler ...

Aşağıdakiler, AdrianCarneiro'nun cevabındaki (alt sorgu, sol katılım), ~ 1 milyon kayıtlık InnoDB tablosu ile MySQL ölçümlerine dayanan, grup büyüklüğü: 1-3 arasındaki çözümlerle yapılan karşılaştırmalardır.

Tam tablo taramaları için alt sorgu/sol birleştirme/ilişkili zamanlamalar 6/8/9 olarak birbirleriyle ilişkiliyken, doğrudan aramalar veya toplu işlem söz konusu olduğunda (id in (1,2,3)), alt sorgu diğerlerinden çok daha yavaştır (alt sorgunun yeniden sıralanması nedeniyle). Bununla birlikte, leftjoin ile ilişkili çözümler arasındaki hızı ayırt edemedim.

Son bir not, leftjoin gruplar halinde n * (n + 1)/2 birleşimi yarattığından, performansı grupların boyutundan büyük oranda etkilenebilir ...

66
Vajk Hermecz

Hiçbir cevap, SQL pencere işlevi çözümü önermediği için kızgınım:

SELECT a.id, a.rev, a.contents
  FROM (SELECT id, rev, contents,
               ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
          FROM YourTable) a
 WHERE a.rank = 1 

SQL standardı ANSI/ISO Standardı SQL: 2003'te eklendi ve daha sonra ANSI/ISO Standardı SQL: 2008 ile genişletildi, pencere (veya pencereleme) fonksiyonları şimdi tüm büyük satıcılarda mevcut. Beraberlikle baş etmek için daha fazla rütbe fonksiyonu vardır: RANK, DENSE_RANK, PERSENT_RANK.

54
topchef

Performans için kefil olamam, ancak işte Microsoft Excel'in sınırlamalarından ilham alan bir numara. Bazı iyi özelliklere sahip

İYİ ŞEYLER

  • Beraberlik olsa bile, sadece bir "maksimum kaydın" geri dönmesini zorlamalıdır (bazen yararlıdır)
  • Katılma gerektirmez

YAKLAŞIMI

Biraz çirkin verevsütununun geçerli değerlerinin aralığı hakkında bir şeyler bilmenizi gerektirir.revsütununun ondalık sayılar da dahil olmak üzere 0.00 ile 999 arasında bir sayı olduğunu bildiğimizi varsayalım, ancak ondalık sayının sağında yalnızca iki basamak olacak (örneğin 34.17 geçerli bir değer olacaktır) ).

İşin özü, birincil karşılaştırma alanını istediğiniz verilerle birlikte birleştiren/paketleyen dizeyle tek bir sentetik sütun oluşturmanızdır. Bu şekilde, SQL'in MAX () toplama işlevini tüm verileri döndürmeye zorlayabilirsiniz (çünkü tek bir sütuna paketlenmiştir). O zaman verileri açmak zorundasın.

İşte SQL ile yazılmış yukarıdaki örnekle nasıl göründüğü

SELECT id, 
       CAST(SUBSTRING(max(packed_col) FROM 2 FOR 6) AS float) as max_rev,
       SUBSTRING(max(packed_col) FROM 11) AS content_for_max_rev 
FROM  (SELECT id, 
       CAST(1000 + rev + .001 as CHAR) || '---' || CAST(content AS char) AS packed_col
       FROM yourtable
      ) 
GROUP BY id

Paketleme,revsütunu,revdeğerinden bağımsız olarak, bilinen bir karakter uzunluğu sayısına zorlayarak başlar.

  • 3.2 1003.201 olur
  • 57, 1057.001 olur
  • 923.88, 1923.881 olur

Bunu doğru yaparsanız, iki sayının dize karşılaştırması, iki sayının sayısal karşılaştırmasıyla aynı "maks" değerini vermelidir ve alt dize işlevini (bir formda veya başka bir şekilde kullanılabilir) kullanarak orijinal sayıya geri dönmek kolaydır. her yerde).

44
David Foster

Bunun en kolay çözüm olduğunu düşünüyorum:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • SEÇ *: Tüm alanları döndür.
  • Çalışantan: Tablo arandı.
  • (SELECT * ...) alt sorgusu: Maaşa göre sıralanmış tüm kişileri döndür.
  • GROUP BYyeesub.Salary:: Her çalışanın en üst sıradaki, Maaş sırasını döndürülen sonuç olmaya zorlayın.

Sadece bir satıra ihtiyacınız olursa, daha da kolay:

SELECT *
FROM Employee
ORDER BY Employee.Salary DESC
LIMIT 1

Ayrıca, ayrılmanın, anlaşılmanın ve başka amaçlarla değiştirmenin en kolay olduğunu düşünüyorum:

  • SİPARİŞ KİŞİ Çalışan. Ücret Tarifesi: Sonuçları önce en yüksek maaşlı maaş ile verin.
  • SINIR 1: Sadece bir sonuç döndür.

Bu yaklaşımı anlamak, bu benzer sorunlardan herhangi birini çözmek önemsiz hale gelir: en düşük maaşlı çalışanı al (DESC'yi ASC'ye değiştir), ilk on kazanç çalışanını al (SINIR 1'den SINIR 10'a değiştir), başka bir alana göre sırala (ORDER BY değiştir Çalışan.Özel Sipariş: Çalışan Ücreti), vb.

25
HoldOffHunger

Böyle bir şey mi?

SELECT yourtable.id, rev, content
FROM yourtable
INNER JOIN (
    SELECT id, max(rev) as maxrev FROM yourtable
    WHERE yourtable
    GROUP BY id
) AS child ON (yourtable.id = child.id) AND (yourtable.rev = maxrev)
18
Marc B

Bu, bu sorunla ilgili en popüler soru olduğundan, burada da başka bir yanıt göndereceğim:

Bunu yapmanın daha basit bir yolu var gibi görünüyor (ama sadece MySQL'de):

select *
from (select * from mytable order by id, rev desc ) x
group by id

Lütfen Bohemian kullanıcısına verilen cevabı kredilendirin in bu soru bu soruna böyle özlü ve zarif bir cevap vermek için.

EDIT: bu çözüm birçok kişi için çalışsa da, uzun vadede istikrarlı olmayabilir, çünkü MySQL GROUP BY ifadesinin GROUP BY listesinde olmayan sütunlar için anlamlı değerler getireceğini garanti etmiyor. kendi riski

6
Yura

Bu sorun için NOT EXIST tabanlı bir çözüm kullanmayı seviyorum:

SELECT id, rev
FROM YourTable t
WHERE NOT EXISTS (
   SELECT * FROM YourTable t WHERE t.id = id AND rev > t.rev
)
6
Bulat

Bahsettiğim neredeyse hiç görmediğim üçüncü bir çözüm MySQL'e özgü ve şöyle görünüyor:

SELECT id, MAX(rev) AS rev
 , 0+SUBSTRING_INDEX(GROUP_CONCAT(numeric_content ORDER BY rev DESC), ',', 1) AS numeric_content
FROM t1
GROUP BY id

Evet berbat görünüyor (ipe ve geriye dönüşe vb.) Ama benim tecrübeme göre bu genellikle diğer çözümlerden daha hızlı. Belki de sadece kullanım durumlarım için, ama milyonlarca kayıt ve birçok benzersiz kimliğe sahip masalarda kullandım. Belki de MySQL'in diğer çözümleri optimize etmekte oldukça kötü olmasından kaynaklanmaktadır (en azından bu çözümü bulduğumda 5.0 gün içinde).

Önemli bir şey, GROUP_CONCAT'in oluşturabileceği dize için maksimum uzunluğa sahip olmasıdır. Muhtemelen group_concat_max_len değişkenini ayarlayarak bu sınırı yükseltmek istersiniz. Çok sayıda satırınız varsa, bunun ölçeklendirme için bir sınır olacağını unutmayın.

Her neyse, içerik alanınız zaten metinse, yukarıdakiler doğrudan çalışmaz. Bu durumda, muhtemelen\0 gibi, farklı bir ayırıcı kullanmak isteyebilirsiniz. Ayrıca, group_concat_max_len limitini daha çabuk çalıştırırsınız.

5
Jannes

Sanırım bunu istiyor musun?

select * from docs where (id, rev) IN (select id, max(rev) as rev from docs group by id order by id)  

SQL Fiddle: Burayı kontrol et

4
Abhishek Rana

Select deyiminde birçok alanınız varsa ve optimize edilmiş kod aracılığıyla bu alanların tümü için en son değeri istiyorsanız:

select * from
(select * from table_name
order by id,rev desc) temp
group by id 
4
seahawk

NOT mySQL, ancak bu soruyu bulan ve SQL kullanan diğer insanlar için, /grubun başına - - problemini çözmenin başka bir yolu MS SQL'de/- Cross Apply komutunu kullanmaktır.

WITH DocIds AS (SELECT DISTINCT id FROM docs)

SELECT d2.id, d2.rev, d2.content
FROM DocIds d1
CROSS APPLY (
  SELECT Top 1 * FROM docs d
  WHERE d.id = d1.id
  ORDER BY rev DESC
) d2

İşte SqlFiddle'da bir örnek

4
KyleMit

İşi yapmanın bir başka yolu, OVER PARTITION yan tümcesinde MAX() analitik işlevini kullanmaktır

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,MAX(rev) OVER (PARTITION BY id) as max_rev
      FROM YourTable
    ) t
  WHERE t.rev = t.max_rev 

Bu gönderide daha önce belgelenen diğer ROW_NUMBER() OVER PARTITION çözümü

SELECT t.*
  FROM
    (
    SELECT id
          ,rev
          ,contents
          ,ROW_NUMBER() OVER (PARTITION BY id ORDER BY rev DESC) rank
      FROM YourTable
    ) t
  WHERE t.rank = 1 

Bu 2 SELECT, Oracle 10g'da iyi çalışır.

MAX () çözümü kesinlikle daha HIZLI çalışır çünkü ROW_NUMBER() karmaşıklığı MAX() olduğundan O(n) çözümü ROW_NUMBER() karmaşıklığı asgari O(n.log(n)) iken n tablodaki kayıt sayısını gösterir!

3
schlebe

Bunu kullanırdım:

select t.*
from test as t
join
   (select max(rev) as rev
    from test
    group by id) as o
on o.rev = t.rev

Subquery SELECT belki de çok yetersizdir, ancak JOIN deyiminde kullanılabilir görünmektedir. Sorguları optimize etme konusunda uzman değilim, ancak MySQL, PostgreSQL, FireBird'de denedim ve çok iyi çalışıyor.

Bu şemayı birden fazla birleşmede ve WHERE yan tümcesinde kullanabilirsiniz. Bu benim çalışma örneğim ("firmy" tablosu ile aynı problemi çözmek):

select *
from platnosci as p
join firmy as f
on p.id_rel_firmy = f.id_rel
join (select max(id_obj) as id_obj
      from firmy
      group by id_rel) as o
on o.id_obj = f.id_obj and p.od > '2014-03-01'

Gençlere binlerce rekoru olan masalarda sorulur ve gerçekten çok güçlü olmayan bir makinede 0,01 saniyeden daha kısa sürer.

IN yan tümcesini kullanmazdım (yukarıda belirtildiği gibi). IN, kısa sorgularda listelenmiştir ve alt sorgu üzerine inşa edilmiş sorgu filtresi olarak kullanılmamalıdır. Bunun nedeni, IN'de alt sorgulamanın, çok uzun zaman harcayarak sorgu yapabilen her taranan kayıt için gerçekleştirilmesidir.

3
Marek Wysmułek

Buna ne dersin:

SELECT all_fields.*  
FROM (SELECT id, MAX(rev) FROM yourtable GROUP BY id) AS max_recs  
LEFT OUTER JOIN yourtable AS all_fields 
ON max_recs.id = all_fields.id
3
inor
SELECT *
FROM Employee
where Employee.Salary in (select max(salary) from Employee group by Employe_id)
ORDER BY Employee.Salary
3
guru008

Bu cevapların hiçbiri benim için işe yaramadı.

Bu benim için çalıştı.

with score as (select max(score_up) from history)
select history.* from score, history where history.score_up = score.max
2
qaisjp

Rev alanını ters sırada sıraladıktan sonra en yüksek rev değerine sahip olan her grubun ilk satırını veren id ile gruplandırın.

SELECT * FROM (SELECT * FROM table1 ORDER BY id, rev DESC) X GROUP BY X.id;

Aşağıdaki verilerle http://sqlfiddle.com/ adresinde test edilmiştir.

CREATE TABLE table1
    (`id` int, `rev` int, `content` varchar(11));

INSERT INTO table1
    (`id`, `rev`, `content`)
VALUES
    (1, 1, 'One-One'),
    (1, 2, 'One-Two'),
    (2, 1, 'Two-One'),
    (2, 2, 'Two-Two'),
    (3, 2, 'Three-Two'),
    (3, 1, 'Three-One'),
    (3, 3, 'Three-Three')
;

Bu MySql 5.5 ve 5.6’da aşağıdaki sonucu verdi. 

id  rev content
1   2   One-Two
2   2   Two-Two
3   3   Three-Two
2
blokeish

İşte bunu yapmanın güzel bir yolu

Aşağıdaki kodu kullanın:

with temp as  ( 
select count(field1) as summ , field1
from table_name
group by field1 )
select * from temp where summ = (select max(summ) from temp)
2
shay

Bunu kayıtları bir sütuna göre sıralayarak yapmak hoşuma gidiyor. Bu durumda, rev tarafından gruplanan id değerleri sıralanır. rev değeri daha yüksek olanlarda daha düşük sıralamaya sahip olacaksınız. Öyleyse rev 1 sıralaması olacak.

select id, rev, content
from
 (select
    @rowNum := if(@prevValue = id, @rowNum+1, 1) as row_num,
    id, rev, content,
    @prevValue := id
  from
   (select id, rev, content from YOURTABLE order by id asc, rev desc) TEMP,
   (select @rowNum := 1 from DUAL) X,
   (select @prevValue := -1 from DUAL) Y) TEMP
where row_num = 1;

Değişkenleri tanıtmanın her şeyi daha yavaş yapıp yapmadığından emin değilim. Ama en azından iki kez YOURTABLE sorgulamıyorum.

2
user5124980

Kayıtları yalnızca bu alan için maksimum değere sahip bir alanla almak için başka bir çözüm. Bu, üzerinde çalıştığım platform olan SQL400 için çalışıyor. Bu örnekte, FIELD5 alanındaki maksimum değere sahip kayıtlar aşağıdaki SQL ifadesiyle alınacaktır.

SELECT A.KEYFIELD1, A.KEYFIELD2, A.FIELD3, A.FIELD4, A.FIELD5
  FROM MYFILE A
 WHERE RRN(A) IN
   (SELECT RRN(B) 
      FROM MYFILE B
     WHERE B.KEYFIELD1 = A.KEYFIELD1 AND B.KEYFIELD2 = A.KEYFIELD2
     ORDER BY B.FIELD5 DESC
     FETCH FIRST ROW ONLY)
2
Cesar

işte birisine yardımcı olacağını umuyorum başka bir çözüm 

Select a.id , a.rev, a.content from Table1 a
inner join 
(SELECT id, max(rev) rev FROM Table1 GROUP BY id) x on x.id =a.id and x.rev =a.rev
2
Abdul Samad

Bu çözüm YourTable'dan sadece bir seçim yapar, bu yüzden daha hızlı. Sqlfiddle.com'da yapılan teste göre sadece MySQL ve SQLite (SQLite DESC'yi kaldır) için çalışır. Belki de aşina olmadığım diğer dillerde çalışmak için ince ayar yapılabilir.

SELECT *
FROM ( SELECT *
       FROM ( SELECT 1 as id, 1 as rev, 'content1' as content
              UNION
              SELECT 2, 1, 'content2'
              UNION
              SELECT 1, 2, 'content3'
              UNION
              SELECT 1, 3, 'content4'
            ) as YourTable
       ORDER BY id, rev DESC
   ) as YourTable
GROUP BY id
2
plavozont

Açıklama

Bu saf SQL değil. Bu SQLAlchemy ORM kullanacaktır.

Buraya SQLAlchemy yardımını almaya geldim, bu yüzden Adrian Carneiro'nun python/SQLAlchemy sürümündeki cevabını çoğaltacağım.

Bu sorgu şu soruyu yanıtlıyor: 

"Bana en yüksek sürüm numarasına sahip bu kayıt grubundaki (aynı kimliği temel alan) kayıtları iade edebilir misiniz".  

Bu, kaydı kopyalamamı, güncellememi, sürüm numarasını arttırmamı ve eski sürümün kopyasını zaman içerisinde değişimi gösterebileceğim şekilde almamı sağlıyor.

Kod

MyTableAlias = aliased(MyTable)
newest_records = appdb.session.query(MyTable).select_from(join(
    MyTable, 
    MyTableAlias, 
    onclause=and_(
        MyTable.id == MyTableAlias.id,
        MyTable.version_int < MyTableAlias.version_int
    ),
    isouter=True
    )
).filter(
    MyTableAlias.id  == None,
).all()

PostgreSQL veritabanında test edilmiştir.

0
Ian A McElhenny

Aşağıdakileri kendi sorunumu çözmek için kullandım. Önce bir temp tablosu yarattım ve benzersiz bir kimlik için maksimum rev değerini ekledim.

CREATE TABLE #temp1
(
    id varchar(20)
    , rev int
)
INSERT INTO #temp1
SELECT a.id, MAX(a.rev) as rev
FROM 
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as a 
GROUP BY a.id
ORDER BY a.id

Daha sonra bu maksimum değerleri (# temp1) olası tüm id/içerik kombinasyonlarına dahil ettim. Bunu yaparak, maksimum olmayan id/içerik kombinasyonlarını doğal olarak filtreliyorum ve her biri için yalnızca maksimum rev değerleri bırakıyorum.

SELECT a.id, a.rev, content
FROM #temp1 as a
LEFT JOIN
    (
        SELECT id, content, SUM(rev) as rev
        FROM YourTable
        GROUP BY id, content
    ) as b on a.id = b.id and a.rev = b.rev
GROUP BY a.id, a.rev, b.content
ORDER BY a.id
0
Richard Ball

rev ve id öğelerini MAX() için bir maxRevId değerinde bir araya getirip ardından orijinal değerlerine böldüğünüzde seçimi birleştirme olmadan yapabilirsiniz:

SELECT maxRevId & ((1 << 32) - 1) as id, maxRevId >> 32 AS rev
FROM (SELECT MAX(((rev << 32) | id)) AS maxRevId
      FROM YourTable
      GROUP BY id) x;

Bu, özellikle tek bir masa yerine karmaşık bir birleştirme olduğunda hızlıdır. Geleneksel yaklaşımlarla, karmaşık birleştirme iki kez yapılır.

Yukarıdaki kombinasyon, rev ve idINT UNSIGNED (32 bit) ve birleşik değer BIGINT UNSIGNED (64 bit) için uygun olduğunda bit işlevleriyle basittir. id & rev, 32 bitlik değerlerden daha büyük veya birden çok sütundan yapılmışsa, değeri örneğin; MAX() için uygun dolgulu bir ikili değer.

0
zovio