SQL statements for report statistics and SQL statement reports

SQL statements for report statistics and SQL statement reports
-- Some Basic SQL functions used
-- Returns the middle-aged value of the date: SQL YEAR function.
-- Return the value of the Month in the date: SQL Month function.
-- Return the value of the date: SQL Day function. -- DATE_ADD (): The function adds the specified time interval to the date.
-- LAST_DAY (): returns the last day of the current month of a time value.
-- MAKEDATE (): returns the year value and the day value in the year, and returns a date.
-- CONCAT: splicing date
-- ELT matching expression
-- SQL EXTRACT () function is used to EXTRACT values (year, month, day, etc.) from a date expression)


-- NOW (): The current time, which can be replaced with any time in DATE format.
-- Get the current time yyyy-mm -- dd hh: mm: ss
Select now () AS times
-- Obtain the current date yyyy-mm-dd
Select date (NOW () AS times

-- Query year, quarter, month


-- Query the last day of a month
SELECT LAST_DAY (NOW () AS times;

-- Query the first day of March January
SELECT DATE_ADD (LAST_DAY (NOW (), INTERVAL 1 DAY) AS times

-- Query the first day of the current month
SELECT DATE_ADD (LAST_DAY (NOW (), INTERVAL 1 DAY), INTERVAL-1 MONTH) AS times


-- Query quarter


-- Query the current quarter
Select quarter (NOW () AS times

-- Query the first month of the quarter
Select elt (QUARTER (NOW (), 1, 4, 7, 10) AS times

-- Obtain the first day of the current quarter
Select date (CONCAT (YEAR (NOW (), '-', ELT (QUARTER (NOW (),), '-1') AS times


-- Obtain the first day of the year
Select makedate (EXTRACT (year from now (), 1) AS times

-- Get the last day of the quarter
SELECT LAST_DAY (MAKEDATE (EXTRACT (year from now (), 1) + interval quarter (NOW () * 3-1 MONTH) AS times


-- Obtain the first day of the first month of the current quarter
SELECT DATE_ADD (LAST_DAY (MAKEDATE (EXTRACT (year from now (), 1) + interval quarter (NOW () * 3-3 MONTH), INTERVAL 1 DAY ), INTERVAL-1 MONTH) AS times

-- Obtain the last day of the first month of the current quarter
SELECT LAST_DAY (MAKEDATE (EXTRACT (year from now (), 1) + interval quarter (NOW () * 3-3 MONTH) AS times
-- Of course, you can change the following multiplication parameter for different quarters.


-- A statistical statement used
-- Achieve the sales volume of a single product in the first month of the corresponding date quarter (sales order statistics) dataEx: A Time Variable
SELECT B. productname productnames, SUM (B. ordercount) ordercounts, SUM (B. subtotal) subtotals FROM orders a, child_orders B WHERE. id = B. orders_id AND B. productname = 'productnames'
AND. create_date> = DATE_ADD (LAST_DAY (MAKEDATE (EXTRACT (year from '"+ dataEx +"'), 1) + interval quarter ('"+ dataEx + "') * 3-3 MONTH), INTERVAL 1 DAY), INTERVAL-1 MONTH) AND. create_date <= LAST_DAY (MAKEDATE (EXTRACT (year from '"+ dataEx +"'), 1) + interval quarter ('"+ dataEx +"') * 3-3 MONTH)
Group by productnames order by a. create_date DESC

Note:
Of course, this is just a query statement, and there are other statements to implement the same function. Here I only use statements that I think can be used. Of course, there is still a lot of work to do to achieve statistics.
Thanks