ALTER FUNCTION [dbo].[XSTJ] (@TIME char(6))
returns table
as
return
(SELECT SUBSTRING(TD004,1,3) AS 产品类别,SUM(A) AS 本月累计订单,SUM(B) AS 年累计订单,
SUM(C) AS 本月累计销货,SUM(D) AS 年累计销货,SUM(E) AS 本月累计订单数,SUM(F) AS 年累计订单数,
SUM(G) AS 本月累计销货数,SUM(H) AS 年累计销货数 FROM
(
SELECT SUBSTRING(TD004,1,3),SUM(TD012) AS A,0 AS B,0 AS C,0 AS D,SUM(TD008) AS E,0 AS F,0 AS G,0 AS H
FROM COPTD
LEFT JOIN COPTC ON TC002=TD002 AND TC001=TD001 AND SUBSTRING(TC003,1,6)=@TIME
GROUP BY SUBSTRING(TD004,1,3)UNION
SELECT SUBSTRING(TD004,1,3),0 AS A,SUM(TD012) AS B,0 AS C,0 AS D,0 AS E,SUM(TD008) AS F,0 AS G,0 AS H
FROM COPTD
LEFT JOIN COPTC ON TC002=TD002 AND TC001=TD001 AND SUBSTRING(TC003,1,4)=SUBSTRING(@TIME,1,4)
GROUP BY SUBSTRING(TD004,1,3)UNION
SELECT SUBSTRING(TH004,1,3),0 AS A,0 AS B,SUM(TH013) AS C,0 AS D,0 AS E,0 AS F,SUM(TH008) AS G,0 AS H
FROM COPTH
LEFT JOIN COPTG ON TG002=TH002 AND TG001=TH001 AND SUBSTRING(TG003,1,6)=@TIME
GROUP BY SUBSTRING(TH004,1,3)UNION
SELECT SUBSTRING(TH004,1,3),0 AS A,0 AS B,0 AS C,SUM(TH013) AS D,0 AS E,0 AS F,0 AS G,SUM(TH008) AS H
FROM COPTH
LEFT JOIN COPTG ON TG002=TH002 AND TG001=TH001 AND SUBSTRING(TG003,1,4)=SUBSTRING(@TIME,1,4)
GROUP BY SUBSTRING(TH004,1,3)
) mytable
GROUP BY SUBSTRING(TD004,1,3)
)消息 8155,级别 16,状态 2,过程 XSTJ,第 5 行
没有为 'mytable' 的列 1 指定任何列。
消息 207,级别 16,状态 1,过程 XSTJ,第 32 行
列名 'TD004' 无效。
消息 207,级别 16,状态 1,过程 XSTJ,第 5 行
列名 'TD004' 无效。
returns table
as
return
(SELECT SUBSTRING(TD004,1,3) AS 产品类别,SUM(A) AS 本月累计订单,SUM(B) AS 年累计订单,
SUM(C) AS 本月累计销货,SUM(D) AS 年累计销货,SUM(E) AS 本月累计订单数,SUM(F) AS 年累计订单数,
SUM(G) AS 本月累计销货数,SUM(H) AS 年累计销货数 FROM
(
SELECT SUBSTRING(TD004,1,3),SUM(TD012) AS A,0 AS B,0 AS C,0 AS D,SUM(TD008) AS E,0 AS F,0 AS G,0 AS H
FROM COPTD
LEFT JOIN COPTC ON TC002=TD002 AND TC001=TD001 AND SUBSTRING(TC003,1,6)=@TIME
GROUP BY SUBSTRING(TD004,1,3)UNION
SELECT SUBSTRING(TD004,1,3),0 AS A,SUM(TD012) AS B,0 AS C,0 AS D,0 AS E,SUM(TD008) AS F,0 AS G,0 AS H
FROM COPTD
LEFT JOIN COPTC ON TC002=TD002 AND TC001=TD001 AND SUBSTRING(TC003,1,4)=SUBSTRING(@TIME,1,4)
GROUP BY SUBSTRING(TD004,1,3)UNION
SELECT SUBSTRING(TH004,1,3),0 AS A,0 AS B,SUM(TH013) AS C,0 AS D,0 AS E,0 AS F,SUM(TH008) AS G,0 AS H
FROM COPTH
LEFT JOIN COPTG ON TG002=TH002 AND TG001=TH001 AND SUBSTRING(TG003,1,6)=@TIME
GROUP BY SUBSTRING(TH004,1,3)UNION
SELECT SUBSTRING(TH004,1,3),0 AS A,0 AS B,0 AS C,SUM(TH013) AS D,0 AS E,0 AS F,0 AS G,SUM(TH008) AS H
FROM COPTH
LEFT JOIN COPTG ON TG002=TH002 AND TG001=TH001 AND SUBSTRING(TG003,1,4)=SUBSTRING(@TIME,1,4)
GROUP BY SUBSTRING(TH004,1,3)
) mytable
GROUP BY SUBSTRING(TD004,1,3)
)消息 8155,级别 16,状态 2,过程 XSTJ,第 5 行
没有为 'mytable' 的列 1 指定任何列。
消息 207,级别 16,状态 1,过程 XSTJ,第 32 行
列名 'TD004' 无效。
消息 207,级别 16,状态 1,过程 XSTJ,第 5 行
列名 'TD004' 无效。
FROM COPTD
LEFT JOIN COPTC ON TC002=TD002 AND TC001=TD001 AND SUBSTRING(TC003,1,6)=@TIME
GROUP BY SUBSTRING(TD004,1,3)
消息 208,级别 16,状态 6,过程 XSTJ,第 5 行
对象名 'dbo.XSTJ' 无效。