是否有用于“CONTAINS”的MySQL聚合函数? [英] Is there any MySQL Aggregate Function for "CONTAINS"?
本文介绍了是否有用于“CONTAINS”的MySQL聚合函数?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
假设我有这个数据集
user | group
-------- + -------
a@a.com | A
a@a.com | B
b@b.com | A
c@c.com | B
d@d.com | A
d@d.com | B
d@d.com | C
我想将它转换为如下表格:
user | IN_A | IN_B | IN_C
-------- + ------- + ------- + -------
a@a.com | TRUE | TRUE | FALSE
b@b.com | TRUE | FALSE | FALSE
c@c.com | FALSE | TRUE | FALSE
d@d.com | TRUE | TRUE | TRUE
我有:
SELECT(
)用户,
IF(LOCATE('A',GROUP_CONCAT(group))> 0,TRUE,FALSE)AS IN_A,
IF(LOCATE('B',GROUP_CONCAT(group) )> 0,TRUE,FALSE)AS IN_B,
IF(LOCATE('C',GROUP_CONCAT(group))> 0,TRUE,FALSE)AS IN_C
FROM用户
GROUP BY用户
我想知道的是,如果有更好的方法来知道聚合字段是否包含值,或者这是唯一的
解决方案
正确的做法:
<$ p $ (SUM(group ='A'),TRUE,FALSE)AS IN_A,
IF(SUM(group ='B' ),TRUE,FALSE)AS IN_B,
IF(SUM(group ='C'),TRUE,FALSE)AS IN_C
FROM用户
GROUP BY用户
Say I have this data set
user | group --------+------- a@a.com | A a@a.com | B b@b.com | A c@c.com | B d@d.com | A d@d.com | B d@d.com | C
I want to convert this into a table like this:
user | IN_A | IN_B | IN_C --------+-------+-------+------- a@a.com | TRUE | TRUE | FALSE b@b.com | TRUE | FALSE | FALSE c@c.com | FALSE | TRUE | FALSE d@d.com | TRUE | TRUE | TRUE
I've got:
SELECT user, IF(LOCATE('A', GROUP_CONCAT(group)) > 0, TRUE, FALSE) AS IN_A, IF(LOCATE('B', GROUP_CONCAT(group)) > 0, TRUE, FALSE) AS IN_B, IF(LOCATE('C', GROUP_CONCAT(group)) > 0, TRUE, FALSE) AS IN_C FROM users GROUP BY user
What I'm wondering is if there is a better way to known if an aggregated field contains a value, or if this is the only way?
解决方案
The right way to do it:
SELECT
user,
IF(SUM(group = 'A'), TRUE, FALSE) AS IN_A,
IF(SUM(group = 'B'), TRUE, FALSE) AS IN_B,
IF(SUM(group = 'C'), TRUE, FALSE) AS IN_C
FROM users
GROUP BY user
这篇关于是否有用于“CONTAINS”的MySQL聚合函数?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文