Mysql_ about using nested tables can be used without 20161205.

Source: Internet
Author: User

The calculation of the sum of the indicators, in fact, do not use nested tables, such as the calculation of the city product classification of the order amount.

If you want to calculate a non-repeating indicator such as a user down multiple orders a day with such a table to calculate how many users in a day to order this user is sure to go to heavy orders should also be treated as a user

This use of nested tables is primarily for logical understanding of the steps, if you are familiar with the database table, you can omit the step of the nested table

1. Nested tables

# #C024_02 last week category sales revenue select  CONCAT (b. Category ID, '-', A. Product category) as commodity category, SUM (if (city = "City A", sales confirmation, NULL)) as City A,sum (if (city = "City B", Sales confirmation, NULL) as City B,sum (if (city = "City C", sales confirmation, null)) as City C,sum (if (city = "City D", sales confirmation, null)) as City D,sum (if (city = "City E", sales confirmation,  NULL) as City Efrom (#上周一到本周一    SELECT City, A2. Product category, SUM (sales) as sales acknowledgement from    ' A005_account ' as A1 '    a002_ ' As A2 on A1. Product ID=A2. Product ID    WHERE receivable date >=date_add (date (Date_add (Current_date,interval-weekday) Day) , INTERVAL-1 WEEK) and receivable date <date (Date_add (Current_date,interval-weekday (current_date) day))    GROUP by city, A2. Product Categories As Aleft JOIN ' a000_cate ' as B on a. Product Classification =b. Product Classification Group by a. Product category Order BY B. Category ID

2. No nesting

# #C024_02 last week category sales revenue select  CONCAT (A3. Category ID, '-', '-', A2. Product category) as commodity category, SUM (if (city = "City A", sales, NULL)) as City A,sum (if (city = "City B", Sales, NULL) as City B,sum (if (city = "City C", sales, null)) as City C,sum (if (city = "City D", sales, null)) as City D,sum (if (city = "City E", sales, null)) as City Efrom ' A005_account ' as A1left join ' a002_ product ' as A2 on A1. Product ID=A2. Product Idleft JOIN ' A000_cate ' as A3 on A2. Product category =a3. Product Categories Wher E Receivable Date >=date_add (date (Date_add (Current_date,interval-weekday (current_date) day), INTERVAL-1 WEEK) and receivable date <date (Date_add (Current_date,interval-weekday (current_date) day)) GROUP by A2. Product Category Order by A3. Category ID

Mysql_ about using nested tables can be used without 20161205.

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.