sql – 选择具有匹配标记的所有项目

前端之家收集整理的这篇文章主要介绍了sql – 选择具有匹配标记的所有项目前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
我正试图找到最有效的方法解决这个问题,但我必须告诉你,我已经搞砸了它.环顾四周,没有发现任何相关性,所以在这里.

如何选择与所需项目具有相似标签的所有项目?

以此表为例:
(用于重新创建表格的sql代码)

  1. project 1 -> tagA | tagB | tagC
  2. project 2 -> tagA | tagB
  3. project 3 -> tagA
  4. project 4 -> tagC

选择项目1应返回所有项目.
选择项目4应仅返回项目项目1

到目前为止,我的查询非常依赖于左连接,并且肯定有更好的方法来执行此操作:

  1. SELECT all_tags.project_id,all_tags.tag_id,final.title,tag.tag
  2. FROM projects AS p
  3. LEFT JOIN projects_to_tags AS pt ON p.num = pt.project_id
  4. LEFT JOIN projects_to_tags AS all_tags ON pt.tag_id = all_tags.tag_id
  5. LEFT JOIN projects AS final ON all_tags.project_id = final.num
  6. LEFT JOIN tags AS tag ON all_tags.tag_id = tag.tag_id
  7. WHERE p.num = 4
  8. GROUP BY final.num

谢谢大家的意见.我虽然与大家分享了100k项目数据库上所有查询的平均结果,100k标签数据库与100k projects_to_tags关系.所有查询都已更改为要求project_1.

甜蜜和短暂:

  1. 0.0160 sec - OMG Ponies - Using JOINS
  2. 0.0208 sec - jdelard
  3. 0.2581 sec - OMG Ponies - Using EXISTS
  4. 0.2777 sec - OMG Ponies - Using IN
  5. 0.5295 sec - Emtucifor - updated query
  6. 0.5088 sec - Emtucifor - first query

非常感谢大家.将相应地更新我的所有查询.

这里包括所有查询和各自的MysqL EXPLAIN以及时间

  1. ===============================================================================================================================================
  2. Emtucifor - updated query
  3. ===============================================================================================================================================
  4. Showing rows 0 - 1 (2 total,Query took 0.5295 sec)
  5. SELECT *
  6. FROM projects AS L
  7. WHERE L.num !=1-- instead of <> PT2.project_id inside
  8.  
  9. AND EXISTS (
  10.  
  11. SELECT 1
  12. FROM projects_to_tags PT
  13. INNER JOIN projects_to_tags PT2 ON PT.tag_id = PT2.tag_id
  14. WHERE L.num = PT.project_id
  15. AND PT2.project_id =1
  16. )
  17. LIMIT 0,30
  18.  
  19. id select_type table type possible_keys key key_len ref rows Extra
  20. 1 PRIMARY L ALL PRIMARY NULL NULL NULL 100000 Using where
  21. 2 DEPENDENT SUBQUERY PT2 ref project_id project_id 4 const 1 Using index
  22. 2 DEPENDENT SUBQUERY PT ref project_id project_id 8 test.L.num,test.PT2.tag_id 12000 Using index
  23.  
  24.  
  25.  
  26.  
  27. ===============================================================================================================================================
  28. Emtucifor - first query
  29. ===============================================================================================================================================
  30. Showing rows 0 - 1 (2 total,Query took 0.5088 sec)
  31. SELECT *
  32. FROM projects AS L
  33. WHERE
  34. EXISTS (
  35.  
  36. SELECT 1
  37. FROM projects_to_tags PT
  38. INNER JOIN projects_to_tags PT2 ON PT.tag_id = PT2.tag_id
  39. WHERE L.num = PT.project_id
  40. AND PT2.project_id =1
  41. AND PT2.project_id <> L.num
  42. )
  43. LIMIT 0,30
  44.  
  45. id select_type table type possible_keys key key_len ref rows Extra
  46. 1 PRIMARY L ALL NULL NULL NULL NULL 100000 Using where
  47. 2 DEPENDENT SUBQUERY PT2 ref project_id project_id 4 const 1 Using index
  48. 2 DEPENDENT SUBQUERY PT ref project_id project_id 8 test.L.num,test.PT2.tag_id 12000 Using where; Using index
  49.  
  50.  
  51.  
  52.  
  53. ===============================================================================================================================================
  54. jdelard
  55. ===============================================================================================================================================
  56. Showing rows 0 - 1 (2 total,Query took 0.0208 sec)
  57. SELECT p.num,p.title
  58. FROM projects_to_tags pt1,projects_to_tags pt2,projects p
  59. WHERE pt1.project_id =1
  60. AND pt2.project_id !=1
  61. AND pt1.tag_id = pt2.tag_id
  62. AND p.num = pt2.project_id
  63. GROUP BY pt2.project_id
  64. LIMIT 0,30
  65.  
  66. id select_type table type possible_keys key key_len ref rows Extra
  67. 1 SIMPLE pt1 ref project_id project_id 4 const 1 Using index; Using temporary; Using filesort
  68. 1 SIMPLE pt2 index project_id project_id 8 NULL 75001 Using where; Using index
  69. 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 test.pt2.project_id 1
  70.  
  71.  
  72.  
  73.  
  74. ===============================================================================================================================================
  75. OMG Ponies - Using IN
  76. ===============================================================================================================================================
  77. Showing rows 0 - 2 (3 total,Query took 0.2777 sec)
  78. SELECT p . *
  79. FROM projects p
  80. JOIN projects_to_tags pt ON pt.project_id = p.num
  81. WHERE pt.tag_id
  82. IN (
  83.  
  84. SELECT x.tag_id
  85. FROM projects_to_tags x
  86. WHERE x.project_id =1
  87. )
  88. LIMIT 0,30
  89.  
  90. id select_type table type possible_keys key key_len ref rows Extra
  91. 1 PRIMARY pt index project_id project_id 8 NULL 100001 Using where; Using index
  92. 1 PRIMARY p eq_ref PRIMARY PRIMARY 4 test.pt.project_id 1
  93. 2 DEPENDENT SUBQUERY x ref project_id project_id 8 const,func 12000 Using where; Using index
  94.  
  95.  
  96.  
  97.  
  98. ===============================================================================================================================================
  99. OMG Ponies - Using EXISTS
  100. ===============================================================================================================================================
  101. Showing rows 0 - 2 (3 total,Query took 0.2581 sec)
  102. SELECT p . *
  103. FROM projects p
  104. JOIN projects_to_tags pt ON pt.project_id = p.num
  105. WHERE EXISTS (
  106.  
  107. SELECT NULL
  108. FROM projects_to_tags x
  109. WHERE x.project_id = 1
  110. AND x.tag_id = pt.tag_id
  111. )
  112. LIMIT 0,30
  113.  
  114.  
  115.  
  116.  
  117. ===============================================================================================================================================
  118. OMG Ponies - Using JOINS
  119. ===============================================================================================================================================
  120. Showing rows 0 - 2 (3 total,Query took 0.0160 sec)
  121. SELECT DISTINCT p . *
  122. FROM projects p
  123. JOIN projects_to_tags pt ON pt.project_id = p.num
  124. JOIN projects_to_tags x ON x.tag_id = pt.tag_id
  125. AND x.project_id = 1
  126. LIMIT 0,30
  127.  
  128. id select_type table type possible_keys key key_len ref rows Extra
  129. 1 SIMPLE x ref project_id project_id 4 const 1 Using index; Using temporary
  130. 1 SIMPLE pt index project_id project_id 8 NULL 75001 Using where; Using index
  131. 1 SIMPLE p eq_ref PRIMARY PRIMARY 4 test.pt.project_id 1

