問題描述
從 GROUP BY 組中選擇具有特定內容的行 (Select a row with specific content from a GROUP BY group)
I have two tables which allow a user to request songs. Of course a song can be requested by multiple users:
| Id | Song_Name | | Requested_Id | By_IP |
+====+===========+ +==============+=========+
| 1 | song1 | | 1 | 1.1.1.1 |
| 2 | song2 | | 1 | 2.2.2.2 |
| 3 | song3 | | 1 | 3.3.3.3 |
| 2 | 2.2.2.2 |
In order to prevent one user from requesting a song multiple times (abuse), I need to check whether a certain song has already been requested by the user which is just trying to request it again. So I'm doing a LEFT JOIN between the first and the second table and a GROUP BY by the row's Id which returns one row for each song.
PROBLEM: GROUP BY returns unpredictable values on fields which are not grouped. That is known. But How can I make sure that SELECT returns the row containing a specific IP, in case this IP exists in this group? If the IP does not exist, any other row of the group can be returned by SELECT.
Thanks a lot!
UPDATE: I need to show the song in a list, independent of how many users (or even none at all) have requested it. So SELECT definitely needs to return one row for every song. But in case that for example the user with IP 3.3.3.3 is trying to request song1, (which was already requested by him) I expect the query to return this:
| Id | Song_Name | By_IP |
+====+===========+=========+
| 1 | song1 | 3.3.3.3 | (3.3.3.3 in case it exists, otherwise anything else)
| 2 | song2 | 2.2.2.2 |
I also need the grouping with the other requests (IPs), because I need to get the whole number of requests per song as well. Therefore I use Count().
WORKAROUND: Since it seems to be pretty complicated to do what I need (if possible at all), I'm now working with a workaround. I'm using the GROUP_CONCAT() aggregate function. This delivers me all IPs of that group separated by ",". So I can search whether the one I'm searching for already exists there. The only drawback of this is, that the (default) maximum lenght of this returned string is 1024. That means that I can't handle a big amount of users, but for now it should be fine.
‑‑‑‑‑
參考解法
方法 1:
It is still unclear what do u want? there is no requested date present in table. without date how do u know when a particular song has been requested.
Select Songs.id, Songs.Song_name, requested_songs.By_IP from Songs INNER JOIN requested_songs on Songs.id = requested_songs.Requested_id Group BY requested_songs.Requested_id order by requested_songs.Requested_id ASC ;
SQLFiddle Demo:
方法 2:
Just group by with Song_Name and By_IP. Like this
SELECT * FROM `songs` JOIN users GROUP BY song_name, ip
方法 3:
Are you sure you're not overthinking your solution a bit? If all you want to do is eliminate duplicates, just put a UNIQUE index on your second table on both columns.
If you're trying to do something more complicated with that GROUP BY, please provide a sample resultset, as Quassnoi requested.
(by Bernd、Anam、Pramod、Naltharial)