mysql - تحديد SQL الصفوف فقط مع قيمة الحد الأقصى في عمود




aggregate-functions greatest-n-per-group (20)

للوهلة الأولى...

كل ما تحتاجه هو عبارة GROUP BY مع الوظيفة التجميعية MAX :

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

انها ليست بهذه البساطة ، أليس كذلك؟

لقد لاحظت أنك تحتاج إلى عمود content أيضًا.

هذا سؤال شائع جدًا في SQL: ابحث عن البيانات الكاملة للصف الذي يحتوي على بعض القيم القصوى في عمود لكل معرّف مجموعة. سمعت ذلك كثيرا خلال مسيرتي. في الواقع ، كان أحد الأسئلة التي أجبت عليها في المقابلة الفنية الحالية.

في الواقع ، من الشائع جدًا أن ينشئ منتدى StackOverflow علامة واحدة فقط للتعامل مع أسئلة من هذا القبيل: greatest-n-per-group .

في الأساس ، لديك طريقتان لحل هذه المشكلة:

الانضمام باستخدام group-identifier, max-value-in-group بسيط group-identifier, max-value-in-group استعلام فرعي group-identifier, max-value-in-group

في هذا النهج ، يمكنك أولاً العثور على group-identifier, max-value-in-group (سبق حلها أعلاه) في استعلام فرعي. ثم تنضم إلى الجدول الخاص بك إلى الاستعلام الفرعي مع المساواة على كل group-identifier max-value-in-group :

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

الانضمام إلى اليسار مع الذات ، التغيير والتبديل شروط الانضمام والمرشحات

في هذا النهج ، تركت الانضمام إلى الجدول نفسه. المساواة ، بطبيعة الحال ، يذهب في group-identifier . ثم ، 2 التحركات الذكية:

  1. شرط الارتباط الثاني هو وجود قيمة الجانب الأيسر أقل من القيمة الصحيحة
  2. عندما تقوم بالخطوة 1 ، سيكون الصف (الصفوف) التي لديها بالفعل الحد الأقصى للقيمة NULL في الجانب الأيمن (وهو LEFT JOIN ، تذكر؟). بعد ذلك ، نقوم بتصفية النتيجة المشتركة ، مع إظهار الصفوف فقط حيث يكون الجانب الأيمن هو NULL .

حتى ينتهي بك الأمر مع:

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;

استنتاج

كلا المناهج يجلب نفس النتيجة بالضبط.

إذا كان لديك صفين ذات max-value-in-group group-identifier ، فسيكون كل من الصفوف في النتيجة في كلا الأسلوبين.

كلا الأسلوبين متوافق مع SQL ANSI ، وبالتالي ، سوف يعمل مع RDBMS المفضلة لديك ، بغض النظر عن "النكهة".

كلتا الطريقتين هي أيضًا متوافقة مع الأداء ، إلا أن المسافة المقطوعة الخاصة بك قد تختلف (RDBMS ، DB Structure ، فهارس ، إلخ). لذلك عند اختيار نهج واحد على الآخر ، المعيار . وتأكد من اختيار واحد مما يجعل معظم بمعنى لك.

لدي هذا الجدول للمستندات (نسخة مبسطة هنا):

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

كيف أختار صفًا واحدًا لكل معرف وأكبر قدر من المراجعة؟
مع البيانات المذكورة أعلاه ، يجب أن تحتوي النتيجة على صفين: [1, 3, ...] و [2, 1, ..] . أنا أستخدم MySQL .

أنا حاليا استخدام الشيكات في حلقة في while لاكتشاف وكتابة المراجعات القديمة من resultset. ولكن هل هذه هي الطريقة الوحيدة لتحقيق النتيجة؟ ليس هناك حل SQL ؟

تحديث
كما تقترح الإجابات ، هناك حل SQL ، وهنا عرض تجريبي sqlfiddle .

