DECLARE @BDAT DATETIME
SELECT @BDAT=DBO.SYSMINDATE(MAX(ENDDAT)+1) FROM FINCLS
IF @BDAT IS NULL SELECT @BDAT= CONVERT(DATETIME,'19491001')select DPTNO = max(B.DPTNO),
DPTNAME = max(B.DPTNAME),
BEGCS = sum(A.BEGCS),
BEGQTY = sum(A.BEGQTY),
doc21cs = sum(A.doc21cs),
doc21net = sum(A.doc21net),
doc03net = sum(A.doc03net),
ENDCS = sum(A.ENDCS),
ENDQTY = sum(A.ENDQTY),
MGNAMT = sum(a.mgnamt),
mgnart = case when (a.doc21net+a.doc03net) = 0 then 0 else convert(numeric(16,2),a.mgnamt*100/(a.doc21net+a.doc03net)) end,
ID = A.DPTID,
PID = max(B.PID)
from BASDEPT B,
(
select DPTID = dbo.FindParentDPT(DPTID, 1), BEGCS, BEGQTY, doc21cs,doc21net,doc03net,mgnamt,ENDCS, ENDQTY
from
(
select DPTID= b.dptid,
BEGCS = sum(A.BEGCS),
BEGQTY = sum(A.BEGQTY),
DOC21CS = sum(A.DOC21CS),
doc21net = sum(c.doc21net),
doc03net = sum(c.doc03net),
MGNAMT = sum(c.mgnamt),
ENDCS = sum(A.endCS),
ENDQTY = sum(A.endQTY)
from dbo.GetFINSTK('2012-03-01', '2012-03-31') A, BASPLUCRTC B, dbo.GetFINMGN('2012-03-01', '2012-03-31') C
where A.PLUID = B.PLUID and a.pluid = c.pluid
group by B.DPTID
)A
)A
where A.DPTID = B.ID and ([DPTNO])
group by A.DPTID
order by DPTNO总是提示 a.doc21net既不包含在聚合函数中,也不包含在group by子句中...搞不懂哪里有问题
SELECT @BDAT=DBO.SYSMINDATE(MAX(ENDDAT)+1) FROM FINCLS
IF @BDAT IS NULL SELECT @BDAT= CONVERT(DATETIME,'19491001')select DPTNO = max(B.DPTNO),
DPTNAME = max(B.DPTNAME),
BEGCS = sum(A.BEGCS),
BEGQTY = sum(A.BEGQTY),
doc21cs = sum(A.doc21cs),
doc21net = sum(A.doc21net),
doc03net = sum(A.doc03net),
ENDCS = sum(A.ENDCS),
ENDQTY = sum(A.ENDQTY),
MGNAMT = sum(a.mgnamt),
mgnart = case when (a.doc21net+a.doc03net) = 0 then 0 else convert(numeric(16,2),a.mgnamt*100/(a.doc21net+a.doc03net)) end,
ID = A.DPTID,
PID = max(B.PID)
from BASDEPT B,
(
select DPTID = dbo.FindParentDPT(DPTID, 1), BEGCS, BEGQTY, doc21cs,doc21net,doc03net,mgnamt,ENDCS, ENDQTY
from
(
select DPTID= b.dptid,
BEGCS = sum(A.BEGCS),
BEGQTY = sum(A.BEGQTY),
DOC21CS = sum(A.DOC21CS),
doc21net = sum(c.doc21net),
doc03net = sum(c.doc03net),
MGNAMT = sum(c.mgnamt),
ENDCS = sum(A.endCS),
ENDQTY = sum(A.endQTY)
from dbo.GetFINSTK('2012-03-01', '2012-03-31') A, BASPLUCRTC B, dbo.GetFINMGN('2012-03-01', '2012-03-31') C
where A.PLUID = B.PLUID and a.pluid = c.pluid
group by B.DPTID
)A
)A
where A.DPTID = B.ID and ([DPTNO])
group by A.DPTID
order by DPTNO总是提示 a.doc21net既不包含在聚合函数中,也不包含在group by子句中...搞不懂哪里有问题
说我语法有问题
DECLARE @BDAT DATETIME
SELECT @BDAT=DBO.SYSMINDATE(MAX(ENDDAT)+1) FROM FINCLS
IF @BDAT IS NULL SELECT @BDAT= CONVERT(DATETIME,'19491001')select DPTNO = max(B.DPTNO),
DPTNAME = max(B.DPTNAME),
BEGCS = sum(A.BEGCS),
BEGQTY = sum(A.BEGQTY),
doc21cs = sum(A.doc21cs),
doc21net = sum(A.doc21net),
doc03net = sum(A.doc03net),
ENDCS = sum(A.ENDCS),
ENDQTY = sum(A.ENDQTY),
MGNAMT = sum(a.mgnamt),
mgnart = case when (a.doc21net+a.doc03net) = 0 then 0 else convert(numeric(16,2),a.mgnamt*100/(a.doc21net+a.doc03net)) end,
ID = A.DPTID,
PID = max(B.PID)
from BASDEPT B,
(
select DPTID = dbo.FindParentDPT(DPTID, 1), BEGCS, BEGQTY, doc21cs,doc21net,doc03net,mgnamt,ENDCS, ENDQTY
from
(
select DPTID= b.dptid,
BEGCS = sum(A.BEGCS),
BEGQTY = sum(A.BEGQTY),
DOC21CS = sum(A.DOC21CS),
doc21net = sum(c.doc21net),
doc03net = sum(c.doc03net),
MGNAMT = sum(c.mgnamt),
ENDCS = sum(A.endCS),
ENDQTY = sum(A.endQTY)
from dbo.GetFINSTK('2012-03-01', '2012-03-31') A, BASPLUCRTC B, dbo.GetFINMGN('2012-03-01', '2012-03-31') C
where A.PLUID = B.PLUID and a.pluid = c.pluid
group by B.DPTID
)A
)A
where A.DPTID = B.ID and ([DPTNO])
group by A.DPTID
order by DPTNO,a.doc21net,a.doc03net,a.mgnamt
错了,应该是b把case when里面的列加到group bu 后面,而不是order by
DECLARE @BDAT DATETIME
SELECT @BDAT=DBO.SYSMINDATE(MAX(ENDDAT)+1) FROM FINCLS
IF @BDAT IS NULL SELECT @BDAT= CONVERT(DATETIME,'19491001')select DPTNO = max(B.DPTNO),
DPTNAME = max(B.DPTNAME),
BEGCS = sum(A.BEGCS),
BEGQTY = sum(A.BEGQTY),
doc21cs = sum(A.doc21cs),
doc21net = sum(A.doc21net),
doc03net = sum(A.doc03net),
ENDCS = sum(A.ENDCS),
ENDQTY = sum(A.ENDQTY),
MGNAMT = sum(a.mgnamt),
mgnart = case when (a.doc21net+a.doc03net) = 0
then 0 else convert(numeric(16,2),
a.mgnamt*100/(a.doc21net+a.doc03net)) end,
ID = A.DPTID,
PID = max(B.PID)
from BASDEPT B,
(
select DPTID = dbo.FindParentDPT(DPTID, 1), BEGCS, BEGQTY, doc21cs,doc21net,doc03net,mgnamt,ENDCS, ENDQTY
from
(
select DPTID= b.dptid,
BEGCS = sum(A.BEGCS),
BEGQTY = sum(A.BEGQTY),
DOC21CS = sum(A.DOC21CS),
doc21net = sum(c.doc21net),
doc03net = sum(c.doc03net),
MGNAMT = sum(c.mgnamt),
ENDCS = sum(A.endCS),
ENDQTY = sum(A.endQTY)
from dbo.GetFINSTK('2012-03-01', '2012-03-31') A,
BASPLUCRTC B, dbo.GetFINMGN('2012-03-01', '2012-03-31') C
where A.PLUID = B.PLUID and a.pluid = c.pluid
group by B.DPTID
)A
)A
where A.DPTID = B.ID and ([DPTNO])
group by A.DPTID,a.doc21net,a.doc03net,a.mgnamt
order by DPTNO
这句不是聚合函数,7楼说的可以,上面这句加个AS MGNART
加到GROUP BY 子句中:group by (A.DPTID,MGNART)