mysql - SQL COUNT 与 2 INNER JOINS

Question

video表存储id和video数据。

标签表存储 id 和 tag_name。

video_tag 表连接video_ids 和tag_ids 表示哪个视频属于哪个标签。

例如，在下面的查询中，我可以获得属于 ID 为 both 3 和 4 的标签的视频

另外，我想知道有多少行。我应该如何修改查询？

SELECT *
            FROM video
            INNER JOIN video_tag ON video.id = video_tag.video_id
            INNER JOIN tag ON tag.id = video_tag.tag_id
            WHERE video_tag.tag_id IN (3,4)
            GROUP BY video.id
            HAVING COUNT(video.id)=2
            ORDER BY video.id DESC 

---

*

表结构:

   --
    -- Table structure for table `video`
    --

    CREATE TABLE IF NOT EXISTS `video` (
      `id` int(11) NOT NULL AUTO_INCREMENT,
      `original_id` varchar(20) COLLATE utf8_turkish_ci NOT NULL COMMENT 'alınan sitedeki id''si',
      `source` tinyint(2) NOT NULL,
      `title` varchar(160) COLLATE utf8_turkish_ci NOT NULL,
      `link` varchar(250) COLLATE utf8_turkish_ci NOT NULL,
      `image` varchar(300) COLLATE utf8_turkish_ci NOT NULL,
      `seconds` smallint(6) NOT NULL,
      `fullscreen` varchar(100) COLLATE utf8_turkish_ci NOT NULL,
      PRIMARY KEY (`id`),
      KEY `source` (`source`,`seconds`)
    ) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=122987 ;

--
-- Table structure for table `tag`
--

CREATE TABLE IF NOT EXISTS `tag` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `tag_name` varchar(24) COLLATE utf8_turkish_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `tag_name` (`tag_name`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_turkish_ci AUTO_INCREMENT=191 ;
--
-- Table structure for table `video_tag`
--

CREATE TABLE IF NOT EXISTS `video_tag` (
  `video_id` int(11) NOT NULL,
  `tag_id` int(11) NOT NULL,
  KEY `video_id` (`video_id`,`tag_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Answer 1

您的查询应该如您所愿。但是，您可以简化它:

SELECT v.*
FROM video v INNER JOIN
     video_tag vt
     ON v.id = vt.video_id
WHERE vt.tag_id IN (3, 4)
GROUP BY v.id
HAVING COUNT(v.id) = 2
ORDER BY v.id DESC ;

只有在视频可能具有相同类型的重复标签时，此方法才行不通。在这种情况下，您可以改用 COUNT(DISTINCT)。

如果您想返回包含行数的查询，例如分页，请使用 SQL_CALC_FOUND_ROWS:

SELECT SQL_CALC_FOUND_ROWS v.*
. . .

然后使用 FOUND_ROWS()。

如果只是想要行数，可以使用子查询，进一步简化:

SELECT COUNT(*)
FROM (SELECT v.*
      FROM video_tag vt
      WHERE vt.tag_id IN (3, 4)
      GROUP BY vt.id
      HAVING COUNT(*) = 2
     ) t