Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.1k views
in Technique[技术] by (71.8m points)

mysql - Count of other rows in table with the same value

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.

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               |
See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

Try this:

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...