22,210
社区成员
发帖
与我相关
我的任务
分享寻求统计月销售数据的问题
--统计脚本
--商品房每月统计
SELECT TOP (100) PERCENT a_1.楼盘名称,
ISNULL(b_1.今日销售套数, 0) AS 本月销售套数
FROM (SELECT i.ItemID, i.ItemName AS 楼盘名称, i.District AS 区域, i.OpenDate AS 开盘日期, SUM(CASE WHEN r.pactstatus <> '4' AND r.[use] IN ('住宅',
'商业') THEN 1 ELSE 0 END) AS 登记套数, SUM(CASE WHEN r.pactstatus <> '4' AND r.[use] IN ('住宅', '商业', '车库', '车位')
THEN r.buildarea ELSE 0 END) AS 登记面积, SUM(CASE WHEN (r.pactstatus = '3' AND r.[use] IN ('住宅', '商业')) THEN 1 ELSE 0 END)
AS 可售套数, SUM(CASE WHEN r.pactstatus = '1' AND r.[use] IN ('住宅', '商业') THEN 1 ELSE 0 END) AS 已售套数
FROM dbo.tabRoom AS r INNER JOIN
dbo.tabItem AS i ON r.ItemID = i.ItemID
WHERE (i.Status = '已审批') AND (r.RoomNo NOT LIKE '%Y')
GROUP BY i.ItemID, i.ItemName, i.District, i.OpenDate) AS a_1 LEFT OUTER JOIN
(SELECT d.ItemID, d.ItemName AS 楼盘名称, SUM(CASE WHEN c.pactstatus = '1' AND c.[use] IN ('住宅', '商业') THEN 1 ELSE 0 END)
AS 今日销售套数, ROUND(SUM(b.SumMoney) / SUM(c.BuildArea), 2) AS 今日成交均价,
ISNULL(SUM(CASE WHEN c.[use] = '住宅' THEN 1 ELSE 0 END), 0) AS 今日住宅销售套数,
ROUND(SUM(CASE WHEN c.[use] = '住宅' THEN b.summoney ELSE 0 END)
/ SUM(CASE WHEN c.[use] = '住宅' THEN c.buildarea ELSE NULL END), 2) AS 今日住宅成交均价
FROM dbo.tabPactPut AS a INNER JOIN
dbo.tabPactPutList AS b ON a.Keycode = b.Keycode INNER JOIN
dbo.tabRoom AS c ON b.RoomID = c.RoomID INNER JOIN
dbo.tabItem AS d ON c.ItemID = d.ItemID
WHERE (a.ValidFlag = '1') AND (c.AttachRoom = '0') AND (c.PactStatus = '1') AND (CONVERT(varchar(10), a.PutDate, 120) > '2015-07-01 00:00:00' and CONVERT(varchar(10), a.PutDate, 120) < '2015-07-31 23:59:59')
GROUP BY d.ItemName, d.ItemID) AS b_1 ON a_1.ItemID = b_1.ItemID
--ORDER BY 今日销售套数 DESC
统计结果：
我现在要求使结果能列出每个月的统计结果，如下，请问脚本应该如何处理，万分感谢！：
...全文
请发表友善的回复…
发表回复
江南雪_158 2015-09-01
- 打赏
- 举报
pivot函数是行转列函数，是2005版本中引入的函数，你使用该函数的数据库兼容性需要在2005以上就可以；[/quote]
其实我的数据库版本是：
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
Sep 16 2010 20:09:22
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
应该是支持这个函数的，很奇怪。[/quote]
这个跟数据版本没有多大关系，主要是兼容性的问题
「已注销」 2015-09-01
- 打赏
- 举报
我看了，可能这个数据库是从sql server 2000中升级过来的，因为它的兼容级别是sql server 2000 (80)
「已注销」 2015-09-01
- 打赏
- 举报
pivot函数是行转列函数，是2005版本中引入的函数，你使用该函数的数据库兼容性需要在2005以上就可以；[/quote]
其实我的数据库版本是：
Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (Intel X86)
Sep 16 2010 20:09:22
Copyright (c) 1988-2008 Microsoft Corporation
Enterprise Edition on Windows NT 6.0 <X86> (Build 6002: Service Pack 2)
应该是支持这个函数的，很奇怪。
江南雪_158 2015-09-01
- 打赏
- 举报
pivot函数是行转列函数，是2005版本中引入的函数，你使用该函数的数据库兼容性需要在2005以上就可以；
「已注销」 2015-08-31
- 打赏
- 举报
我发现是数据库版本兼容问题：
消息 325,级别 15,状态 1,第 47 行 'pivot' 附近有语法错误。您可能需要将当前数据库的兼容级别设置为更高的值，以启用此功能。
解决方式：
select compatibility_level from sys.databases where NAME='数据库'
--80
解决：
EXEC sp_dbcmptlevel 数据库, 90;
GO
*/
即可解决，非常感谢楼上的朋友！
「已注销」 2015-08-31
- 打赏
- 举报
楼上高手啊,请问运行后出现错误提示是哪儿有问题?
Tiger_Zhao 2015-08-31
- 打赏
- 举报
WITH t AS (
SELECT a_1.楼盘名称,
b_1.月份,
b_1.本月销售套数
FROM ( SELECT i.itemid,
i.itemname AS 楼盘名称
FROM dbo.tabroom AS r
INNER JOIN dbo.tabitem AS i
ON r.itemid = i.itemid
WHERE (i.status = '已审批')
AND (r.roomno NOT LIKE '%Y')
GROUP BY i.itemid,i.itemname
) AS a_1
LEFT JOIN (
SELECT d.itemid,
MONTH(a.putdate) 月份,
COUNT(*) 本月销售套数
FROM dbo.tabpactput AS a
INNER JOIN dbo.tabpactputlist AS b
ON a.keycode = b.keycode
INNER JOIN dbo.tabroom AS c
ON b.roomid = c.roomid
INNER JOIN dbo.tabitem AS d
ON c.itemid = d.itemid
WHERE (a.validflag = '1')
AND (c.attachroom = '0')
AND (c.pactstatus = '1')
AND c.[use] IN ('住宅','商业')
AND a.putdate >= '2015-01-01'
AND a.putdate < '2016-01-01'
GROUP BY dd.itemid,MONTH(a.putdate)
) AS b_1
ON a_1.itemid = b_1.itemid
)
SELECT 楼盘名称,
ISNULL([1],0) AS [1月],
ISNULL([2],0) AS [2月],
ISNULL([3],0) AS [3月],
ISNULL([4],0) AS [4月],
ISNULL([5],0) AS [5月],
ISNULL([6],0) AS [6月],
ISNULL([7],0) AS [7月],
ISNULL([8],0) AS [8月],
ISNULL([9],0) AS [9月],
ISNULL([10],0) AS [10月],
ISNULL([11],0) AS [11月],
ISNULL([12],0) AS [12月]
FROM t
PIVOT (MAX(本月销售套数)
FOR 月份 IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) p「已注销」 2015-08-31
- 打赏
- 举报
楼上的，能详细一点嘛？
yooq_csdn 2015-08-31
- 打赏
- 举报
先用 sum group by 项目名称,月份
做成 项目名称,月份,套数 的格式，再行专列。
