如何使用mysql填充每个组的空记录

Question

我正在尝试处理sql查询以获得我想要的内容。

下面是表格的架构。

CREATE TABLE MY_LOG (
RANKING      VARCHAR(20)   
, DAYOFWEEK      VARCHAR(10) 
, MENU     VARCHAR(10)   
)

我插入了一些值，如下所示。

Ranking   DAYOFWEEK   MENU
1         MONDAY      PIZZA 
2         MONDAY      ICE CREAM
3         MONDAY      CHICKEN
4         MONDAY      RICE
5         MONDAY      BREAD
1         TUESDAY      PIZZA 
2         TUESDAY      ICE CREAM
3         TUESDAY      CHICKEN
4         TUESDAY      RICE
1         WEDNESDAY      PIZZA 
2         WEDNESDAY      ICE CREAM
3         WEDNESDAY      CHICKEN

正如你所看到的，一周中的每一天，排名都会显示在菜单中。然而，在周二和周三，他们只有四项和三项记录。因此，我想插入如下所示的空白记录。

Ranking   DAYOFWEEK   MENU
1         MONDAY      PIZZA 
2         MONDAY      ICE CREAM
3         MONDAY      CHICKEN
4         MONDAY      RICE
5         MONDAY      BREAD
1         TUESDAY      PIZZA 
2         TUESDAY      ICE CREAM
3         TUESDAY      CHICKEN
4         TUESDAY      RICE
5         -            -
1         WEDNESDAY      PIZZA 
2         WEDNESDAY      ICE CREAM
3         WEDNESDAY      CHICKEN
4         -              -
5         -              -

我曾试图解决这个问题，但失败了。如何做到这一点？

Answer 1

您可以尝试将OUTER JOIN与子查询一起使用，使CORSS JOIN得到结果RANKING&DAYOFWEEK笛卡尔乘积

查询#1

SELECT t1.RANKING,
t2.DAYOFWEEK,
t2.MENU
FROM (
SELECT DISTINCT t1.DAYOFWEEK,t2.RANKING
FROM MY_LOG t1
CROSS JOIN MY_LOG t2
) t1 LEFT JOIN  MY_LOG t2
ON t1.RANKING = t2.RANKING 
AND t1.DAYOFWEEK = t2.DAYOFWEEK
ORDER BY t1.DAYOFWEEK,t1.RANKING;

菜单披萨冰淇淋鸡肉披萨冰淇淋鸡肉大米披萨冰淇淋鸡肉

排名	DAYOFWEEK
1	周一
2	周一
3	星期一
4	周一	大米
5	周一	呼吸
1	周二
2	周二
3	星期二
4	星期二
5
1	周三
2	周三
3	周三
4
5

当您希望一周中的其他日子也包括在内时：

with recursive weekdays as (
select dayname(curdate()) dn, curdate() as d 

union all 

select dayname(d+1),d+1 
from weekdays 
where d+1<date_add(curdate(), interval 7 DAY)
),
oneTofive as (
select 1 as n union select 2 union select 3 union select 4 union select 5)
select 
COALESCE(MY_LOG.RANKING, oneTofive.n) as Ranking,
weekdays.dn as Weekday,
MY_LOG.Menu

from weekdays
cross join oneTofive 
left join MY_LOG ON MY_LOG.DAYOFWEEK = weekdays.dn AND oneTofive.n=MY_LOG.RANKING
order by 
weekdays.d, 
COALESCE(MY_LOG.RANKING, oneTofive.n);

输出：

//tr>披萨冰淇淋[/tr>鸡披萨冰淇淋[/tr>鸡肉披萨鸡

排名		工作日	菜单
1	周日
2	周日
3	周日
4	周日
5	周日
1	周一
2	周一
3	周一
4	周一	大米
5	周一	呼吸
1	星期二
2	周二
3	星期二
4	星期二	大米
5	周二
1	周三
2	周三	冰淇淋
3	周三
4	周三
5	周三
1	周四
2	星期四
	等