--
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 閱讀(256)
評論(0) 編輯 收藏 所屬分類:
Database