基于2个表中的列的比较更新表列（SQL）

I have the following tables.

我有以下表格。

Table 1

Id | Values | Counts 
1  | rock   |  0
2  | tina   |  0
3  | alex   |  0

Table 2

Id | Values 
1  | rock
2  | alex
3  | alex
4  | rock
5  | rock
6  | tina

As you can see, table 1 contains Values as rock, tina and alex. These column will always have unique values. Counts column should check the count of 'rock' in Table 2 and update it in Counts column. for e.g. rock is shown 3 times in table 2. The counts for rock should be then 3.

如您所见,表1包含值为rock,tina和alex。这些列始终具有唯一值。计数列应检查表2中的“摇滚”计数,并在计数列中更新。例如表2中显示了3次岩石。岩石的数量应为3。

Similarly for other values. Can someone pls let me know how can i achieve this using SQL. Here is how the final table should look like.

与其他值类似。有人请允许我知道如何使用SQL实现这一目标。以下是决赛桌的样子。

Table 1

Id | Values | Counts 
1  | rock   |  3
2  | tina   |  1
3  | alex   |  2

Any help is appreciated. I searched online and couldnot find a possible solution for this scenario.

任何帮助表示赞赏。我在网上搜索,无法找到这种情况的可能解决方案。

3 个解决方案

#1

You can generally use a JOIN between 2 tables to update Table1 with values from Table2 (or further if you are using bridge tables).

通常可以在2个表之间使用JOIN来使用Table2中的值更新Table1(如果使用桥表,则更新)。

UPDATE t1
SET    t1.dataColumn = t2.dataColumn
FROM   Table1     t1
INNER JOIN Table2 t2 ON t1.keyColumn = t2.keyColumn

However, when you are using Aggregate functions (such as Count, Sum)you must utilize a subquery for the second table and perform the JOIN to that subquery

但是,当您使用聚合函数(如Count,Sum)时,必须使用子查询来表示第二个表并对该子查询执行JOIN

UPDATE t1
SET    t1.Counts = sb.Counts
FROM   Table1 AS t1
INNER JOIN (
    SELECT    [values], Counts = Count([values])
    FROM      Table2 
    GROUP BY  [values]
    ) AS sb
ON t1.[values] = sb.[values]

Running this on your tables gave me this:

在你的桌子上运行这个给了我:

SELECT * FROM Table1

id   values   counts
----  -------  -------
1     rock       3
2     tina       1
3     alex       2

One thing concerning your table design; I generally recommend not using reserved/special/key words when naming tables, columns, or other database objects. I also try to avoid using the generic name id because it can get confusing when you start linking tables to one another, even idTable1 can make things a lot easier

关于你的桌子设计的一件事;我通常建议在命名表,列或其他数据库对象时不要使用保留/特殊/关键字。我也试图避免使用通用名称id,因为当你开始将表链接到另一个时它会变得混乱,即使idTable1可以使事情变得容易很多

#2

In SQL Server, using a correlated subquery:

在SQL Server中,使用相关子查询:

update t1
set t1.Counts = (
    select count(*) 
    from t2 
    where t2.[Values] = t1.[Values]
    );

rextester demo: http://rextester.com/SBYNB72372

rextester演示:http://rextester.com/SBYNB72372

In MySQL, using a correlated subquery:

在MySQL中,使用相关子查询:

update t1
set t1.Counts = (
    select count(*) 
    from t2 
    where t2.`Values` = t1.`Values`
    );

rextester demo: http://rextester.com/DDDC21719

rextester演示:http://rextester.com/DDDC21719

Although this sort of thing might be better calculated in a view instead of stored in the t1 table.

虽然这种事情可能在视图中更好地计算而不是存储在t1表中。

In SQL Server:

在SQL Server中:

create view dbo.t1_with_counts as 
select t1.Id, t1.[Values], count(t2.[Values]) as Counts
from t1
  left join t2 
    on t1.[Values] = t2.[Values]
group by t1.Id, t1.[Values]
go
select *
from dbo.t1_with_counts;

In MySQL:

create view t1_with_counts as 
select t1.Id, t1.`Values`, count(t2.`Values`) as Counts
from t1
  left join t2 
    on t1.`Values` = t2.`Values`
group by t1.Id, t1.`Values`;

select *
from t1_with_counts;

#3

I would question the wisdom of keeping track of a count in a table like that. That leads to poor relational database structure and management. Instead, I suggest you remove the count column from Table 1. Then, whenever you need to see the counts you use a view:

我会质疑在这样的表格中跟踪计数的智慧。这导致关系数据库结构和管理不良。相反,我建议您从表1中删除计数列。然后,每当您需要查看计数时,您使用视图:

SELECT t1.ID, t1.VALUES, COUNT(t2.ID) AS VALUE_COUNT
FROM TABLE1 t1 LEFT JOIN TABLE2 t2 ON t1.VALUES = t2.VALUES

This results in a dynamically updated view of your data instead of a static view that has the potential for going stale without your realizing it.

这会导致动态更新您的数据视图,而不是静态视图,如果您没有意识到这一点,它可能会过时。

#1