SQL SELECT with a default value
Assuming I have an SQL table with this schema:
CREATE TABLE(
foo INTEGER,
bar INTEGER,
baz INTEGER DEFAULT 0
);
which contains data
foo|bar|baz
-----开发者_开发知识库------
1 |1 |2
2 |3 |4
and I am interested in values 1,2 for foo
and 1,2,3 for bar
. Is there an SQL request which would return "missing" values along with the existing ones:
foo|bar|baz
-----------
1 |1 |2
1 |2 |0
1 |3 |0
2 |1 |0
2 |2 |0
2 |3 |4
? I suspect there isn't, but perhaps I just don't know it?
UPDATE:
1) using SQLite 3;
2) the missing values are given by the DEFAULT
clause;
3) the second table shows the expected result.
Assuming you named your table Data
, following would get you the results you've posted.
- the
WITH
statement creates a temporary in memory table containing all Bar's from 1 to the Maximum present in your actual table. - the
CROSS APPLY
returns a row for every bar, existing in your table or not. - the
CASE
statement selects an existing baz if present, 0 if not.
SQL Statement
WITH q AS (
SELECT [bar] = 1
, [MaxBar] = MAX(bar)
FROM Data
UNION ALL
SELECT q.bar + 1
, q.MaxBar
FROM q
WHERE q.bar + 1 <= q.MaxBar
)
SELECT Data.foo
, q.bar
, CASE WHEN q.bar = Data.bar THEN Data.baz ELSE 0 END
FROM q
CROSS APPLY Data
ORDER BY
Data.foo
, q.bar
Test script
WITH Data AS (
SELECT [foo] = 1, [bar] = 1, [baz] = 2
UNION ALL SELECT 2, 3, 4
), q AS (
SELECT [bar] = MIN(bar)
, [MaxBar] = MAX(bar)
FROM Data
UNION ALL
SELECT q.bar + 1
, q.MaxBar
FROM q
WHERE q.bar + 1 <= q.MaxBar
)
SELECT Data.foo
, q.bar
, CASE WHEN q.bar = Data.bar THEN Data.baz ELSE 0 END
FROM q
CROSS APPLY Data
ORDER BY
Data.foo
, q.bar
create following tables
table_foo
foo|
-----------
1 |
2 |
table_bar
bar|
-----------
1 |
2 |
3 |
After that, this query should do the trick:
select * from table
UNION
(
select
table_foo.foo,
table_bar.bar,
0
from
table,
table_foo,
table_bar
where
table.foo != table_foo.foo and
table.bar != table_bar.bar
)
Assuming that your table is called MyTab, this sql will produce the second table of data (I'm assuming MSSQL):
select
f.foo,
b.bar,
baz = coalesce(m.baz,0)
from
(select foo = 1 union select foo = 2) f
cross join (select bar = 1 union select bar = 2 union select bar = 3) b
left join mytab m on f.foo = m.foo and b.bar = m.bar
declare @foovalues table (foo int) -- values 1,2 for foo
insert into @foovalues values (1)
insert into @foovalues values (2)
declare @barvalues table (bar int) -- values 1,2,3 for bar
insert into @barvalues values (1)
insert into @barvalues values (2)
insert into @barvalues values (3)
declare @table table (foo int, bar int, baz int) -- your data
insert into @table values (1,1,2)
insert into @table values (2,3,4)
select x.foo, x.bar, isnull(baz, 0) as baz
from (select f.foo, b.bar from @foovalues f, @barvalues b) as x
left outer join @table t on t.foo = x.foo and t.bar = x.bar
order by x.foo, x.bar
精彩评论