SQL - 列中的类似数据-解网

问：

有没有办法在列中找到类似的结果。例：

我想要从没有 4 棵绿树的表数据中查询返回，因为没有与绿树类似的数据，但蓝色汽车类似于蓝色汽车，红色娃娃类似于红色小车。

如何做到这一点？

我使用 Microsoft SQL Server Management Studio

SQL 服务器

create table myTest
(
    id int,
    name varchar(20)
);

insert into myTest values(1, 'blue car');
insert into myTest values(2, 'red doll');
insert into myTest values(3, 'blue cars');
insert into myTest values(4, 'green tree');
insert into myTest values(5, 'red dolly');

select left(name,8), count(*) 
from myTest 
group by left(name,8) 
having count(*) > 1;

4赞 Rich Benner 6/10/2016 #2

你可以用它来做这件事。SOUNDEX

样本数据;

CREATE TABLE #SampleData (Column1 int, Column2 varchar(10))
INSERT INTO #SampleData (Column1, Column2)
VALUES
(1,'blue car')
,(2,'red doll')
,(3,'blue cars')
,(4,'green tree')
,(5,'red dolly')

以下代码将用于创建中类似条目的列表。然后，它使用不同的子查询来查看该字段的出现次数;soundexcolumn2soundex

SELECT
a.GroupingField
,a.Title
,b.SimilarFields
FROM (
        SELECT
        SOUNDEX(Column2) GroupingField
        ,MAX(Column2) Title --Just return a unique title for this soundex group
        FROM #SampleData
        GROUP BY SOUNDEX(Column2)
      ) a
LEFT JOIN   (
                SELECT
                SOUNDEX(Column2) GroupingField
                ,COUNT(Column2) SimilarFields --How many fields are in the soundex group?
                FROM #SampleData
                GROUP BY SOUNDEX(Column2)
            ) b
ON a.GroupingField = b.GroupingField
WHERE b.SimilarFields > 1

结果如下所示（我保留了字段以向您展示它的样子）;soundex

GroupingField   Title       SimilarFields
B400            blue cars   2
R300            red dolly   2

关于 https://msdn.microsoft.com/en-gb/library/ms187384.aspx 的一些进一步阅读soundex

编辑：根据您的要求，要获取原始数据，您不妨推送到临时表中，将我给您的查询更改为在语句之前放置一个;INTOFROM

SELECT
a.GroupingField
,a.Title
,b.SimilarFields
INTO #Duplicates
FROM (
        SELECT
        SOUNDEX(Column2) GroupingField
        ,MAX(Column2) Title --Just return a unique title for this soundex group
        FROM #SampleData
        GROUP BY SOUNDEX(Column2)
      ) a
LEFT JOIN   (
                SELECT
                SOUNDEX(Column2) GroupingField
                ,COUNT(Column2) SimilarFields --How many fields are in the soundex group?
                FROM #SampleData
                GROUP BY SOUNDEX(Column2)
            ) b
ON a.GroupingField = b.GroupingField
WHERE b.SimilarFields > 1

然后，使用以下查询链接回原始数据;

SELECT
a.GroupingField
,a.Title
,a.SimilarFields
,b.Column1
,b.Column2
FROM #Duplicates a
JOIN #SampleData b
ON a.GroupingField = SOUNDEX(b.Column2)
ORDER BY a.GroupingField

将给出以下结果;

GroupingField   Title       SimilarFields   Column1     Column2
B400            blue cars   2               1           blue car
B400            blue cars   2               3           blue cars
R300            red dolly   2               5           red dolly
R300            red dolly   2               2           red doll

记得

DROP TABLE #Differences

create table phrases ( phrase varchar(max) )
insert phrases values( 'blue car' ), ( 'blue cars' ), ('green tree' ), ( 'red doll' ), ( 'red dolly' )

create function dbo.fnSimilar( @s1 varchar(max), @s2 varchar(max) )
returns int
begin
    if @s1 = @s2 return 0 -- a phrase is not similar to itself
    if @s1 like @s2 + '%' return 1
    if @s2 like @s1 + '%' return 2
    return 0
end

select x.phrase, similar = count(1) + 1 from 
(
    select p1.phrase from phrases p1
    inner join phrases p2 on dbo.fnSimilar( p2.phrase, p1.phrase ) = 1
) x
group by x.phrase

结果：

phrase      similar
--------    -------
blue car    2
red doll    2

上一个：如何在警报对话框 Android 中加载现有 WebView

下一个：Laravel 5 多语言不起作用

SQL - 列中的类似数据

SQL - similar data in column

评论

评论