As the next table


How to display the following when view query effect


Some examples checked online, found the MYSQL does not support the grouping.

CodePudding user response:

First, you need to add a classification to the original table (TYPE), the same classification data, statistics to a total,
Then write a separate query to return all of the total,
Finally combined with the query statement UNION ALL the original table,
SELECT * FROM (
SELECT a T1. *, 1 Y FROM TABLE_NAME T1
UNION ALL
The SELECT T2. *, 2 Y FROM TABLE_NAME T2 GOURP BY T2. TYPE the
T ORDER) BY T.T YPE, T.Y Lin;
The final ORDER BY is to ensure that the same classification (TYPE) of the data together, Y is in ORDER to guarantee the basic data in the former, statistics,

CodePudding user response:

Light With the query is not meet you so perfect, if use Group By With Rollup is also short of effect you want,
If you want to write like you, need to write a function that returns a table,
Then function and writes the data exchange rate one table,

CodePudding user response:

Accumulation in vernier cycle minimum date date less than or equal to the maximum, inserted into a temporary table

CodePudding user response:

Idea is to have two query, statistics monthly, and yearly, small gauge for one of the biggest month the number of days, such as the 2019-03-31, so he must be at the end of each month; A total number to one of the biggest month, such as 2019-13-01; And then the detailed, subtotal, total union all together, in the positive sequence is arranged by date, eg:


Select * from (

Select * from t1
UNION ALL
Select CONCAT (DATE_FORMAT (date, 'yyyy - MM),' - 32) date, 'subtotal' goods, 'sale,' remain, the sum (profit) profit from t1 GROUP BY DATE_FORMAT (date, 'yyyy - MM)
UNION ALL
Select CONCAT (DATE_FORMAT (date, 'yyyy'), '13-01) the date of goods' total', 'sale,' remain, the sum (profit) profit from t1

) the order by the date

CodePudding user response:

reference 1st floor AHUA1001 response:

first of all, need to add a classification to the original table (TYPE), the same classification data, statistics to a total,
Then write a separate query to return all of the total,
Finally combined with the query statement UNION ALL the original table,
SELECT * FROM (
SELECT a T1. *, 1 Y FROM TABLE_NAME T1
UNION ALL
The SELECT T2. *, 2 Y FROM TABLE_NAME T2 GOURP BY T2. TYPE the
T ORDER) BY T.T YPE, T.Y Lin;
The final ORDER BY is to ensure that the same classification (TYPE) of the data together, Y is in ORDER to guarantee the basic data in the former, statistics,

Above, do not need to modify the original table, add the classification (TYPE) is added in the query, query can be directly, actually not complex,

CodePudding user response:

With TMP as
(select the date '2019-03-05' as p_date,
'computer' as p_good,
4 as p_salnum,
6 as p_stock,
100 as p_money
The from dual
Union all
Select the date '2019-03-09', 'mobile phone', 3, 5, 200
The from dual
Union all
Select the date '2019-04-10', 'printer', 3, 4, 360,
The from dual
Union all
Select the date '2019-04-15', 'fridge, 2, 5, 250
The from dual),
Tmp1 as
(select TMP. *,
Trunc (TMP) p_date, 'mm) p_month,
Trunc (TMP) p_date, 'yyyy) as p_year
The from TMP)
The select P_DATE, P_GOOD P_SALNUM P_STOCK, P_MONEY
The from tmp1
Union all
The select add_months (tmp1 p_month, 1) as P_DATE,
Null as P_GOOD,
Null as P_SALNUM,
Null as P_STOCK,
The sum (P_MONEY)
The from tmp1
Group by tmp1. P_month
Union all
Select add_months (tmp1 p_year, 12) as P_DATE,
Null as P_GOOD,
Null as P_SALNUM,
Null as P_STOCK,
The sum (P_MONEY)
The from tmp1
Group by tmp1. P_year
The order by p_date
This is the oracle version of an idea, for your reference, is constructs the you need to collect data,