تحديث 2
لاحظت بعد إضافة sqlfiddle أعلاه ، تجاوز المعدل الذي يتم التصويت عليه السؤال معدل upvote الأجوبة. لم يكن هذا هو النية! ويستند الكمان على الإجابات ، وخاصة الإجابة المقبولة.


SELECT * FROM Employee where Employee.Salary in (select max (salary) from Employee group by Employe_id) ORDER BY Employee.Salary


أعتقد أن هذا هو الحل الأسهل:

SELECT *
FROM
    (SELECT *
    FROM Employee
    ORDER BY Salary DESC)
AS employeesub
GROUP BY employeesub.Salary;
  • حدد *: إرجاع كافة الحقول.
  • من الموظف: جدول البحث على.
  • (SELECT * ...) استعلام فرعي: إعادة جميع الأشخاص ، مصنَّفين حسب الراتب.
  • GROUP BY employeeub.Salary:: فرض صف المرتب الأعلى ، كل موظف ليكون النتيجة المرتجعة.

إذا كنت تحتاج إلى صف واحد فقط ، فستكون أسهل:

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

أعتقد أيضًا أنه الأسهل في التفصيل والفهم والتعديل لأغراض أخرى:

  • ORDER BY Employee.Salary DESC: ترتيب النتائج حسب الراتب ، مع أعلى الرواتب أولا.
  • LIMIT 1: إرجاع نتيجة واحدة فقط.

فهم هذه المقاربة ، حل أي من هذه المشاكل المشابهة يصبح تافهاً: الحصول على الموظف بأجر أدنى (تغيير DESC إلى ASC) ، الحصول على أعلى عشرة أرباح للموظفين (تغيير LIMIT 1 إلى LIMIT 10) ، الفرز عن طريق حقل آخر (تغيير ORDER BY) Employee.Salary to ORDER BY Employee.Commission)، etc ..


أفضلي هو استخدام أقل قدر ممكن من التعليمات البرمجية ...

يمكنك فعل ذلك باستخدام IN جرب هذا:

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

في رأيي أنه أقل تعقيدًا ... أسهل في القراءة والمحافظة عليه.


أود القيام بذلك عن طريق ترتيب السجلات من قبل بعض العمود. في هذه الحالة ، قيم rev مجمّعة حسب id . أولئك الذين لديهم rev أعلى سيكون لديهم تصنيف أقل. لذلك سوف يكون أعلى درجة في الترتيب 1.

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;

لست متأكدا إذا كان إدخال المتغيرات يجعل كل شيء أبطأ. ولكن على الأقل أنا لا YOURTABLE عن YOURTABLE مرتين.


إذا كان أي شخص يبحث عن Linq verson ، يبدو أن هذا العمل بالنسبة لي:

public static IQueryable<BlockVersion> LatestVersionsPerBlock(this IQueryable<BlockVersion> blockVersions)
{
    var max_version_per_id = blockVersions.GroupBy(v => v.BlockId)
        .Select( v => new { BlockId = v.Key, MaxVersion = v.Max(x => x.Version) } );    

    return blockVersions.Where( v => max_version_per_id.Any(x => x.BlockId == v.BlockId && x.MaxVersion == v.Version) );
}

إليك حلاً آخر لاسترداد السجلات فقط باستخدام حقل له الحد الأقصى لقيمة هذا الحقل. هذا يعمل لـ SQL400 وهو النظام الأساسي الذي أعمل عليه. في هذا المثال ، سيتم استرداد السجلات ذات القيمة القصوى في الحقل FIELD5 بواسطة عبارة SQL التالية.

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)

الحل الآخر هو استخدام طلب فرعي مترابط:

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

وجود فهرس على (معرف ، rev) يجعل الاستعلام الفرعي كبحث بسيط ...

فيما يلي مقارنات الحلول في الإجابة على @ AdrianCarneiro (طلب البحث الفرعي ، leftjoin) ، استنادًا إلى قياسات MySQL مع جدول InnoDB الذي يحتوي على 1 مليون سجل ، حجم المجموعة هو: 1-3.

