dongpo1203
2014-11-26 02:42 阅读 42
已采纳

查询具有相同结构的多个表

I have a few tables with the same structure built and wanted to query them at once using PHP MYSQL. The challenging part that I face was to query x tables where I do not know how many of them there. (Assuming user will select the tables on UI perspective). The reason of this is to output them into a csv format.

For example:-

Date, xTable, yTable, zTable, ....
==============================b
bla      1        2      3
bla      4        3      5
...
...

How can I modify below query to cater for above needs? (The query needs to be flexible enough to query multi table).

SELECT created_dt, xTable FROM needToQueryMultiTable WHERE created_dt BETWEEN ? AND ?

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享

1条回答 默认 最新

  • 已采纳
    douya1855 douya1855 2014-11-26 02:55

    You can create a view of all possible tables:

    create v_tables as
        select 'table1' as which, t.*
        from table1 t
        union all
        select 'table2' as which, t.*
        from table2 t
        . . .;
    

    Then, you can select from the view.

    In MySQL this is not particularly efficient, because all the tables will still need to be read. However, for smaller tables this could be a reasonable solution.

    You should ask yourself why you have identically structured tables in the databases. In general, these should be combined into a single table. For performance, you can partition the table.

    点赞 评论 复制链接分享

相关推荐