· 7 years ago · Sep 04, 2018, 09:18 AM
1SQL - Find all related tags?
2CREATE TABLE IF NOT EXISTS `relation` (
3 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
4 `tag_id` int(10) unsigned NOT NULL,
5 `node_id` int(10) unsigned NOT NULL,
6 PRIMARY KEY (`id`)
7) ;
8
9INSERT INTO `relation` (`id`, `tag_id`, `node_id`) VALUES
10(1, 1, 1),
11(2, 2, 1),
12(3, 3, 1),
13(4, 4, 1),
14(5, 1, 2),
15(6, 3, 2),
16(7, 4, 2),
17(8, 5, 2),
18(9, 1, 3),
19(10, 3, 3),
20(11, 5, 3),
21(12, 6, 3);
22
23tag_id count
243 3
254 2
265 2
276 1
282 1
29
30SELECT tag_id, COUNT(*) as count FROM relation
31WHERE tag_id != 1 AND node_id IN
32(
33 SELECT node_id FROM relation WHERE tag_id = 1
34)
35GROUP BY tag_id ORDER BY count DESC LIMIT 0,10
36
37select tag_id, Count(*) as TagCount
38from relation
39where tag_id <> 1
40group by tag_id
41order by Count(*) desc
42
43SELECT tag_id, COUNT(*)
44FROM relation
45GROUP BY tag_id
46ORDER BY COUNT(*) desc