في حين أن طلبات البحث عن الجدول الكامل / logjoin / المترابطة مرتبطة ببعضها البعض كـ 6/8/9 ، عندما يتعلق الأمر بعمليات البحث المباشر أو الدُفعة ( id in (1,2,3) ) ، يكون طلب البحث الفرعي أبطأ بكثير ثم الآخر ( بسبب إعادة تشغيل الاستعلام الفرعي). ومع ذلك لم أتمكن من التفريق بين حلول اليسار والحلول المترابطة في السرعة.

ملاحظة أخيرة ، كما يخلق leftjoin ينضم n * (n + 1) / 2 في مجموعات ، يمكن أن يتأثر أداءها بشكل كبير بحجم المجموعات ...


بما أن هذا هو السؤال الأكثر شيوعًا فيما يتعلق بهذه المشكلة ، فسأقوم بإعادة نشر إجابة أخرى له هنا أيضًا:

يبدو أن هناك طريقة أبسط للقيام بذلك (ولكن فقط في MySQL ):

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

يرجى الإجابة عن إجابة المستخدم البوهيمي في هذا السؤال لتقديم هذه الإجابة المختصرة والأنيقة لهذه المشكلة.

تعديل: على الرغم من أن هذا الحل يعمل مع العديد من الأشخاص ، فإنه قد لا يكون ثابتًا على المدى الطويل ، بما أن MySQL لا تضمن أن عبارة GROUP BY ستعرض قيمًا ذات معنى للأعمدة غير الموجودة في قائمة GROUP BY. لذلك استخدم هذا الحل على مسؤوليتك الخاصة


تم فرز حقل المراجعة بترتيب عكسي ثم تجميعها حسب المعرّف الذي أعطى الصف الأول من كل مجموعة وهو الذي يحتوي على أعلى قيمة مراجعة.

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

تم اختباره في http://sqlfiddle.com/ مع البيانات التالية

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')
;

هذا أعطى النتيجة التالية في MySql 5.5 و 5.6

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

شيء من هذا القبيل؟

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)

كثير ، إن لم يكن كل ، من الإجابات الأخرى هنا هي جيدة لمجموعات البيانات الصغيرة. للتوسع ، هناك حاجة لمزيد من الرعاية. انظر here .

ويناقش طرق أسرع متعددة للقيام أقصى مجموعة أقصى وأعلى N لكل مجموعة.


لقد استخدمت أدناه لحل مشكلة خاصة بي. قمت أولاً بإنشاء جدول مؤقت وأدرجت قيمة المراجعة القصوى لكل معرف فريد.

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

ثم انضممت إلى هذه القيم القصوى (# temp1) إلى جميع مجموعات المعرّف / المحتوى الممكنة. من خلال القيام بذلك ، أقوم بطبيعة الحال بترشيح مجموعات المحتوى / المعرّف غير القصوى ، وتُركت مع قيم المراجعة القصوى فقط لكل منها.

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

لم تنجح أي من هذه الإجابات بالنسبة لي.

هذا ما نجح بالنسبة لي.

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

هنا طريقة لطيفة للقيام بذلك

استخدم الرمز التالي:

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)

هنا هو حل آخر نأمل أن يساعد شخص ما

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

وماذا عن هذا:

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

يجعل هذا الحل تحديدًا واحدًا فقط من YourTable ، لذلك يكون أسرع. يعمل فقط على MySQL و SQLite (لإزالة SQLite DESC) وفقًا للاختبار على sqlfiddle.com. ربما يمكن تعديله للعمل على اللغات الأخرى التي لست على دراية بها.

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

SELECT * FROM t1 ORDER BY rev DESC LIMIT 1;

select * from yourtable
group by id
having rev=max(rev);




greatest-n-per-group