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);
解决方案 »
- mysql 想用root登录却变成匿名用户
- 如何编译官网下的mysql源码
- 有关MySQL5.1版本在linux 4下的安装问题,急!!!
- 请大家帮帮我!
- Got error 134 from table handler(紧急求救)
- MYSQL ERROR:Can't get stat of 'C:\Program Files\Borland\CBuilder6\Projects\DBclient\Book1.csv'
- 有关mysql的几个小问题?
- 100分求EMS Mysql Manager的注册码!!!
- 请教如何按一定的顺序修改某列的值
- 根据Unique字段,有就更新,没有就插入数据该怎么写?
- mysql数据库设计
- Mysql5.5怎样编译集成infobright引擎?
贴建表及插入记录的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`;