H
han--bing
Unregistered / Unconfirmed
GUEST, unregistred user!
SELECT aa.deptno, aa.workkind, aa.cpcode, aa.cpname, aa.spectype, SUM(aa.kcnum) AS kcnum, aa.unit, bb.pname,aa.jobprg,cc.origin,cc.ltype,cc.stype,cc.cpkind
FROM (
SELECT deptno, cpcode, cpname, isnull(spectype,'')as spectype, unit, 0 AS workkind, SUM(storenum) AS kcnum, 1 AS inflag,Null as jobprg
FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind >= 0 (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
UNION
SELECT deptno, cpcode, cpname, isnull(spectype,'')as spectype, unit, 0 AS workkind, SUM(storenum * - 1) AS kcnum, - 1 AS inflag,null as jobprg FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind < 0 AND (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
) aa
LEFT OUTER JOIN
dbo.pub_dpt bb ON aa.deptno = bb.pcode
inner join cp_info cc on aa.cpcode=cc.cpcode
GROUP BY aa.deptno, aa.workkind, aa.cpcode, aa.cpname, aa.spectype, aa.unit, bb.pname,aa.jobprg,cc.origin,cc.ltype,cc.stype,cc.cpkind
在SQL分析器可以通过,在DELPHI怎么改写啊?
FROM (
SELECT deptno, cpcode, cpname, isnull(spectype,'')as spectype, unit, 0 AS workkind, SUM(storenum) AS kcnum, 1 AS inflag,Null as jobprg
FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind >= 0 (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
UNION
SELECT deptno, cpcode, cpname, isnull(spectype,'')as spectype, unit, 0 AS workkind, SUM(storenum * - 1) AS kcnum, - 1 AS inflag,null as jobprg FROM sc_deptstoreroomlist WHERE instorekind % 4 <> 0 AND instorekind < 0 AND (DDATE between ''' + DateToStr(datetimepicker1.Date) + ''' and ''' + DateToStr(datetimepicker2.Date) + ''') GROUP BY deptno, cpcode, cpname, spectype, unit,jobprg
) aa
LEFT OUTER JOIN
dbo.pub_dpt bb ON aa.deptno = bb.pcode
inner join cp_info cc on aa.cpcode=cc.cpcode
GROUP BY aa.deptno, aa.workkind, aa.cpcode, aa.cpname, aa.spectype, aa.unit, bb.pname,aa.jobprg,cc.origin,cc.ltype,cc.stype,cc.cpkind
在SQL分析器可以通过,在DELPHI怎么改写啊?