时间戳

首页 > 解决方案 > 如何从连接中排除某些字符串

mysql - 如何从连接中排除某些字符串

问题描述

使用 mysql 5.7，我想查询article表中具有最相似tag列的行：

 CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `body` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `slug` varchar(100) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci NOT NULL,
  `lasthit` datetime DEFAULT CURRENT_TIMESTAMP,
  `tag1` varchar(100) NOT NULL DEFAULT 'NA',
  `tag2` varchar(100) NOT NULL DEFAULT 'NA',
  `tag3` varchar(100) NOT NULL DEFAULT 'NA',
  PRIMARY KEY (`id`),
  KEY `created_at` (`created_at`)
) ENGINE=InnoDB AUTO_INCREMENT=10800 DEFAULT CHARSET=utf8

这是我的查询：

SELECT newA.title, newA.slug, newA.tag1, newA.tag2, newA.tag3
        FROM article a
        JOIN article newA
          ON newA.tag1 IN (a.tag1, a.tag2, a.tag3) OR
             newA.tag2 IN (a.tag1, a.tag2, a.tag3) OR
             newA.tag3 IN (a.tag1, a.tag2, a.tag3)
        WHERE a.id = 242 
           AND newA.id != a.id
        ORDER BY
          (newA.tag1 IN (a.tag1, a.tag2, a.tag3) IS NOT NULL) +
          (newA.tag2 IN (a.tag1, a.tag2, a.tag3) IS NOT NULL) +
          (newA.tag3 IN (a.tag1, a.tag2, a.tag3) IS NOT NULL)
          DESC
        LIMIT 10;

我想排除标签长度小于 2 个字符的结果，以便不相关的项目（标签列为空或仅包含其中的项目）NA不会潜入。

我所做的是添加AND LEN(a.tag1)>2 AND LEN(a.tag2)>2 AND LEN(a.tag3)>2到WHERE子句，但后来我得到了很多Display all 1333 possibilities? (y or n)而不是结果。

我也试过

  ...
      ON (newA.tag1 IN (a.tag1, a.tag2, a.tag3) OR
         newA.tag2 IN (a.tag1, a.tag2, a.tag3) OR
         newA.tag3 IN (a.tag1, a.tag2, a.tag3)) AND
        (LEN(a.tag1)>2 AND LEN(a.tag2)>2 AND LEN(a.tag3)>2)        
  ...

但后来我得到

ERROR 1305 (42000): FUNCTION myawsomedb.LEN does not exist

我怎样才能解决这个问题？

标签： mysqlsqlsearch

解决方案

LENGTH() > 2结合您现有的标准和适当的括号使用：

SELECT newA.title, newA.slug, newA.tag1, newA.tag2, newA.tag3
FROM article a
JOIN article newA
  ON newA.id > a.id
  AND
  (
       (LENGTH(newA.tag1) > 2 AND newA.tag1 IN (a.tag1, a.tag2, a.tag3))
    OR (LENGTH(newA.tag2) > 2 AND newA.tag2 IN (a.tag1, a.tag2, a.tag3))
    OR (LENGTH(newA.tag3) > 2 AND newA.tag3 IN (a.tag1, a.tag2, a.tag3))
  )
WHERE a.id = 242
ORDER BY
  (LENGTH(newA.tag1) > 2 AND newA.tag1 IN (a.tag1, a.tag2, a.tag3)) +
  (LENGTH(newA.tag2) > 2 AND newA.tag2 IN (a.tag1, a.tag2, a.tag3)) +
  (LENGTH(newA.tag3) > 2 AND newA.tag3 IN (a.tag1, a.tag2, a.tag3))
  DESC
LIMIT 10;

（我做了这个newA.id > a.id，因为我希望“新” ID 大于旧 ID。newA.id <> a.id如果我错了，请将其改回。）

推荐阅读