删除没有 min(rid) 的行

Question

select min(q.rid)
    from qvalues q
        inner join batchinfo b
            on q.rowid = b.rowid
                and b.instrument = 'tf1'
    group by q.rowid, q.name, q.compound
    having count(*) > 1

1. 我如何删除除 min(rid) 之外的所有内容，而不是选择 min(rid)？
2. 我如何删除除 max(rid) 之外的所有内容？

请注意，我只想删除具有相同 rowid、名称和复合的值

Answer 1

begin transaction

delete from [table]
where rid != 
(select min(q.rid)
from qvalues q
    inner join batchinfo b
        on q.rowid = b.rowid
            and b.instrument = 'tf1'
group by q.rowid, q.name, q.compound
having count(*) > 1)
and rowid != 
(select q.rowid
from qvalues q
    inner join batchinfo b
        on q.rowid = b.rowid
            and b.instrument = 'tf1'
group by q.rowid, q.name, q.compound
having count(*) > 1)
and name != 
(select q.name
from qvalues q
    inner join batchinfo b
        on q.rowid = b.rowid
            and b.instrument = 'tf1'
group by q.rowid, q.name, q.compound
having count(*) > 1)
and compound != 
(select q.compound
from qvalues q
    inner join batchinfo b
        on q.rowid = b.rowid
            and b.instrument = 'tf1'
group by q.rowid, q.name, q.compound
having count(*) > 1)

要删除除 max 之外的所有内容，请使用 max 语法执行相同的操作