左侧和右侧加入查询
问题描述:
一位朋友询问我如何构建一个查询,以显示每个月份每天销售的每个型号有多少个零件,如果没有销售某个特定型号的零件,则显示为零特定的一天,即使当天没有销售任何型号的产品。我想出了下面的查询,但它没有按预期工作。我只获得已售出机型的记录,但我不知道为什么。左侧和右侧加入查询
select days_of_months.`Date`,
m.NAME as "Model",
count(t.ID) as "Count"
from MODEL m
left join APPLIANCE_UNIT a on (m.ID = a.MODEL_FK and a.NUMBER_OF_UNITS > 0)
left join NEW_TICKET t on (a.NEW_TICKET_FK = t.ID and t.TYPE = 'SALES'
and t.SALES_ORDER_FK is not null)
right join (select date(concat(2009,'-',temp_months.id,'-',temp_days.id)) as "Date"
from temp_months
inner join temp_days on temp_days.id <= temp_months.last_day
where temp_months.id = 3 -- March
) days_of_months on date(t.CREATION_DATE_TIME) =
date(days_of_months.`Date`)
group by days_of_months.`Date`,
m.ID, m.NAME
我创建临时表temp_months
和temp_days
为了得到所有天的任何一个月。我正在使用MySQL 5.1,但我试图使查询符合ANSI。
答
你应该CROSS JOIN
您的日期和模式,让你有每天模型对一个记录无论是什么,然后LEFT JOIN
其他表:
SELECT date, name, COUNT(t.id)
FROM (
SELECT ...
) AS days_of_months
CROSS JOIN
model m
LEFT JOIN
APPLIANCE_UNIT a
ON a.MODEL_FK = m.id
AND a.NUMBER_OF_UNITS > 0
LEFT JOIN
NEW_TICKET t
ON t.id = a.NEW_TICKET_FK
AND t.TYPE = 'SALES'
AND t.SALES_ORDER_FK IS NOT NULL
AND t.CREATION_DATE_TIME >= days_of_months.`Date`
AND t.CREATION_DATE_TIME < days_of_months.`Date` + INTERVAL 1 DAY
GROUP BY
date, name
你做的方式,现在你得到NULL
在model_id
的销售日期内,他们被分组在一起。的
AND t.CREATION_DATE_TIME >= days_of_months.`Date`
AND t.CREATION_DATE_TIME < days_of_months.`Date` + INTERVAL 1 DAY
代替
DATE(t.CREATION_DATE_TIME) = DATE(days_of_months.`Date`)
这将有助于使你的查询sargable
(由索引优化)
答
你正在寻找一个OUTER加入。即使右侧没有要加入的记录,左外连接也会从连接的左侧创建带有记录的结果集。即使左侧没有相应的记录,右外连接也会在相反的方向上执行相同的操作,为右侧的表创建记录。从表中计算出的没有记录的列在连接结果中将具有NULL值。
你不需要做指定子宫连接。 我不太熟悉mysql,但在sql server中,如果你忽略内部/外部,它默认为内部。 – 2009-07-01 13:47:51
@丹s:左和右意味着外。 – Quassnoi 2009-07-01 13:48:59