开发者

Join on each possible combination for each group

I have 2 tables

T1
order1      grp
----------- -----------
1           1
2           1
3       开发者_JAVA百科    1
4           1
1           2
2           2
3           2

T2
order1      count1      abbrev grp
----------- ----------- ------ -----------
2           5           A      1
1           17          B      2
2           11          B      2

The tables are joined this way:

SELECT 
  t2.grp, t2.order1, t2.count1, t2.abbrev 
FROM t2 
JOIN t1
ON
  t1.grp = t2.grp and
  t1.order1 = t2.order1

Result:

grp         order1      count1      abbrev
----------- ----------- ----------- ------
1           2           5           A
2           1           17          B
2           2           11          B

Problem is that i need a row for each possible combination for each group:

grp         order1      count1      abbrev
----------- ----------- ----------- ------
1           1           0           A
1           2           5           A
1           3           0           A
1           4           0           A
2           1           17          B
2           2           11          B
2           3           0           B


SELECT
  cube.grp,
  cube.order,
  ISNULL(data.count, 0),
  chars.abbrev
FROM
  @t1   AS cube
INNER JOIN
  (SELECT grp, abbrev FROM @t2 GROUP BY grp, abbrev) AS chars
    ON chars.grp = cube.grp
LEFT JOIN
  @t2   AS data
    ON  data.grp    = cube.grp
    AND data.order  = cube.order
    AND data.abbrev = chars.abbrev
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