I'm going to use the following SQL to calculate, but the requirement is that there will be no sales on the day (0 yen). I'd like to print out the data, but what should I do? If possible, I would like to complete it on the database side.
December 1, 2014 1000010000
2014/12/2 ¥10000
December 3, 2014 * This record does not exist, but I would like to print 00 on the list.
2014/12/4 ¥10000
sql
select
DATE_FORMAT(s.start_date, '%Y-%m-%d') as date,
sum(p.price) as price
from
sales
inner join
productsp
on
s.product_id=p.id
group by
DATE_FORMAT(s.start_date, '%Y%m%d');
Sorry, I misunderstood the first answer.
I understand that you want to print out the dates that are not in sales.
I think we have no choice but to prepare a separate date table.
Head office has a similar question, and it seems that you can generate a date table below.
It is generated without loops or temporary tables.
However, it seems that it may not be possible to obtain the old date because of the relationship using the current date.
(In my environment, the results are empty in 2011)
select a.Date
from(
select date() - INTERVAL(a.a+(10*b.a)+(100*c.a))DAY as Date
from (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as a
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as b
cross join (select 0 as a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) as c
(a)
where a. Date between '2014-12-20' and '2014-12-27'
for each creation issue (e.g., performance or SQL appearance issues)
I have created a sample procedure for creating a calendar table.(mysql 5.1.59)
Enter the date of the month in a table similar to the one below, specifying the year and date.
Note that the day column is datetime, not numeric.
create table mycalendar (year integer, month integer, day datetime)
After you create the procedure shown later, you can invoke it with a specified number of years and place the data into the mycalendar table.
call sampleInsertDaysToMyCalendar (2010,2)
As mentioned in the comment, if you replace three lines, you can ignore the month specification and change it to one year's table creation.
The following code is used to create tables and procedures at once (data input is commented out):
Verifying that phpmyadmin can run it.
delimiter//
-- If there is no table, create it.
create table if not exists mycalendar (year integer, month integer, day datetime)
//
-- Delete the procedure if it exists.
drop procedure if exists sampleInsertDaysToMyCalendar
//
-- This is the creation part of the procedure.
CREATE PROCEDURE sampleInsertDaysToMyCalendar(IN_year integer, IN_month integer)
BEGIN
set @current=date_add(madeate(_year,1), interval(_month)-1month);
set @lastday = last_day(@curday);
delete from mycalendar where year=_year and month=_month;
-- If you want to ignore the month and include year data, replace the top three lines with the bottom three lines of comment out.
-- set@curday=madeate(_year,1);
-- set @lastday = last_day(date_add(@curday, interval(12)-1month));
-- delete from mycalendar where year=_year;
commit;
while@curday<=@lastday do
insert into mycalendar(year, month, day) values(_year,_month,@current);
set@curday=ADDDATE(@curday,1);
end while;
commit;
END
//
-- Run the procedure.
-- call sampleInsertDaysToMyCalendar (2010,2) //
-- The delimiter to ; .
delimiter;
© 2024 OneMinuteCode. All rights reserved.