选择最多2行，其中列具有相同的值

I am trying to select a maximum of 2 rows where a column has the same value. ie:

我试图选择最多2行，其中列具有相同的值。即：

id  title   accountid   date
1   job 1       1      Oct. 1
2   job 2       1      Oct. 1
3   job 3       1      Oct. 1
4   job 1       2      Oct. 2
5   job a       3      Oct. 2
6   job z       4      Oct. 3
7   job 2       2      Oct. 3
8   job 3       2      Oct. 8

I want to select

我想选择

    1   job 1       1      Oct. 1
    2   job 2       1      Oct. 1
                                   <----- Skip this row because we already 
                                          have 2 from account 1
    4   job 1       2      Oct. 2
    5   job a       3      Oct. 2
    6   job z       4      Oct. 3
    7   job 2       2      Oct. 3

What I'm using right now to select is something like this:

我现在正在使用的选择是这样的：

SELECT * 
FROM table 
ORDER BY date DESC, RAND()

I've looked a bit into using HAVING COUNT(accountid) <= 2 but that's just led to confusion. I'm pretty new to using sql.

我看起来有点使用HAVING COUNT（accountid）<= 2但这只是导致混乱。我很擅长使用sql。

Thanks for your help!

谢谢你的帮助！

UPDATE:

更新：

Hi, thanks for all the quick responses. I've tried each of them and couldn't seem to get them to work. I figured out a way to limit the jobs per account id using php. Thanks again for your time and efforts to help me solve this.

嗨，谢谢你所有的快速反应。我已经尝试了他们每个人，似乎无法让他们工作。我想出了一种使用php限制每个帐户ID的作业的方法。再次感谢您花时间和精力帮助我解决这个问题。

3 个解决方案

#1

set @id := 0, @acid := 0;
select t.id, title, accountid, `date`
from 
    t
    inner join (
        select 
            id, 
            if(@acid = accountid, @i := @i + 1, @i := 1) as i,
            @acid := accountid as acid
        from t
        order by accountid, `date` desc
    ) s on t.id = s.id
where s.i <= 2

#2

Hi this gonna solve your problem, but I don't know how quick this query will be.

嗨，这将解决您的问题，但我不知道这个查询有多快。

SQLFIDDLE example

SQLFIDDLE示例

QUERY:

查询：

SELECT
id, 
title,
accountid,
date
FROM
    (SELECT
     IF(@prev != a.accountid, @rownum:=1, @rownum:=@rownum+1) as rownumber, 
     @prev:=a.accountid, 
     a.*
     FROM (
           SELECT 
           t1.id, 
           t1.title,
           t1.accountid,
           t1.date
           FROM tbl t1,
           (SELECT @rownum := 0, @prev:='') sq
              ORDER BY  accountid, id) a
    )b
WHERE b.rownumber<3
ORDER BY b.id

Result:

结果：

| ID | TITLE | ACCOUNTID |   DATE |
-----------------------------------
|  1 | job 1 |         1 | Oct. 1 |
|  2 | job 2 |         1 | Oct. 1 |
|  4 | job 1 |         2 | Oct. 2 |
|  5 | job a |         3 | Oct. 2 |
|  6 | job z |         4 | Oct. 3 |
|  7 | job 2 |         2 | Oct. 3 |

#3

SELECT tab.id, tab.title, tab.accountid, tab.date
FROM table tab INNER JOIN table count_tab ON tab.id = count_tab.id
WHERE count_tab.id <= tab.id
GROUP BY tab.id, tab.title, tab.accountid, tab.date
HAVING count(count_tab.id) <= 2

#1

set @id := 0, @acid := 0;
select t.id, title, accountid, `date`
from 
    t
    inner join (
        select 
            id, 
            if(@acid = accountid, @i := @i + 1, @i := 1) as i,
            @acid := accountid as acid
        from t
        order by accountid, `date` desc
    ) s on t.id = s.id
where s.i <= 2

#2