按结果数量排序SQL查询

I want to do the following:

我想做以下事情:

$searchParams is an array, which contains some strings (dynamicly generated).

$ searchParams是一个数组,包含一些字符串(动态生成)。

Now the Statement would be something like this:

现在声明将是这样的:

SELECT manufacturer FROM shop_articles WHERE manufacturer LIKE '".$searchParams."%'

But what I want is the result with the most matches. Can I code that in one statement? So it would be something like

但我想要的是最匹配的结果。我可以在一个声明中编写代码吗?所以它会是这样的

ORDER BY MATCHES DESC

How do I do that?

我怎么做?

6 个解决方案

#1

For each param in your searchParam array you have to make a like clause

对于searchParam数组中的每个参数,您必须创建一个like子句

    SELECT Manufacturer, COUNT(*) AS Matches FROM
    FROM shop_articles WHERE (
manufacturer LIKE '".$searchParams[0]."%' OR
manufacturer LIKE '".$searchParams[1]."%' OR 
...
manufacturer LIKE '".$searchParams[n]."%' OR )
    GROUP BY Manufacturer
    ORDER BY Matches

#2

SELECT COUNT(*) AS matches, manufacturer FROM shop_articles WHERE manufacturer LIKE '".$searchParams."%' GROUP BY(manufaturer) ORDER BY matches ASC

#3

Actually you have to measure matching (likeness) between manufacturer and $searchParam. Unfortunatelly LIKE does not provide such functionality. You may use Lavenshtein distances. See this post - Implementation of Levenshtein distance for mysql/fuzzy search?

实际上你必须测量制造商和$ searchParam之间的匹配(相似)。不幸的是,LIKE没有提供这样的功能。你可以使用Lavenshtein距离。看到这篇文章 - 用于mysql /模糊搜索的Levenshtein距离的实现?

#4

SELECT manufacturer, COUNT(manufacturer) FROM shop_articles WHERE manufacturer LIKE '".$searchParams."' GROUP BY manufacturer ORDER BY COUNT(manufacturer) DESC

SELECT制造商,COUNT(制造商)FROM shop_articles WHERE制造商LIKE'“。$ searchParams。”'GROUP BY制造商ORDER BY COUNT(制造商)DESC

#5

No, you cannot submit an array to the LIKE operator. It is more tedious than that. :-)

不,您无法向LIKE运算符提交数组。这比那更乏味。 :-)

When your data are not yet properly regularized, so that you have variants of the manufacturer name in the PRODUCTS table (e.g. "HP", "Hewlett Packard") rather than an integer ManufacturerID, you have to go through the grunt work of reducing those variants to a single entity.

如果您的数据尚未正确规范化,那么您在PRODUCTS表中有制造商名称的变体(例如“HP”,“Hewlett Packard”)而不是整数ManufacturerID,那么您必须经历减少这些数据的繁琐工作变体到单个实体。

A typical approach for doing that (quite unavoidable) work is to create a Manufacturers table like this:

这样做(非常不可避免)工作的典型方法是创建一个像这样的制造商表:

  Table: MANUFACTURER

                   manufacturerid  INTEGER primary key
                   manufacturername  varchar
                   primarymanufacturerid  INTEGER  FOREIGN KEY REFERENCES MANUFACTURER(manufacturerid)

The last column allows you to associate a variant name (e.g. "HP") with the row where the main manufacturer record is stored (e.g. "Hewlett Packard").

最后一列允许您将变体名称(例如“HP”)与存储主制造商记录的行(例如“Hewlett Packard”)相关联。

                   124,   Hewlett Packard,    124
                   367,   HP,                 124

The row where primarymanufacturerid = manufacturerid is the the main entity.

primarymanufacturerid = manufacturerrid的行是主要实体。

Then you could do this during an interim cleanup phase when you have not yet added a manufacturerid to the PRODUCTS table but it still has the name:

然后,您可以在临时清理阶段执行此操作,此时尚未将productsrid添加到PRODUCTS表,但它仍具有以下名称:

                 select * from products 
                 where manufacturer in
                 (
                   select manufacturername from manufacturer
                   where primarymanufacturerid = 
                    (
                     select primarymanufacturerid from manufacturer
                     where manufacturername = 'Hewlett Packard'
                     )
                 )

P.S. With a database engine that had support for functions and stored procedures, you could write your own function that accepted a delimited string of name variations, built a dynamic SQL statement, possibly using a temporary table to store the variant names one name per row, and returned a count of the matches. This would be a resource-intensive approach recommended only to assist in the clean-up phase -- not something I'd put into production for end-users to consume as their daily bread.

附:使用支持函数和存储过程的数据库引擎,您可以编写自己的函数,接受分隔的名称变体字符串,构建动态SQL语句,可能使用临时表将变量名称存储为每行一个名称,以及返回了一系列的比赛。这将是一种资源密集型方法,建议仅用于协助清理阶段 - 而不是我为生产最终用户生产的日常面包。

P.P.S. And, of course, once you have your MANUFACTURER table properly created, with the primarymanufacturerid references completed, you could add a [manufacturerid] column to your PRODUCTS table and update it accordingly, and then dispense with all of this roundabout stuff.

P.P.S.当然,一旦您正确创建了MANUFACTURER表,并且已完成primarymanufacturerid引用,您可以在PRODUCTS表中添加[manufacturerid]列并相应地更新它,然后省去所有这些环形交叉的东西。

#6

$sql = 'SELECT manufacturer FROM shop_articles WHERE 1=1'; for($i=0;$i

$ sql ='SELECT manufacturer FROM shop_articles WHERE 1 = 1';为($ I = 0; $我

by this you can run query for $searchParams array dynamically

通过这个,您可以动态地运行$ searchParams数组的查询

#1