如何将一个字段的值更新为另一个字段最常用的值? [英] How can I update the value of one field to the most often used value of another field?

问题描述

我有一个类似于以下表格:

I have a table similar to the following:

ID    PAYEE      类别 001  Costco     杂货店
002  See's                             003  Costco     维修
005  Costco     杂货
006  Costco
007&Costco
008  See的

ID     PAYEE     CATEGORY
001  Costco      Grocery
002  See's        Candy
003  Costco      Repair
005  Costco      Grocery
006  Costco
007  Costco
008  See's

在不使用编程语言的情况下使用MySQL，是否有一个查询(是否嵌套)(将三个新行的类别设置为这些收款人最常用的类别)?

Using MySQL withOUT the aid of a programming language, is there a query (nested or not) that would set the category of the three new rows to the most often used category for those payees?

例如，一个Costco记录(ID 003)的类别为Repair，而其他两个Costco行(ID 001和ID 005)的类别为Grocery.因此，理想的结果是将新的Costco行(ID 006和ID 007)设置为Grocery，因为这是该收款人最常用的类别.

For example, one of the Costco records (ID 003) has Repair as its category, whereas the other two Costco rows (ID 001 and ID 005) have Grocery as their category. Thus the desired result would be that the new Costco rows (ID 006 and ID 007) would be set to Grocery since that's the most often used category for that payee.

推荐答案

确定..只需将'your_table'更改为表名

sure.. just change 'your_table' to the name of your table

UPDATE your_table
LEFT JOIN (SELECT payee, category 
             FROM
             (SELECT payee, category FROM your_table WHERE category != '' AND category IS NOT NULL GROUP BY payee, category ORDER BY count(*) DESC) AS tbl2
             GROUP BY payee
           ) AS tbl2 USING (payee)
SET your_table.category = tbl2.category;

这也会将分类为维修的Costco也更改为杂货店".如果您不希望这样做，请添加:

this will change the costco that is categorized as repair to 'grocery' as well.. if you dont want this, add:

  WHERE your_table.category IS NULL OR your_table.category = ''    

查询结束

这篇关于如何将一个字段的值更新为另一个字段最常用的值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！