raphael raphael - 1 year ago 64
SQL Question

the where condition does not work with Group By Rollup

I have a trouble when I run this SQL:

Create Table test_temp (
gsid Number,
sl Number);
Insert Into test_temp Values(53010100,15);
Insert Into test_temp Values(53010000,10);
Insert Into test_temp Values(53000000,5);

Select * From (
Select Sum(sl), gsid, substr(gsid, 0, 4) sj_gsid, substr(gsid, 0, 2) pro_gsid
From test_temp
Group By Rollup(substr(gsid, 0, 2), substr(gsid, 0, 4), gsid))
Where sj_gsid <>'5300'

on Oracle 11g R2 (Linux).

I think there will be 5 rows returned. Like this:

what I wanted

But, finally, I get 3 row returned:

wrong result

What is going on?

Answer Source

You only get three rows returned because the two rows you are missing have NULL for sj_gsid. You do understand that NULL <> '5300' is not TRUE, but UNKNOWN, and only rows where the condition is TRUE are returned, right? If you want them included, you must add or sj_gsid is null to your where condition