如何在SQL中查找以相同字符串开头的行(相似的行)? [英] How to find rows in SQL that start with the same string (similar rows)?

问题描述

我有一个带有主键的表，如下所示:

I have a table with primary keys that look like this:

FIRSTKEY〜ABC

FIRSTKEY~ABC

SECONDKEY〜DEF

SECONDKEY~DEF

FIRSTKEY〜DEF

FIRSTKEY~DEF

我想编写一条SELECT语句，将波浪号后的段剥离，并在波浪后段消失后返回所有重复的行.就是

I want to write a SELECT statement that strips off the segment following the tilde and returns all rows that are duplicates after the post-tilde segment is gone. That is,

选择...

给我:

FIRSTKEY〜ABC

FIRSTKEY~ABC

FIRSTKEY〜DEF

FIRSTKEY~DEF

为重复项".

我已经可以使用SUBSTRING剥离末端段了:

I already have the bit to strip off the end segment using SUBSTRING:

从表中选择SUBSTRING(COLUMN，0，CHARINDEX('〜'，COLUMN))

SELECT SUBSTRING(COLUMN, 0, CHARINDEX('~', COLUMN)) FROM TABLE

这是在SQL Server上.

This is on SQL Server.

推荐答案

给出的第一个解决方案将标识键前缀；稍微扩展一下，以使表行以这些键开头:

The first solution given will identify the key prefixes; extend it just a bit to get the table rows beginning with those keys:

SELECT * 
FROM TABLE
WHERE SUBSTRING(COLUMN, 0, CHARINDEX('~', COLUMN)) IN
(
    SELECT SUBSTRING(COLUMN, 0, CHARINDEX('~', COLUMN)) FROM TABLE 
    GROUP BY SUBSTRING(COLUMN, 0, CHARINDEX('~', COLUMN))
    HAVING COUNT(*) > 1
)

或者，您可以在包含前缀的临时表和原始表之间使用联接-如果前缀的数量很大，则使用"where in"可能会变得非常昂贵.

Alternately, you could use a join between a temp table containing the prefixes and the original table - if the number of prefixes becomes very large, using a "where in" can become very expensive.

这篇关于如何在SQL中查找以相同字符串开头的行(相似的行)?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！