i need some ideas to create a MySQL View.
The base query collect all table names with prefix _dynamicgroup.
SELECT table_name AS tname FROM information_schema.tables where TABLE_NAME LIKE "%_dynamicgroup%"');
As a next step I would like to have the number of individual fields of each table. In each table there is a column "ID".
SELECT count(id) FROM 10_dynamicgroup
The view should then look like this:
| IDcount | tname |
|---|---|
| 23 | 10_dynamicgroup |
| 17 | 33_dynamicgroup |
| 27 | 3_dynamicgroup |
| 1 | 56_dynamicgroup |
| 110 | 18_dynamicgroup |
Thank you for your ideas
CodePudding user response：
you can use.
SELECT ... FROM ... GROUP BY ...
</code
CodePudding user response：
You should be querying the information_schema.COLUMNS table:
SELECT COUNT(*) AS IDcount, TABLE_NAME AS name
FROM information_schema.COLUMNS
WHERE TABLE_NAME LIKE '%_dynamicgroup%'
GROUP BY TABLE_NAME;