(select g.islcode,g.idx,g.salordcode,g.gcode,g.gattr1,g.gattr2,g.gattr3,g.gattr4,g.gattr5,g.gattr6,g.gattr7,g.gattr8,
g.gattr9,g.qty from storeslist3g g where islcode in (select islcode from storeslist3g group by islcode,idx having
sum(qty*dc)>0)) as g
left outer join
(select islcode,idx,sum(qty*(1-dc))/2 as copyqty,(sum(qty*dc)/2 - sum(qty*(1-dc))/2) as copyqty from storeslist3g group by islcode) as temp on g.islcode = temp.islcode and g.idx = temp.idx
g.gattr9,g.qty from storeslist3g g where islcode in (select islcode from storeslist3g group by islcode,idx having
sum(qty*dc)>0)) as g
left outer join
(select islcode,idx,sum(qty*(1-dc))/2 as copyqty,(sum(qty*dc)/2 - sum(qty*(1-dc))/2) as copyqty from storeslist3g group by islcode) as temp on g.islcode = temp.islcode and g.idx = temp.idx
select * from
(select g.islcode,g.idx,g.salordcode,g.gcode,g.gattr1,g.gattr2,g.gattr3,g.gattr4,g.gattr5,g.gattr6,g.gattr7,g.gattr8,
g.gattr9,g.qty
from storeslist3g g
where islcode in (select islcode from storeslist3g group by islcode,idx having
sum(qty*dc)>0)
) as g
left outer join
(select islcode,idx,sum(qty*(1-dc))/2 as copyqty,(sum(qty*dc)/2 - sum(qty*(1-dc))/2) as copyqty
from storeslist3g
group by islcode
) as temp
on g.islcode = temp.islcode and g.idx = temp.idx
SELECT g.* from (
SELECT g.islcode,g.idx,g.salordcode,g.gcode,
g.gattr1,g.gattr2,g.gattr3,g.gattr4,
g.gattr5,g.gattr6,g.gattr7,g.gattr8,
g.gattr9,g.qty
FROM storeslist3g g
WHERE islcode IN (SELECT islcode FROM storeslist3g GROUP BY islcode,idx HAVING Sum(qty*dc)>0)) as g
LEFT OUTER JOIN
(SELECT islcode,idx,sum(qty*(1-dc))/2 AS copyqty,(sum(qty*dc)/2 - sum(qty*(1-dc))/2) AS copyqty
FROM storeslist3g GROUP BY islcode) AS temp
ON g.islcode = temp.islcode AND g.idx = temp.idx
--或者
SELECT g.islcode,g.idx,g.salordcode,g.gcode,
g.gattr1,g.gattr2,g.gattr3,g.gattr4,
g.gattr5,g.gattr6,g.gattr7,g.gattr8,
g.gattr9,g.qty
FROM storeslist3g g
LEFT OUTER JOIN
(SELECT islcode,idx,sum(qty*(1-dc))/2 AS copyqty,(sum(qty*dc)/2 - sum(qty*(1-dc))/2) AS copyqty
FROM storeslist3g GROUP BY islcode) AS temp ON g.islcode = temp.islcode AND g.idx = temp.idx
where g.islcode IN (SELECT islcode FROM storeslist3g GROUP BY islcode,idx HAVING Sum(qty*dc)>0)
SELECT * FROM (
select g.islcode,
g.idx,
g.salordcode,
g.gcode,
g.gattr1,
g.gattr2,
g.gattr3,
g.gattr4,
g.gattr5,
g.gattr6,
g.gattr7,
g.gattr8,
g.gattr9,
g.qty
from storeslist3g g
where islcode in ( select islcode
from storeslist3g
group by islcode,idx
having sum(qty*dc)>0)) as d
left outer join
(
select islcode,
idx,
sum(qty*(1-dc))/2 as copyqty,
(sum(qty*dc)/2 - sum(qty*(1-dc))/2) as copyqty
from storeslist3g
group by islcode) as temp on d.islcode = temp.islcode and d.idx = temp.idx