i****a
发帖数: 36252 | 1 SQL 2008
TableA
ID, CatID, TranID
1, 1, 100
2, 1, 101
3, 2, 102
4, 2, 103
TableB
CatID, Description
1, blah
2, blahblah
3, blahblahblah
TableC
TranID, Cost
100, 10
101, 15
102, 5
103, 3
select B.CatID, SUM(C.Cost)
from TableA A
inner join TableB B
on A.CatID = B.CatID
inner join TableC C
on A.TranID = C.TranID
group by B.CatID
this does not show CatID 3 because there are no transactions with it.
What if user wants to see CatID 3 in the result displayed as $0? | s**********0
发帖数: 266 | 2 select B.CatID, SUM(isnull(C.Cost,0))
from TableB B
left outer join TableA A
on A.CatID = B.CatID
left outer join TableC C
on A.TranID = C.TranID
group by B.CatID | i****a
发帖数: 36252 | 3 works for my example but not in the real code....need to see what's
eliminating the records...
thanks
【在 s**********0 的大作中提到】
: select B.CatID, SUM(isnull(C.Cost,0))
: from TableB B
: left outer join TableA A
: on A.CatID = B.CatID
: left outer join TableC C
: on A.TranID = C.TranID
: group by B.CatID
| i****a
发帖数: 36252 | 4 hum... doesn't work after adding where clause
SELECT B.CatID
, SUM(ISNULL(C.Cost, 0))
FROM TableB B
LEFT OUTER JOIN TableA A
ON A.CatID = B.CatID
LEFT OUTER JOIN TableC C
ON A.TranID = C.TranID
WHERE A.ID < 100
GROUP BY B.CatID
【在 s**********0 的大作中提到】
: select B.CatID, SUM(isnull(C.Cost,0))
: from TableB B
: left outer join TableA A
: on A.CatID = B.CatID
: left outer join TableC C
: on A.TranID = C.TranID
: group by B.CatID
| i****a
发帖数: 36252 | 5 works if I put the condition into the join
SELECT B.CatID
, SUM(ISNULL(C.Cost, 0))
FROM TableB B
LEFT OUTER JOIN TableA A
ON A.CatID = B.CatID
AND A.ID < 100
LEFT OUTER JOIN TableC C
ON A.TranID = C.TranID
GROUP BY B.CatID
haa, interesting... | i****a
发帖数: 36252 | 6 doesn't work on real code... ah.. almost 6pm... I give up | a9
发帖数: 21638 | 7 这样试试看？
SELECT B.CatID
, SUM(ISNULL(C.Cost, 0))
FROM TableB B
LEFT OUTER JOIN (select * from tablea where a.id<100) A
ON A.CatID = B.CatID
LEFT OUTER JOIN TableC C
ON A.TranID = C.TranID
GROUP BY B.CatID
【在 i****a 的大作中提到】
: hum... doesn't work after adding where clause
: SELECT B.CatID
: , SUM(ISNULL(C.Cost, 0))
: FROM TableB B
: LEFT OUTER JOIN TableA A
: ON A.CatID = B.CatID
: LEFT OUTER JOIN TableC C
: ON A.TranID = C.TranID
: WHERE A.ID < 100
: GROUP BY B.CatID
| i*****w
发帖数: 75 | 8 Try this:
SELECT B.CatID
, SUM(ISNULL(C.Cost, 0))
FROM @TableB B
LEFT OUTER JOIN @TableA A
ON A.CatID = B.CatID
LEFT OUTER JOIN @TableC C
ON A.TranID = C.TranID
WHERE ISNULL(A.ID,0) < 100
--WHERE A.ID < 100
GROUP BY B.CatID | i****a
发帖数: 36252 | 9 thanks for the suggestions. those all worked with the example I provided.
But on my real code with multiple group by columns and where conditions, I
just can't make it to work.
I think I find the issue is, if the query does not return, say CatID 3 at
all, then the left outer join and group by would return CatID 3 as 0
But if one of the tranID has CatID 3, then none of the other tranID will
output CatID 3 as 0
I guess I'll have to handle the missing rows when i do pivot. | g***l
发帖数: 18555 | 10 从A到C就是个INNER JOIN，因为A没有3，所以FILTER掉了，就是写LEFT JOIN也没用，你是
B LEFT JOIN了A,再用A LEFT JOIN C，因为A没有3，所以掉了。 | | | N*******m
发帖数: 517 | 11 我的印象里group by和where不能同时用。
改成
GROUP BY B.CatID
HAVING A.ID < 100 | O**K
发帖数: 11 | 12 SELECT B.CatID
, SUM(ISNULL(C.Cost, 0))
FROM TableB B
LEFT OUTER JOIN TableA A
ON A.CatID = B.CatID
AND A.ID < 100
LEFT OUTER JOIN TableC C
ON ISNULL(A.TranID, 0) = C.TranID /* assume TranID > 0*/
GROUP BY B.CatID | i****w
发帖数: 329 | 13 variable ID exists only in table A, 加了where statement
left join 成了inner join,
试试
LEFT OUTER JOIN TableA (where=(id<100)) A
不保证能work啊. | N*******m
发帖数: 517 | 14 Sorry, 我瞎说了。
既然A和C的TransID是一对一的话，先把A跟C搞成一个大表S，然后B再left join这个大
表S不行吗？
SELECT DISTINCT B.CatID, SUM(ISNULL(S.Cost, 0))
FROM TableB as B
LEFT JOIN
(select A.ID, A.CatID, A.TranID, C.Cost
from TableA as A
inner join TableC as C
om A.TranID = C.TranID
) as S
ON B.CatID = S.CatID
【在 N*******m 的大作中提到】
: 我的印象里group by和where不能同时用。
: 改成
: GROUP BY B.CatID
: HAVING A.ID < 100
| i****w
发帖数: 329 | 15 isnull function 我这里用不了, 所以做了点改动.
proc sql;
SELECT B.CatID
, case when SUM(c.cost) ne . then sum(c.cost) else 0 end as cost
FROM TableB B
LEFT OUTER JOIN TableA(where=(id<100)) A
ON A.CatID = B.CatID
LEFT OUTER JOIN TableC C
ON A.TranID = C.TranID
GROUP BY B.CatID;
quit;
CatID cost
1 25
2 8
3 0
LEFT OUTER JOIN TableA(where=(id<100)) A 是可行的. |
|
