SELECT * FROM (SELECT DISTINCT group_id FROM weibo_toprank_group_keywords WHERE tag LIKE 'a%' UNION ALL SELECT group_id FROM weibo_toprank_group_keywords WHERE tag LIKE 'b%' UNION ALL SELECT group_id FROM weibo_toprank_group_keywords WHERE tag LIKE 'c%') tbl GROUP BY group_id HAVING COUNT(*) = (
SELECT COUNT(*) cnt FROM (SELECT DISTINCT group_id FROM weibo_toprank_group_keywords WHERE tag LIKE 'a%' UNION ALL SELECT group_id FROM weibo_toprank_group_keywords WHERE tag LIKE 'b%' UNION ALL SELECT group_id FROM weibo_toprank_group_keywords WHERE tag LIKE 'c%') tbl GROUP BY group_id ORDER BY cnt DESC LIMIT 1);
SELECT COUNT(*) cnt FROM (SELECT DISTINCT group_id FROM weibo_toprank_group_keywords WHERE tag LIKE 'a%' UNION ALL SELECT group_id FROM weibo_toprank_group_keywords WHERE tag LIKE 'b%' UNION ALL SELECT group_id FROM weibo_toprank_group_keywords WHERE tag LIKE 'c%') tbl GROUP BY group_id ORDER BY cnt DESC LIMIT 1);
贴建表及插入记录的SQL,及要求结果出来看看
`group_id` int(11) NOT NULL,
`tag` varchar(50) NOT NULL,
`created_date` datetime DEFAULT NULL,
PRIMARY KEY (`group_id`,`tag`),
KEY `idx_tag` (`tag`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
需要查询tag在任意多的词中,匹配数量最多的group_id,这个表中的记录有几十万条(而且已每天3000多条的速度增长)。比如有如下记录:
group_id tag
1 aba
1 abb
1 cdd
2 bb
2 aba
3 ba
3 bbb查询符合a%,b%,c%的,结果应该是:1,2
from tb
where tag like 'a%' or tag like 'b%' or tag like 'c%'
group by group_id
order by num desc
limit 1;
在就是,同一个group_id,对同一个词,只算一次,也就是说, 1,as;1,add,对于a%,只能算1词而不是2次,所以我的语句中用了DISTINCT
from weibo_toprank_group_keywords a
where exists (select 1 from weibo_toprank_group_keywords where tag like 'a%' and group_id=a.group_id)
and exists (select 1 from weibo_toprank_group_keywords where tag like 'b%' and group_id=a.group_id)
and exists (select 1 from weibo_toprank_group_keywords where tag like 'c%' and group_id=a.group_id);
group_id tag
1 aba
1 abb
1 cdd
2 bb
2 aba
3 ba
3 bbb查询符合a%,b%,c%的,结果应该是:1,2为什么没有3?
SELECT `group_id`,COUNT(*) FROM (
SELECT a.`group_id` FROM weibo_toprank_group_keywords a
WHERE tag LIKE 'a%' GROUP BY a.`group_id`
UNION ALL
SELECT a.`group_id` FROM weibo_toprank_group_keywords a
WHERE tag LIKE 'b%' GROUP BY a.`group_id`
UNION ALL
SELECT a.`group_id` FROM weibo_toprank_group_keywords a
WHERE tag LIKE 'c%' GROUP BY a.`group_id`) aa GROUP BY `group_id`
SELECT a.`group_id`,COUNT(*) FROM weibo_toprank_group_keywords a WHERE NOT EXISTS(SELECT 1 FROM weibo_toprank_group_keywords WHERE a.`group_id`=`group_id` AND
(
a.`tag` LIKE 'a%' AND `tag` LIKE 'a%'
OR
a.`tag` LIKE 'b%' AND `tag` LIKE 'b%'
OR
a.`tag` LIKE 'c%' AND `tag` LIKE 'c%')
AND a.id<id) GROUP BY a.`group_id`;