mysql - كيف يمكنني اختيار صفوف مع MAX(قيمة العمود) ، DISTINCT بواسطة عمود آخر في SQL؟




greatest-n-per-group (12)

مائدتي هي:

id  home  datetime     player   resource
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399 
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
3  | 10  | 03/03/2009 | john   | 300
4  | 11  | 03/03/2009 | juliet | 200
6  | 12  | 03/03/2009 | borat  | 500
7  | 13  | 24/12/2008 | borat  | 600
8  | 13  | 01/01/2009 | borat  | 700

أحتاج إلى تحديد كل home متميز يمتلك الحد الأقصى لقيمة datetime .

ستكون النتيجة:

id  home  datetime     player   resource 
---|-----|------------|--------|---------
1  | 10  | 04/03/2009 | john   | 399
2  | 11  | 04/03/2009 | juliet | 244
5  | 12  | 04/03/2009 | borat  | 555
8  | 13  | 01/01/2009 | borat  | 700

لقد حاولت:

-- 1 ..by the MySQL manual: 

SELECT DISTINCT
  home,
  id,
  datetime AS dt,
  player,
  resource
FROM topten t1
WHERE datetime = (SELECT
  MAX(t2.datetime)
FROM topten t2
GROUP BY home)
GROUP BY datetime
ORDER BY datetime DESC

لا يعمل. تحتوي مجموعة النتائج على 130 صف بالرغم من أن قاعدة البيانات تحتوي على 187. تتضمن النتائج بعض التكرارات home .

-- 2 ..join

SELECT
  s1.id,
  s1.home,
  s1.datetime,
  s1.player,
  s1.resource
FROM topten s1
JOIN (SELECT
  id,
  MAX(datetime) AS dt
FROM topten
GROUP BY id) AS s2
  ON s1.id = s2.id
ORDER BY datetime 

كلا. يعطي كل السجلات.

-- 3 ..something exotic: 

مع نتائج مختلفة.


@ Michae سوف تعمل الإجابة المقبولة بشكل جيد في معظم الحالات ولكنها تفشل في الحصول على واحدة كما هو موضح أدناه.

في حالة ما إذا كان هناك صفان يحتويان على HomeID و Datetime ، فسيرجع الاستعلام كلا الصفوف ، وليس HomeID كما هو مطلوب ، وذلك لإضافة عبارة مميزة في الاستعلام على النحو التالي.

SELECT DISTINCT tt.home  , tt.MaxDateTime
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

أسرع حل MySQL بدون استعلامات داخلية وبدون GROUP BY :

SELECT m.*                    -- get the row that contains the max value
FROM topten m                 -- "m" from "max"
    LEFT JOIN topten b        -- "b" from "bigger"
        ON m.home = b.home    -- match "max" row with "bigger" row by `home`
        AND m.datetime < b.datetime           -- want "bigger" than "max"
WHERE b.datetime IS NULL      -- keep only if there is no bigger than max

شرح :

انضم إلى الجدول نفسه باستخدام العمود home . يضمن استخدام LEFT JOIN جميع الصفوف من الجدول m في مجموعة النتائج. تلك التي ليس لديها تطابق في الجدول b سيكون لها NULL s لأعمدة b .

يسأل الشرط الآخر على JOIN تطابق الصفوف فقط من b التي تحتوي على قيمة أكبر في عمود datetime من الصف من m .

باستخدام البيانات المنشورة في السؤال ، ستقوم LEFT JOIN بإنتاج هذه الأزواج:

+------------------------------------------+--------------------------------+
|              the row from `m`            |    the matching row from `b`   |
|------------------------------------------|--------------------------------|
| id  home  datetime     player   resource | id    home   datetime      ... |
|----|-----|------------|--------|---------|------|------|------------|-----|
| 1  | 10  | 04/03/2009 | john   | 399     | NULL | NULL | NULL       | ... | *
| 2  | 11  | 04/03/2009 | juliet | 244     | NULL | NULL | NULL       | ... | *
| 5  | 12  | 04/03/2009 | borat  | 555     | NULL | NULL | NULL       | ... | *
| 3  | 10  | 03/03/2009 | john   | 300     | 1    | 10   | 04/03/2009 | ... |
| 4  | 11  | 03/03/2009 | juliet | 200     | 2    | 11   | 04/03/2009 | ... |
| 6  | 12  | 03/03/2009 | borat  | 500     | 5    | 12   | 04/03/2009 | ... |
| 7  | 13  | 24/12/2008 | borat  | 600     | 8    | 13   | 01/01/2009 | ... |
| 8  | 13  | 01/01/2009 | borat  | 700     | NULL | NULL | NULL       | ... | *
+------------------------------------------+--------------------------------+

