SQL语句
求一个SQL语句！
我还想加一个条件，就是QTY 大于0，又不想重新来一次select 请问这个
where qty > 0 加在哪里？
------解决方案--------------------
子查询加，或者后面加Having
- Delphi(Pascal) code
select code, sum(qty) as qty from
(Select code,qty from indl
union all
select code,-qty from Tadd
union all
select code,-qty from outdl
union all
select code,sum(Inqty-Outqty) as qty from Inout group by code) aa
where code='PPST0512457'
group by Code';
我还想加一个条件，就是QTY 大于0，又不想重新来一次select 请问这个
where qty > 0 加在哪里？
------解决方案--------------------
子查询加，或者后面加Having
- SQL code
select code, sum(qty) as qty from
(Select code,qty from indl where qty > 0
union all
select code,-qty from Tadd where qty > 0
union all
select code,-qty from outdl where qty > 0
union all
select code,sum(Inqty-Outqty) as qty from Inout where qty > 0 group by code) aa
where code='PPST0512457' and where qty > 0
group by Code
select code, sum(qty) as qty from
(Select code,qty from indl
union all
select code,-qty from Tadd
union all
select code,-qty from outdl
union all
select code,sum(Inqty-Outqty) as qty from Inout group by code) aa
where code='PPST0512457'
group by Code Having sum(qty)>0
------解决方案--------------------
不要Having,就這樣:
- SQL code
select code, sum(qty) as qty from
(Select code,qty from indl where qty > 0
union all
select code,-qty from Tadd where qty > 0
union all
select code,-qty from outdl where qty > 0
union all
select code,sum(Inqty-Outqty) as qty from Inout where qty > 0 group by code) aa
where code='PPST0512457'
group by Code
------解决方案--------------------
应该是这样:
- SQL code
select code, sum(case when qty>0 then qty else 0) as qty from
(Select code,qty from indl
union all
select code,-qty from Tadd
union all
select code,-qty from outdl
union all
select code,sum(Inqty-Outqty) as qty from Inout group by code) aa
where code='PPST0512457'
group by Code';