Câu hỏi MySQL “Nhóm theo” và “Đặt hàng theo”


Tôi muốn có thể chọn một loạt các hàng từ một bảng các e-mail và nhóm chúng lại từ người gửi. Truy vấn của tôi trông giống như sau:

SELECT 
    `timestamp`, `fromEmail`, `subject`
FROM `incomingEmails` 
GROUP BY LOWER(`fromEmail`) 
ORDER BY `timestamp` DESC

Truy vấn hầu như hoạt động như tôi muốn - nó chọn các bản ghi được nhóm theo e-mail. Vấn đề là đối tượng và dấu thời gian không tương ứng với bản ghi gần đây nhất cho một địa chỉ email cụ thể.

Ví dụ, nó có thể trở lại:

fromEmail: john@example.com, subject: hello
fromEmail: mark@example.com, subject: welcome

Khi các bản ghi trong cơ sở dữ liệu là:

fromEmail: john@example.com, subject: hello
fromEmail: john@example.com, subject: programming question
fromEmail: mark@example.com, subject: welcome

Nếu chủ đề "câu hỏi lập trình" là gần đây nhất, làm thế nào tôi có thể nhận được MySQL để chọn bản ghi đó khi nhóm các e-mail?


76
2018-06-30 22:40


gốc




Các câu trả lời:


Một giải pháp đơn giản là để bọc truy vấn vào một subselect với câu lệnh ORDER Đầu tiên và áp dụng GROUP BY một lát sau:

SELECT * FROM ( 
    SELECT `timestamp`, `fromEmail`, `subject`
    FROM `incomingEmails` 
    ORDER BY `timestamp` DESC
) AS tmp_table GROUP BY LOWER(`fromEmail`)

Điều này tương tự như sử dụng kết nối nhưng trông đẹp hơn nhiều.

Sử dụng các cột không tổng hợp trong một SELECT với mệnh đề GROUP BY là không chuẩn. MySQL thường sẽ trả về các giá trị của hàng đầu tiên mà nó tìm thấy và loại bỏ phần còn lại. Bất kỳ mệnh đề ORDER BY nào cũng chỉ áp dụng cho giá trị cột được trả về, không áp dụng cho các giá trị bị hủy bỏ.

CẬP NHẬT QUAN TRỌNG Chọn các cột không tổng hợp được sử dụng để làm việc trong thực tế nhưng không nên dựa vào. Theo Tài liệu MySQL "điều này hữu ích chủ yếu khi tất cả các giá trị trong mỗi cột không được phân tách không được đặt tên trong GROUP BY giống nhau cho mỗi nhóm. tự do lựa chọn bất kỳ giá trị nào từ mỗi nhóm, vì vậy trừ khi chúng giống nhau, các giá trị được chọn là không xác định. "

Kể từ 5.6.21 tôi đã nhận thấy các vấn đề với GROUP BY trên bảng tạm thời hoàn nguyên sắp xếp ORDER BY.

Kể từ 5.7.5 ONLY_FULL_GROUP_BY được bật theo mặc định, tức là không thể sử dụng các cột không tổng hợp.

Xem http://www.cafewebmaster.com/mysql-order-sort-group https://dev.mysql.com/doc/refman/5.6/en/group-by-handling.html https://dev.mysql.com/doc/refman/5.7/en/group-by-handling.html


117
2018-03-21 00:45



Ý tưởng tuyệt vời, tôi sẽ không bao giờ nghĩ đến việc làm theo cách này. - philwilks
Tôi đã đưa ra một giải pháp tương tự vài năm trước, và đó là một giải pháp tuyệt vời. kudo đến b7kich. Hai vấn đề ở đây mặc dù ... GROUP BY là trường hợp không nhạy cảm nên LOWER () là không cần thiết, và thứ hai, $ userID dường như là một biến trực tiếp từ PHP, mã của bạn có thể bị chèn lỗ sql nếu $ userID được người dùng cung cấp và không bị ép buộc là một số nguyên. - velcrow
Ý kiến ​​hay. Cảm ơn nhiều - Rizaldi Maulidia
Thnks buddy .... - black
CẬP NHẬT QUAN TRỌNG cũng áp dụng cho MariaDB: mariadb.com/kb/en/mariadb/… - Arthur Shipkowski


Đây là một cách tiếp cận:

SELECT cur.textID, cur.fromEmail, cur.subject, 
     cur.timestamp, cur.read
FROM incomingEmails cur
LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.timestamp < next.timestamp
WHERE next.timestamp is null
and cur.toUserID = '$userID' 
ORDER BY LOWER(cur.fromEmail)

Về cơ bản, bạn tự mình tham gia bảng, tìm kiếm các hàng sau. Trong mệnh đề where bạn nói rằng không thể có các hàng sau này. Điều này chỉ cho bạn hàng mới nhất.

Nếu có thể có nhiều email với cùng một dấu thời gian, truy vấn này sẽ cần tinh chỉnh. Nếu có một cột ID gia tăng trong bảng email, hãy thay đổi JOIN như sau:

LEFT JOIN incomingEmails next
    on cur.fromEmail = next.fromEmail
    and cur.id < next.id

40
2018-06-30 22:59