وأخيرًا ، تحتفظ جملة WHERE فقط بالأزواج التي تحتوي على NULL s في أعمدة b (تم وضع علامة لها بـ * في الجدول أعلاه) ؛ هذا يعني أنه بسبب الشرط الثاني من جملة JOIN ، فإن الصف المحدد من m له أكبر قيمة في datetime .

قراءة antipatterns SQL: تجنب Pitfalls من كتاب برمجة قاعدة البيانات للحصول على نصائح SQL الأخرى.


أنت قريب جدا! كل ما عليك القيام به هو تحديد كل من المنزل ووقته الأقصى ، ثم الانضمام مرة أخرى إلى الجدول topten على الحقلين التاليين:

SELECT tt.*
FROM topten tt
INNER JOIN
    (SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home) groupedtt 
ON tt.home = groupedtt.home 
AND tt.datetime = groupedtt.MaxDateTime

جرب هذا

select * from mytable a join
(select home, max(datetime) datetime
from mytable
group by home) b
 on a.home = b.home and a.datetime = b.datetime

التحيات ك


سيعمل هذا حتى إذا كان لديك صفان أو أكثر لكل home DATETIME :

SELECT id, home, datetime, player, resource
FROM   (
       SELECT (
              SELECT  id
              FROM    topten ti
              WHERE   ti.home = t1.home
              ORDER BY
                      ti.datetime DESC
              LIMIT 1
              ) lid
       FROM   (
              SELECT  DISTINCT home
              FROM    topten
              ) t1
       ) ro, topten t2
WHERE  t2.id = ro.lid

طريقة أخرى لإجراء gt لأحدث صف في كل مجموعة باستخدام استعلام فرعي والذي يقوم بشكل أساسي بحساب الترتيب لكل صف لكل مجموعة ثم تصفية آخر صفوف كما هو الحال مع الرتبة = 1

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and a.`datetime` < b.`datetime`
) +1 = 1

DEMO

هنا هو العرض التوضيحي المرئي لرتبة لا لكل صف من أجل فهم أفضل

من خلال قراءة بعض التعليقات ما إذا كان هناك صفان لهما نفس قيم الحقل "المنزل" و "التاريخ / الوقت"؟

سوف تفشل الاستعلام أعلاه وستعطي أكثر من 1 صفوف للموقف أعلاه. للتغطية على هذا الوضع ، ستكون هناك حاجة إلى معيار / معلمة / عمود آخر لتحديد الصف الذي يجب أن يؤخذ والذي يقع في الحالة أعلاه. من خلال عرض مجموعة بيانات العينة ، افترض أن هناك id عمود رئيسي أساسي يجب تعيينه لزيادة تلقائية. حتى نتمكن من استخدام هذا العمود لاختيار الصف الأخير عن طريق تعديل نفس الاستعلام بمساعدة بيان CASE مثل

select a.*
from topten a
where (
  select count(*)
  from topten b
  where a.home = b.home
  and  case 
       when a.`datetime` = b.`datetime`
       then a.id < b.id
       else a.`datetime` < b.`datetime`
       end
) + 1 = 1

DEMO

سيقوم الاستعلام أعلاه باختيار الصف ذو أعلى رقم بين قيم datetime نفسها

عرض مرئي لرتبة لا لكل صف


نظرًا لأنه يبدو أن الأشخاص يستمرون في الظهور في هذا الموضوع (نطاقات تاريخ التعليق من 1.5 عامًا) ، فهذا ليس أبسط بكثير:

SELECT * FROM (SELECT * FROM topten ORDER BY datetime DESC) tmp GROUP BY home

لا توجد وظائف التجميع اللازمة ...

في صحتك.


هذا هو الاستعلام الذي تحتاجه:

 SELECT b.id, a.home,b.[datetime],b.player,a.resource FROM
 (SELECT home,MAX(resource) AS resource FROM tbl_1 GROUP BY home) AS a

 LEFT JOIN

 (SELECT id,home,[datetime],player,resource FROM tbl_1) AS b
 ON  a.resource = b.resource WHERE a.home =b.home;

هنا نسخة MySQL التي تطبع إدخال واحد فقط حيث يوجد مكررات MAX (تاريخ / وقت) في مجموعة.

يمكنك اختبار هنا http://www.sqlfiddle.com/#!2/0a4ae/1

عينة البيانات

mysql> SELECT * from topten;
+------+------+---------------------+--------+----------+
| id   | home | datetime            | player | resource |
+------+------+---------------------+--------+----------+
|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 |
|    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 |
|    3 |   10 | 2009-03-03 00:00:00 | john   |      300 |
|    4 |   11 | 2009-03-03 00:00:00 | juliet |      200 |
|    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 |
|    6 |   12 | 2009-03-03 00:00:00 | borat  |      500 |
|    7 |   13 | 2008-12-24 00:00:00 | borat  |      600 |
|    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 |
|    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 |
|   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 |
|   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |
+------+------+---------------------+--------+----------+

إصدار MySQL مع متغير المستخدم

