将一行加入所有行并返回所有行 [英] join one row to all row and returning all row
问题描述
我可以从我的表
| id_outlet| date | count(msisdn) |
| 34.10.1 | 2014-08 | 0 |
| 34.10.1 | 2014-09 | 3 |
| 34.10.1 | 2014-10 | 2 |
| 34.10.2 | 2014-08 | 1 |
| 34.10.2 | 2014-09 | 0 |
| 34.10.2 | 2014-10 | 0 |
所以我有2张桌子
1.桌子出风口(独特)
2.表销售(表出口的详细信息)
如您在我的第二张表中所见,有3个周期(2014-08、2014-09、2014-10)
我想像这样的示例在第一个表中将其与id_outlet连接起来.
我可以吗?
请帮助我
So I have 2 tables
1. table outlet (unique)
2. table sales (detail of table outlet)
As u see in my second table there are 3 periode (2014-08, 2014-09, 2014-10)
I want join that periode with id_outlet in first table like that example.
Can I?
Please Help me
推荐答案
使用交叉联接:-
SELECT
o.id_outlet,
s_main.periode,
o.branch,
count(msisdn)
FROM
(
SELECT DISTINCT SUBSTRING(date,1,7) AS periode
FROM sales
) s_main
CROSS JOIN outlet o
LEFT OUTER JOIN sales s
ON s_main.periode = SUBSTRING(s.date,1,7)
AND o.id_outlet = s.id_outlet
WHERE (o.STATUS LIKE 'STREET%')
GROUP BY s_main.periode, o.branch, o.id_outlet
如果您有日期表,则可以使用该表而不是子查询来获取日期(这还避免了潜在的问题,即当月的销售量为零时,结果中没有日期任何出口).
If you have a table of dates then you can just use that rather than the sub query to get the dates (which also avoids the potential problem of not having a date in the results for a month where there has been zero sales for any outlet).
这篇关于将一行加入所有行并返回所有行的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!