27,579
社区成员
发帖
与我相关
我的任务
分享
;WITH RangeJE
AS
(
SELECT '>='+RTRIM(CASE WHEN b.number = 1 THEN 1
ELSE ( b.number - 1 ) * 10
END)+N'元<'+RTRIM(( b.number ) * 10)+N'元的单据总和' AS 项目
,CASE WHEN b.number = 1 THEN 1
ELSE ( b.number - 1 ) * 10
END AS Startje
,( b.number ) * 10 AS Endje
FROM master..spt_values AS b
WHERE b.type = 'P'
AND b.number >= 1
)
SELECT c.项目
,COUNT(s#) AS 统计
,SUM(je) AS 总和
,SUM(je) / COUNT(s#) AS 客单价
FROM sydbak AS a
,sybbak AS b
,RangeJE AS c
WHERE b# = s#
AND je >= c.Startje
AND je < c.Endje
AND ct < '2015-04-12'
AND ct > '2015-04-11'
GROUP BY c.项目
ORDER BY c.Startje;
注意书写要规范,je同s#是那一个表的?不指定时当列名相同时会报错
SELECT TOP 1000 number=IDENTITY(INT,1,1) INTO # FROM syscolumns AS a ,syscolumns AS b
SELECT '>='+RTRIM(CASE WHEN b.number = 1 THEN 1
ELSE ( b.number - 1 ) * 10
END)+N'元<'+RTRIM(( b.number ) * 10)+N'元的单据总和' AS 项目
,CASE WHEN b.number = 1 THEN 1
ELSE ( b.number - 1 ) * 10
END AS Startje
,( b.number ) * 10 AS Endje
INTO #RangeJE
FROM # AS b
SELECT c.项目
,COUNT(s#) AS 统计
,SUM(je) AS 总和
,SUM(je) / COUNT(s#) AS 客单价
FROM sydbak AS a
,sybbak AS b
,#RangeJE AS c
WHERE b# = s#
AND je >= c.Startje
AND je < c.Endje
AND ct < '2015-04-12'
AND ct > '2015-04-11'
GROUP BY c.项目
ORDER BY c.Startje;
DROP TABLE #,#RangeJE