下面两条语句,要并列.条件比较多
select contno=contno ,
qua=case vo_c_d when 'd'then amount else -amount end ,
cost=case vo_c_d when 'd'then vo_sum else -vo_sum end
Where item_cd like 'AAA%' and jsign<>'AAA1' And ltrim(contno)<>'AAA2' and dcode like 'AAA3%'select fee=case vo_c_d when 'd' then fee else -fee end,
insurance=case vo_c_d when 'd' then insurance else -insurance end
where item_cd like 'BBB%' and jsign='ZZZ'
qua=case vo_c_d when 'd'then amount else -amount end ,
cost=case vo_c_d when 'd'then vo_sum else -vo_sum end
from table1
Where item_cd like 'AAA%' and jsign<>'AAA1' And ltrim(contno)<>'AAA2' and dcode like 'AAA3%'select fee=case vo_c_d when 'd' then fee else -fee end,
insurance=case vo_c_d when 'd' then insurance else -insurance end
from table1
where item_cd like 'BBB%' and jsign='ZZZ'
from
(
select contno=contno ,
qua=case vo_c_d when 'd'then amount else -amount end ,
cost=case vo_c_d when 'd'then vo_sum else -vo_sum end
from table1
Where item_cd like 'AAA%' and jsign<>'AAA1' And ltrim(contno)<>'AAA2' and dcode like 'AAA3%'
)a
inner join
(
select
contno=contno ,
fee=case vo_c_d when 'd' then fee else -fee end,
insurance=case vo_c_d when 'd' then insurance else -insurance end
from table1
where item_cd like 'BBB%' and jsign='ZZZ'
)b
on a.contno =b.contno --try
select contno=contno,
qua=
case
when (item_cd like 'AAA%' and jsign<>'AAA1' And ltrim(contno)<>'AAA2' and dcode like 'AAA3%')
then amount
else -amount
end,
cost=
case
when (item_cd like 'AAA%' and jsign<>'AAA1' And ltrim(contno)<>'AAA2' and dcode like 'AAA3%')
then vo_sum
else -vo_sum
end,
fee=
case
when (item_cd like 'BBB%' and jsign='ZZZ')
then fee
else -fee
end,
insurance=
case
when (item_cd like 'BBB%' and jsign='ZZZ')
then insurance
else -insurance
end
from tab
where vo_c_d = 'd'
select contno=contno,
qua=
case
when (item_cd like 'AAA%' and jsign<>'AAA1' And ltrim(contno)<>'AAA2' and dcode like 'AAA3%') and vo_c_d = 'd'
then amount
else -amount
end,
cost=
case
when (item_cd like 'AAA%' and jsign<>'AAA1' And ltrim(contno)<>'AAA2' and dcode like 'AAA3%') and vo_c_d = 'd'
then vo_sum
else -vo_sum
end,
fee=
case
when (item_cd like 'BBB%' and jsign='ZZZ') and vo_c_d = 'd'
then fee
else -fee
end,
insurance=
case
when (item_cd like 'BBB%' and jsign='ZZZ') and vo_c_d = 'd'
then insurance
else -insurance
end
from tab
----我开始是这么写的,后来"灵机一动"发现可以把vo_c_d = 'd'共用,却没注意到contno=contno是不需要vo_c_d = 'd'这个条件的.
在上面的语句中加上
where
(item_cd like 'AAA%' and jsign<>'AAA1' And ltrim(contno)<>'AAA2' and dcode like 'AAA3%')
or
(item_cd like 'BBB%' and jsign='ZZZ')