表中其他具有相同值的行的计数 [英] Count of other rows in table with the same value

问题描述

因此，我对mySQL有点陌生，可以弄清楚如何做到这一点.我有一张桌子，上面有工作人员的柱子，工作的建筑物以及工作的办公室.

So I'm a bit new to mySQL and can figure out how to do this. I have a table with columns for worker, the building they work in, and the office they work in.

我正在尝试进行查询，该查询将返回此表，并带有额外的列，该列表示在同一房间中有多少人在工作.因此，在此示例中，Paul和Tim都在xxx1中工作，因此该列将说2，但对于John来说它将说1，而他是该房间中的唯一人.

I'm trying to make query that will return this table with an extra column saying how many people work in the same room. so in this example Paul and Tim both work in xxx1 and so the column would say 2 but for John it would say 1 and he's the only person in that room.

| worker | office building   | room number     |
------------------------------------------------
| john   | xxx               | 0               | 
| paul   | xxx               | 1               |
| tim    | xxx               | 1               |
| Dave   | yyy               | 0               |    
| Ty     | yyy               | 1               |    
| Luke   | yyy               | 1               |

推荐答案

尝试一下:

SELECT t1.*,
  (SELECT COUNT(1)
  FROM mytable t2
  WHERE t2.`room number` = t1.`room number`
  )
FROM mytable t1

我建议您不要在字段名中使用空格，而应使用下划线而不是空格.

I advice you don't use field name with white space, use the underscore instead white space.

这篇关于表中其他具有相同值的行的计数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！