users
id|name
1 | tanaka
2 | suzuki
3 | sato
articles
id | writer_1 | writer_2 | writer_3
1 | tanaka | tanaka | tanaka
2 | suzuki | sato | tanaka
3 | suzuki | suzuki | sato
I use MySQL.
There is a table similar to the one above and
when retrieving users' data
"How many times does my name appear in the writer_x column of the articles table?"
Is it possible to aggregate in SQL?
I would appreciate it if you could give me an example of SQL.
The response I would like is as follows:
{
name —tanaka,
hitCount—4
},
{
name: suzuki,
hitCount—3
},
{
name —sato,
hitCount—2
}
Thank you for your cooperation.
php mysql sql
SELECT writer, count (writer) FROM(
SELECT writer_1 AS writer FROM articles
UNION ALL
SELECT writer_2 AS writer FROM articles
UNION ALL
SELECT writer_3 AS writer FROM articles
a GROUP BY writer;
+--------+---------------+
| writer | count (writer) |
+--------+---------------+
| sato | 1 |
| tanaka | 1 |
| Yamada | 1 |
+--------+---------------+
(The original data has not been reproduced.)
This is a typical anti-pattern of table design because multiple columns of the same kind (with = columns) in a single row cannot be easily searched or aggregated.If possible, we will proceed with reviewing the table design.
© 2024 OneMinuteCode. All rights reserved.