php - mysql query to sort one table based on the number of fields in another table

Question

Now there is a novel information table A (just summary information of the novel). The chapter information of the novel will be placed in the table B with the novel id0 (for example, the novel id of table A is 1245, then its chapter data is It will be placed on the table chapter_45, and the novel information will be randomly distributed on 100 sheets by ID) , now I want to find out the novel information of table A arranged in reverse order by the number of chapters (count(*)) in table B. I wrote the sql statement:
select i.id,i.siteid,count(*) as s from ptcms_novelsearch_info as i join ptcms_novelsearch_chapter_i.id0 as c on c.novelid = i.id and c.siteid=i.siteid GROUP BY i.id order by s desc limit 20;
will report a 0 syntax error error. , because the chapter table cannot be determined (there are 100 novel chapter information tables), please ask the master to explain how to realize this requirement

Answer 1

This is the concept of horizontal table partitioning. Just go to a database of an instance to check the information of 100 tables. You must union all the tables to check because you don’t know which tables store the data you want, and MySQL itself doesn’t know. This is obviously unreasonable, so your business model should use table splitting. For example, with the help of mycat, tables A and B are divided into tables according to the novel id. You only need to configure it in mycat and directly select A join B on A.id. Just =B.id

Answer 2

Since the table name is a variable, it cannot be used directly. You should use dynamic sql to splice sql statements.