mysql如何选中一行,mysql-如何根据SQL中的条件在组中选择一行？

题

请考虑下表：

+--------------+--------+--------+

| transactionID | Sgroup | Rgroup |

+--------------+--------+--------+

| 1 | A | I |

| 1 | A | J |

| 2 | B | B |

| 2 | B | K |

+--------------+--------+--------+

对于每个transactionID(2行与ID 1相关联,两行与ID 2相关联),如果transactionID中的任何行都满足条件,我想选择Sgroup = Rgroup的行.否则,我想随机选择一行.对于每个transactionID,最多一行满足Sgroup = Rgroup.我怎样才能做到这一点？

尝试的解决方案

我知道如何选择满足条件Sgroup = Rgroup的行,如下所示：

SELECT *

FROM Transaction

WHERE Sgroup = Rgroup;

+---------------+--------+--------+

| transactionID | Sgroup | Rgroup |

+---------------+--------+--------+

| 2 | B | B |

+---------------+--------+--------+

我还知道如果不满足以下条件,如何随机选择一行(感谢this question)：

SELECT * FROM

(SELECT *

FROM Transaction

WHERE NOT transactionID IN

(SELECT transactionID

FROM Transaction

WHERE Sgroup = Rgroup)

ORDER BY RAND()) AS temp

GROUP BY temp.transactionID;

+---------------+--------+--------+

| transactionID | Sgroup | Rgroup |

+---------------+--------+--------+

| 1 | A | I |

+---------------+--------+--------+

如何将这两个表达式合而为一？我尝试使用一个CASE表达式,但我做得还不够.有人可以提出解决方案吗？

示例代码以下是生成表的代码：

CREATE DATABASE MinimalExample;

USE MinimalExample;

CREATE TABLE Transaction (

transactionID int,

Sgroup nvarchar(1),

Rgroup nvarchar(1)

);

INSERT INTO Transaction VALUES

(1,'A','I'),

(1,'A','J'),

(2,'B','B'),

(2,'B','K');

解决方法:

我认为,如果您真的要说“随机”,那么变量可能是最简单的解决方案：

select t.*

from (select t.*,

(@rn := if(@i = transactionID, @rn + 1,

if(@i := transactionID, 1, 1)

)

) as rn

from (select t.*

from t

order by transactionID, (sgroup = rgroup) desc, rand()

) t cross join

(select @i := -1, @rn := 0) params

) t

where rn = 1;

如果用“随机”的意思是“任意的”,则可以使用以下快速技巧：

(select t.*

from t

where sgroup = rgroup

)

union all

(select t.*

from t

where not exists (select 1 from t t2 where t2.id = t.id and t2.sgroup = t2.rgroup)

group by transactionID

);

这将可怕的select *与group by一起使用,在几乎所有情况下,我都强烈建议不要使用此选项.但是,在这种情况下,您正在专门尝试将每个组减少为不确定的行,因此看起来并不那么糟糕.我将注意到,MySQL实际上并不保证结果集中的所有列都来自同一行.

最后,如果每行上都有一个唯一的主键,则可以使用最简单的解决方案：

select t.*

from t

where t.id = (select t2.id

from t t2

where t2.transactionID = t.transactionID

order by (rgroup = sgroup) desc, rand()

);

标签：select,sql,mysql,group-by

来源： https://codeday.me/bug/20191109/2011353.html