我该如何编写这个MySQL查询？

The user enters from 1 to 6 words (characteristics) so the all input data is 6 words. Here is my table:

用户输入1到6个字(特征),因此所有输入数据都是6个字。这是我的表:

----------------
| USER |  WORD |
----------------
   1   |  Green
   1   |  Black
   1   |  Red
   2   |  Green
   2   |  Gray

For example, array consists of 2 words: "Green", "Black". How can I receive "User=1" if the amount of conjunctions in it's characteristics is maximal. I've already tried this query:

例如,数组由2个单词组成:“绿色”,“黑色”。如果其特征中的连词数量最大,我该如何收到“User = 1”。我已经尝试过这个查询:

// $pat - array of words
// $nums - minimal amount of conjunctions.
$nums = 0.8*count($pat)

SELECT user
FROM words
WHERE word IN ('{$pat[0]}', '{$pat[1]}','{$pat[2]}','{$pat[3]}','{$pat[4]}','{$pat[5]}')
GROUP BY `user`
HAVING count(*) >= ".$nums."
ORDER BY count(*) DESC
LIMIT 1

The problem is that condition HAVING count(*) >= ".$nums." doesn't work properly and the data is extracted even if there is only one conjunction.

问题是条件HAVING count(*)> =“。$ nums。”无法正常工作,即使只有一个连接,也会提取数据。

1 个解决方案

#1

I believe your query is working just fine

我相信你的查询工作正常

In case when we have two words 'Green' and 'Black'

如果我们有两个单词'Green'和'Black'

SELECT user
  FROM words
 WHERE word IN ('Green', 'Black')
 GROUP BY user
HAVING COUNT(*) >= (0.8 * 2)
 ORDER BY COUNT(*) DESC
 LIMIT 1;

As expected only user 1 have have both words (which is more then 0.8 * 2):

正如所料,只有用户1有两个单词(大于0.8 * 2):

| USER |
--------
|    1 |

Now if we have 5 words

现在,如果我们有5个字

SELECT user
  FROM words
 WHERE word IN ('Green', 'Black', 'Red', 'Green', 'Gray')
 GROUP BY user
HAVING COUNT(*) >= (0.8 * 5)
 ORDER BY COUNT(*) DESC
 LIMIT 1;

The result set is empty (again as expected) since there is no user who has at least four (0.8 * 5) words.

结果集为空(再次按预期),因为没有用户具有至少四个(0.8 * 5)字。

Here is SQLFiddle demo

这是SQLFiddle演示

Therefore it seems that you're not telling us something or you real data is probably different

因此,您似乎没有告诉我们某些事情,或者您的实际数据可能不同

---

One of the probable causes might be existence of duplicate rows with the same word for the same user. If it's the case you can either enforce a restriction with UNIQUE constraint on your table or using COUNT(DISTINCT word)

SELECT user
  FROM words
 WHERE word IN ('Green', 'Black')
 GROUP BY user
HAVING COUNT(DISTINCT word) >= (0.8 * 2)
 ORDER BY COUNT(DISTINCT word) DESC

Here is SQLFiddle demo

这是SQLFiddle演示