SELECT *
FROM (
    SELECT ord.*,
        IF (@prev_home = ord.home, 0, 1) AS is_first_appear,
        @prev_home := ord.home
    FROM (
        SELECT t1.id, t1.home, t1.player, t1.resource
        FROM topten t1
        INNER JOIN (
            SELECT home, MAX(datetime) AS mx_dt
            FROM topten
            GROUP BY home
          ) x ON t1.home = x.home AND t1.datetime = x.mx_dt
        ORDER BY home
    ) ord, (SELECT @prev_home := 0, @seq := 0) init
) y
WHERE is_first_appear = 1;
+------+------+--------+----------+-----------------+------------------------+
| id   | home | player | resource | is_first_appear | @prev_home := ord.home |
+------+------+--------+----------+-----------------+------------------------+
|    9 |   10 | borat  |      700 |               1 |                     10 |
|   10 |   11 | borat  |      700 |               1 |                     11 |
|   12 |   12 | borat  |      700 |               1 |                     12 |
|    8 |   13 | borat  |      700 |               1 |                     13 |
+------+------+--------+----------+-----------------+------------------------+
4 rows in set (0.00 sec)

موافقة الخروج المقبولة

SELECT tt.*
FROM topten tt
INNER JOIN
    (
    SELECT home, MAX(datetime) AS MaxDateTime
    FROM topten
    GROUP BY home
) groupedtt ON tt.home = groupedtt.home AND tt.datetime = groupedtt.MaxDateTime
+------+------+---------------------+--------+----------+
| id   | home | datetime            | player | resource |
+------+------+---------------------+--------+----------+
|    1 |   10 | 2009-04-03 00:00:00 | john   |      399 |
|    2 |   11 | 2009-04-03 00:00:00 | juliet |      244 |
|    5 |   12 | 2009-04-03 00:00:00 | borat  |      555 |
|    8 |   13 | 2009-01-01 00:00:00 | borat  |      700 |
|    9 |   10 | 2009-04-03 00:00:00 | borat  |      700 |
|   10 |   11 | 2009-04-03 00:00:00 | borat  |      700 |
|   12 |   12 | 2009-04-03 00:00:00 | borat  |      700 |
+------+------+---------------------+--------+----------+
7 rows in set (0.00 sec)

هنا يظهر إصدار T-SQL :

-- Test data
DECLARE @TestTable TABLE (id INT, home INT, date DATETIME, 
  player VARCHAR(20), resource INT)
INSERT INTO @TestTable
SELECT 1, 10, '2009-03-04', 'john', 399 UNION
SELECT 2, 11, '2009-03-04', 'juliet', 244 UNION
SELECT 5, 12, '2009-03-04', 'borat', 555 UNION
SELECT 3, 10, '2009-03-03', 'john', 300 UNION
SELECT 4, 11, '2009-03-03', 'juliet', 200 UNION
SELECT 6, 12, '2009-03-03', 'borat', 500 UNION
SELECT 7, 13, '2008-12-24', 'borat', 600 UNION
SELECT 8, 13, '2009-01-01', 'borat', 700

-- Answer
SELECT id, home, date, player, resource 
FROM (SELECT id, home, date, player, resource, 
    RANK() OVER (PARTITION BY home ORDER BY date DESC) N
    FROM @TestTable
)M WHERE N = 1

-- and if you really want only home with max date
SELECT T.id, T.home, T.date, T.player, T.resource 
    FROM @TestTable T
INNER JOIN 
(   SELECT TI.id, TI.home, TI.date, 
        RANK() OVER (PARTITION BY TI.home ORDER BY TI.date) N
    FROM @TestTable TI
    WHERE TI.date IN (SELECT MAX(TM.date) FROM @TestTable TM)
)TJ ON TJ.N = 1 AND T.id = TJ.id

تصحيح
للأسف ، لا توجد وظيفة RANK () OVER في MySQL.
ولكن يمكن أن يحتذى به ، انظر محاكاة التحليل التحليلي (تصنيف AKA) مع MySQL .
إذن هذا هو إصدار MySQL :

SELECT id, home, date, player, resource 
FROM TestTable AS t1 
WHERE 
    (SELECT COUNT(*) 
            FROM TestTable AS t2 
            WHERE t2.home = t1.home AND t2.date > t1.date
    ) = 0

SELECT  tt.*
FROM    TestTable tt 
INNER JOIN 
        (
        SELECT  coord, MAX(datetime) AS MaxDateTime 
        FROM    rapsa 
        GROUP BY
                krd 
        ) groupedtt
ON      tt.coord = groupedtt.coord
        AND tt.datetime = groupedtt.MaxDateTime

SELECT c1, c2, c3, c4, c5 FROM table1 WHERE c3 = (select max(c3) from table)

SELECT * FROM table1 WHERE c3 = (select max(c3) from table1)




greatest-n-per-group