根据先前查询的结果更新表 [英] Updating table based on the results of previous query

问题描述

如何根据上一个查询的结果更新表?

原始查询(大感谢 GMB )可以找到地址(用户表)中任何与地址(地址效果表)相匹配的项目.

The original query (big thanks to GMB) can find any items in address (users table) that have a match in address (address_effect table).

从此查询的结果中，我想在address_effect表中找到地址计数并将其添加到"users"表的新列中.例如，john doe在地址栏中与idaho和usa匹配，因此在count栏中显示的计数为"2".

From the result of this query, I want to find the count of address in the address_effect table and add it into a new column in the table "users". For example, john doe has a match with idaho and usa in the address column so it’ll show a count of ‘2’ in the count column.

好，我正在使用XAMPP(使用MariaDB)在本地系统上对此进行测试.

Fyi, I'm testing this on my local system with XAMPP (using MariaDB).

+--------+-------------+---------------+--------------------------+--------+
|    ID  |  firstname  |  lastname     |    address               |  count |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     1  |    john     |    doe        |james street, idaho, usa  |        |                    
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     2  |    cindy    |   smith       |rollingwood av,lyn, canada|        |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     3  |    rita     |   chatsworth  |arajo ct, alameda, cali   |        |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     4  |    randy    |   plies       |smith spring, lima, peru  |        |                       
|        |             |               |                          |        |
+--------------------------------------------------------------------------+
|     5  |    Matt     |   gwalio      |park lane, atlanta, usa   |        |
|        |             |               |                          |        |
+--------------------------------------------------------------------------+

address_effect表

+---------+----------------+
|address   |effect         |
+---------+----------------+
|idaho    |potato, tater   |
+--------------------------+
|canada   |cold, tundra    |
+--------------------------+
|fremont  | crowded        |
+--------------------------+
|peru     |alpaca          |
+--------------------------+
|atlanta  |peach, cnn      |
+--------------------------+
|usa      |big, hard       |
+--------+-----------------+

推荐答案

使用相关的子查询返回匹配的数量:

Use a correlated subquery which returns the number of matches:

UPDATE user u
SET u.count = (
  SELECT COUNT(*)
  FROM address_effect a
  WHERE FIND_IN_SET(a.address, REPLACE(u.address, ', ', ','))
)

请参见演示.
结果:

See the demo.
Results:

> ID | firstname | lastname   | address                    | count
> -: | :-------- | :--------- | :------------------------- | ----:
>  1 | john      | doe        | james street, idaho, usa   |     2
>  2 | cindy     | smith      | rollingwood av,lyn, canada |     1
>  3 | rita      | chatsworth | arajo ct, alameda, cali    |     0
>  4 | randy     | plies      | smith spring, lima, peru   |     1
>  5 | Matt      | gwalio     | park lane, atlanta, usa    |     2

这篇关于根据先前查询的结果更新表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！