--
Test Case
create
table
sale(
sale_id
char
(
1
)
,sale_type
char
(
1
)
)
--
insert
into
sale
values
(
'
a
'
,
'
Y
'
);
insert
into
sale
values
(
'
b
'
,
'
N
'
);
insert
into
sale
values
(
'
b
'
,
'
Y
'
);
insert
into
sale
values
(
'
b
'
,
'
Y
'
);
insert
into
sale
values
(
'
c
'
,
'
Y
'
);
insert
into
sale
values
(
'
c
'
,
'
N
'
);
insert
into
sale
values
(
'
d
'
,
'
N
'
);
insert
into
sale
values
(
'
d
'
,
'
N
'
);
Count the number of 'Y' and the number of 'N' separately
--
full join
select
*
from
(
select
sale_id,
count
(
*
)
as
num_y
from
sale
where
sale_type
=
'
Y
'
group
by
sale_id
) sale_y
full
join
(
select
sale_id,
count
(
*
)
as
num_n
from
sale
where
sale_type
=
'
N
'
group
by
sale_id
) sale_n
using (sale_id)
order
by
sale_id
--
decode
select
sale_id
,
sum
(decode(sale_type,
'
Y
'
,
1
,
0
))
as
num_y
--
note using sum to implement count
,
sum
(decode(sale_type,
'
N
'
,
1
,
0
))
as
num_n
from
sale
group
by
sale_id
order
by
sale_id
--
case
select
sale_id
,
sum
(
case
when
sale_type
=
'
Y
'
then
1
else
0
end
)
as
num_y
--
note using sum to implement count
,
sum
(
case
when
sale_type
=
'
N
'
then
1
else
0
end
)
as
num_n
from
sale
group
by
sale_id
order
by
sale_id
Separate sale_type column
--
union all
select
sale_id, sale_type
as
type_y,
null
from
sale
where
sale_type
=
'
Y
'
union
all
select
sale_id,
null
, sale_type
as
type_n
from
sale
where
sale_type
=
'
N
'
order
by
sale_id
--
decode
select
sale_id
,decode(sale_type,
'
Y
'
,
'
Y
'
,
null
) as type_y
,decode(sale_type,
'
N
'
,
'
N
'
,
null
) as type_n
from
sale
--
case
select
sale_id
,(
case
when
sale_type
=
'
Y
'
then
'
Y
'
else
null
end
)
as
type_y
,(
case
when
sale_type
=
'
N
'
then
'
N
'
else
null
end
)
as
type_n
from
sale
posted on 2006-12-05 13:11
Jcat 阅读(250)
评论(0) 编辑 收藏 所属分类:
Database