sql代码复制/粘贴和乱七八糟.

  1. CREATE TABLE IF NOT EXISTS `projects` (
  2. `num` int(2) NOT NULL auto_increment,`title` varchar(30) NOT NULL,PRIMARY KEY (`num`)
  3. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
  4.  
  5.  
  6. INSERT INTO `projects` (`num`,`title`) VALUES(1,'project 1'),(2,'project 2'),(3,'project 3'),(4,'project 4');
  7.  
  8.  
  9. CREATE TABLE IF NOT EXISTS `projects_to_tags` (
  10. `project_id` int(2) NOT NULL,`tag_id` int(2) NOT NULL,KEY `project_id` (`project_id`,`tag_id`)
  11. ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
  12.  
  13.  
  14. INSERT INTO `projects_to_tags` (`project_id`,`tag_id`) VALUES(1,1),(1,2),3),3);
  15.  
  16.  
  17. CREATE TABLE IF NOT EXISTS `tags` (
  18. `tag_id` int(2) NOT NULL auto_increment,`tag` varchar(30) NOT NULL,PRIMARY KEY (`tag_id`),UNIQUE KEY `tag` (`tag`)
  19. ) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
  20.  
  21.  
  22. INSERT INTO `tags` (`tag_id`,`tag`) VALUES(1,'tag a'),'tag b'),'tag c');

解决方法

在以下任何一种情况下,如果您不知道PROJECT.num / PROJECT_TO_TAGS.project_id,则必须加入PROJECTS表以获取id值以找出它关联的标记.

使用IN

  1. SELECT p.*
  2. FROM PROJECTS p
  3. JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num
  4. WHERE pt.tag_id IN (SELECT x.tag_id
  5. FROM PROJECTS_TO_TAGS x
  6. WHERE x.project_id = 4)

使用EXISTS

  1. SELECT p.*
  2. FROM PROJECTS p
  3. JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num
  4. WHERE EXISTS (SELECT NULL
  5. FROM PROJECTS_TO_TAGS x
  6. WHERE x.project_id = 4
  7. AND x.tag_id = pt.tag_id)

使用JOINS(这是最有效的!)

DISTINCT是必要的,因为JOIN会冒结果集中出现重复数据的风险……

  1. SELECT DISTINCT p.*
  2. FROM PROJECTS p
  3. JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num
  4. JOIN PROJECTS_TO_TAGS x ON x.tag_id = pt.tag_id
  5. AND x.project_id = 4

猜你在找的MsSQL相关文章