Some questions about SQL queries

Source: Internet
Author: User

When you write query queries, you often think of this problem:

1,foreach Circular Query

$data = SELECT...;foreach($data as &$item)    $item['sub] = SELECT .... WHERE $item['xx'] ....;

2, multi-table Association join

先多表联合查询,然后重新组装数据

3, nested, sub-query

So here's the problem.

考虑效率的时候,一般会如何选择,或者还有其他高效的查询方法;

Update
Maybe there was a problem with the presentation, and now I'm going back to a sample supplement:

Virtual a scene:
such as a list of articles, and this list has categories, title, Introduction, author, release time and so on. These data are in article, User,category, and many other tables.
At this point you need the structure of the tree, shown in the view.

接上面问题:为了得到这个数据,最合适的方法是什么呢 ?ps:从效率来看1,多层嵌套 foreach 查询;2,先多表联合查询出结果,然后重新组装需要的数据结构...或者其他

First of all, thank you for your reply, and welcome everyone to talk about their views. Thanks

Reply content:

When you write query queries, you often think of this problem:

1,foreach Circular Query

$data = SELECT...;foreach($data as &$item)    $item['sub] = SELECT .... WHERE $item['xx'] ....;

2, multi-table Association join

先多表联合查询,然后重新组装数据

3, nested, sub-query

So here's the problem.

考虑效率的时候,一般会如何选择,或者还有其他高效的查询方法;

Update
Maybe there was a problem with the presentation, and now I'm going back to a sample supplement:

Virtual a scene:
such as a list of articles, and this list has categories, title, Introduction, author, release time and so on. These data are in article, User,category, and many other tables.
At this point you need the structure of the tree, shown in the view.

接上面问题:为了得到这个数据,最合适的方法是什么呢 ?ps:从效率来看1,多层嵌套 foreach 查询;2,先多表联合查询出结果,然后重新组装需要的数据结构...或者其他

First of all, thank you for your reply, and welcome everyone to talk about their views. Thanks

The general principle is to minimize the operation of SQL in the loop, for your first given DEMO can be used to SELECT WHERE IN integrate into a single query.

Like this kind of query data is not too large, you can merge the data into a table to query.
Generally speaking:

$data = SELECT...;foreach($data as &$item)    $item['sub] = SELECT .... WHERE $item['xx'] ....;

Since you do this, this for believe is not too much, too time-consuming, if in addition to build the relevant index, there is no problem in efficiency, visual inspection within 100ms.

php
  
    $uid2User[$val['id']]);}// 这里 $resut 是我们需要的// 这种方法写了长长的一串, // 当数据量特别大的时候, 是不允许进行连表的,// 同时这么写也方便进行缓存

Save all the where conditions that your main table produces, generate a SQL, go to the schedule, and then set up two foreach to regroup the array.

  • Related Article

    Contact Us

    The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

    If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

    A Free Trial That Lets You Build Big!

    Start building with 50+ products and up to 12 months usage for Elastic Compute Service

    • Sales Support

      1 on 1 presale consultation

    • After-Sales Support

      24/7 Technical Support 6 Free Tickets per Quarter Faster Response

    • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.