我有这个表,我们称之为表一.
@H_502_6@+----+---------+-----------------+ | id | link_id | url | +----+---------+-----------------+ | 1 | 1 | www.example.com | | 2 | 1 | www.abc.com | | 3 | 1 | www.test.com | | 4 | 1 | www.t1.com | | 5 | 1 | www.newtest.com | | 6 | 1 | www.testing.com | | 7 | 1 | www.abc.com | | 8 | 1 | www.example.com | | 9 | 1 | www.web1.com | | 10 | 1 | www.web2.com | | 11 | 2 | www.dear.com | | 12 | 2 | www.google.com | | 13 | 2 | www.flowers.com | | 14 | 2 | www.yahoo.com | | 15 | 2 | www.abc.com | | 16 | 2 | www.dell.com | | 17 | 2 | www.web.com | | 18 | 2 | www.example.com | | 19 | 2 | www.test.com | | 20 | 2 | www.abc.com | +----+---------+-----------------+ 20 rows in set (0.00 sec)
link_id是表中的主要标识符.它告诉我哪些网址出现在链接1,链接2等.
我想要的是:
1.获取所有唯一的URL,
2.显示URL所属的链接
因此,示例输出将是:
@H_502_6@+-----------------+---------+ | url | link_id | +-----------------+---------+ | www.example.com | 1 | | www.example.com | 2 | | www.abc.com | 1 | | www.abc.com | 2 | | www.test.com | 1 | | www.test.com | 2 | | www.t1.com | 1 | | www.newtest.com | 1 | | www.testing.com | 1 | | www.web1.com | 1 |
…等等.
因此,您可以看到www.example.com出现两次,因为它与链接1和2相关联,但web1.com只出现一次,因为它只属于链接1.
我已经尝试了几个不同的小组,但我最终只是更多地摸不着头脑.
任何帮助表示赞赏.如果有人需要,这是表转储:
@H_502_6@CREATE TABLE IF NOT EXISTS `table1` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT,`link_id` tinyint(3) unsigned DEFAULT NULL,`url` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=21 ; INSERT INTO `table1` (`id`,`link_id`,`url`) VALUES (1,1,'www.example.com'),(2,'www.abc.com'),(3,'www.test.com'),(4,'www.t1.com'),(5,'www.newtest.com'),(6,'www.testing.com'),(7,(8,(9,'www.web1.com'),(10,'www.web2.com'),(11,2,'www.dear.com'),(12,'www.google.com'),(13,'www.flowers.com'),(14,'www.yahoo.com'),(15,(16,'www.dell.com'),(17,'www.web.com'),(18,(19,(20,'www.abc.com');
最佳答案