Nói rằng textID đã mơ hồ = / - John Kurlak
Sau đó loại bỏ ambuigity và tiền tố nó với tên bảng, như cur.textID. Thay đổi trong câu trả lời là tốt. - Andomar
Đây là giải pháp duy nhất có thể thực hiện với Doctrine DQL. - VisioN
Điều này không hiệu quả khi bạn đang cố tự tham gia nhiều cột. IE khi bạn đang cố gắng tìm email mới nhất và tên người dùng mới nhất và bạn yêu cầu nhiều lần tự kết nối trái để thực hiện thao tác này trong một truy vấn. - Loveen Dyall
Khi làm việc với các dấu thời gian / ngày trong quá khứ và tương lai, để giới hạn kết quả cho những ngày không tương lai, bạn cần phải thêm một điều kiện khác vào LEFT JOIN tiêu chí AND next.timestamp <= UNIX_TIMESTAMP() - fyrye


Thực hiện GROUP BY sau ORDER BY bằng cách gói truy vấn của bạn với GROUP BY như sau:

SELECT t.* FROM (SELECT * FROM table ORDER BY time DESC) t GROUP BY t.from

26
2018-04-30 19:53



Cảm ơn điều này đã làm việc hoàn hảo cho tôi trên một truy vấn simlar tôi đã làm. - Mark
Vì vậy, GROUP BY` tự động chọn mới nhất timehoặc mới nhất timehoặc ngẫu nhiên? - xrDDDD
Nó chọn thời gian mới nhất bởi vì chúng tôi đang đặt hàng time DESCvà sau đó nhóm bằng cách lấy nhóm đầu tiên (mới nhất). - 11101101b
Cảm ơn bạn ! stackoverflow.com/a/20460516/25286 - GDmac
Bây giờ nếu tôi chỉ có thể làm JOIN trên sub-selects trong VIEWS, trong mysql 5.1. Có lẽ tính năng đó xuất hiện trong bản phát hành mới hơn. - IcarusNM


Theo tiêu chuẩn SQL, bạn không thể sử dụng các cột không tổng hợp trong danh sách lựa chọn. MySQL cho phép sử dụng như vậy (uless ONLY_FULL_GROUP_BY chế độ được sử dụng) nhưng kết quả là không thể dự đoán được.

ONLY_FULL_GROUP_BY

Trước tiên bạn nên chọn từEmail, MIN (đọc), và sau đó, với truy vấn thứ hai (hoặc truy vấn con) - Subject.


21
2018-06-30 22:59



MIN (đọc) sẽ trả về giá trị tối thiểu của "đọc". Có lẽ anh ta đang tìm kiếm cờ "đọc" của email mới nhất thay thế. - Andomar


Như đã chỉ ra trong một câu trả lời, câu trả lời hiện tại là sai, bởi vì GROUP BY tự ý chọn bản ghi từ cửa sổ.

Nếu một người đang sử dụng MySQL 5.6 hoặc MySQL 5.7 với ONLY_FULL_GROUP_BY, truy vấn chính xác (xác định) là:

SELECT incomingEmails.*
  FROM (
    SELECT fromEmail, MAX(timestamp) `timestamp`
    FROM incomingEmails
    GROUP BY fromEmail
  ) filtered_incomingEmails
  JOIN incomingEmails USING (fromEmail, timestamp)
GROUP BY fromEmail, timestamp

Để truy vấn chạy một cách hiệu quả, việc lập chỉ mục thích hợp là bắt buộc.

Lưu ý rằng vì mục đích đơn giản hóa, tôi đã xóa LOWER(), trong hầu hết các trường hợp, sẽ không được sử dụng.


16
2018-02-17 12:00



Đây sẽ là câu trả lời đúng. Tôi vừa phát hiện ra một lỗi trên trang web của tôi liên quan đến điều này. Các order by trong việc chọn lựa trong các câu trả lời khác, không có tác dụng gì cả. - Jette
OMG, hãy làm cho câu trả lời này được chấp nhận. Người được chấp nhận lãng phí 5 giờ thời gian của tôi :( - Richard Kersey


Tôi đã vật lộn với cả hai cách tiếp cận này đối với các truy vấn phức tạp hơn so với các truy vấn được hiển thị, bởi vì cách tiếp cận truy vấn con là vô giá trị bất kể tôi lập chỉ mục nào, và bởi vì tôi không thể tự kết nối bên ngoài thông qua Hibernate

Cách tốt nhất (và dễ nhất) để làm điều này là nhóm theo thứ gì đó được xây dựng để chứa một kết nối của các trường bạn yêu cầu và sau đó kéo chúng ra bằng cách sử dụng các biểu thức trong mệnh đề SELECT. Nếu bạn cần thực hiện MAX (), hãy đảm bảo rằng trường bạn muốn MAX () qua luôn ở đầu quan trọng nhất của thực thể được nối.

Chìa khóa để hiểu điều này là truy vấn chỉ có thể có ý nghĩa nếu các trường khác là bất biến đối với bất kỳ thực thể nào thỏa mãn Max (), do đó về mặt sắp xếp, các phần khác của phép nối có thể bị bỏ qua. Nó giải thích làm thế nào để làm điều này ở dưới cùng của liên kết này. http://dev.mysql.com/doc/refman/5.0/en/group-by-hidden-columns.html

Nếu bạn có thể nhận được sự kiện chèn / cập nhật (như trình kích hoạt) để tính toán trước các kết nối của các trường mà bạn có thể lập chỉ mục và truy vấn sẽ nhanh như khi nhóm chỉ qua trường bạn thực sự muốn MAX ( ). Bạn thậm chí có thể sử dụng nó để có được tối đa nhiều trường. Tôi sử dụng nó để thực hiện các truy vấn đối với các cây đa chiều được biểu thị dưới dạng các tập lồng nhau.


2
2017-10-31 14:00