求关于统计的SQL语句

winxql 2006-05-08 04:48:25
表 TB_Statistic
ID Money BuyTime TypeID
1 200块 2005-05-01 10:40 1
2 250块 2005-05-06 11:20 1
3 220块 2005-05-07 12:55 2
4 300块 2005-06-01 11:25 2
5 240块 2005-07-21 12:35 3
6 400块 2005-07-11 13:45 4
7 450块 2005-08-01 01:20 1
8 200块 2006-05-01 10:40 1
9 250块 2006-05-06 11:20 2
10 220块 2006-05-07 12:55 3
11 300块 2006-06-01 11:25 5
12 240块 2006-07-21 12:35 3
13 400块 2006-07-11 13:45 2
14 450块 2006-08-01 01:20 1

TB_Type
TypeID TypeName
1 一类
2 二类
3 三类
4 四类
5 五类


类型 1月 2月 3月 4月 … 12月

把表中的数据从一月到十二月分别统计出来，行吗
请指教。。

之前已结贴的问题地址，可作参考
http://community.csdn.net/Expert/topic/4736/4736296.xml?temp=.9762689
...全文
204 16 打赏 收藏 转发到动态 举报
写回复
用AI写文章
16 条回复
切换为时间正序
请发表友善的回复…
发表回复
winxql 2006-05-09
  • 打赏
  • 举报
 回复
谢谢。。
paoluo 2006-05-09
  • 打赏
  • 举报
 回复
暈 ，

SUM(Case month(BuyTime) When 1 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [1月],

改為

SUM(Case month(BuyTime) When 1 Then [Money] Else 0 End) As [1月],


其余的相應做更改.
winxql 2006-05-09
  • 打赏
  • 举报
 回复
哦。。我数据库里的没有“块”的，之前是为了问得更清楚才加的，一时忘记了，谢谢
paoluo 2006-05-09
  • 打赏
  • 举报
 回复
你的數據的最後一位是一個中文字符"块",去掉勒才能匯總撒。
winxql 2006-05-09
  • 打赏
  • 举报
 回复
left(Money,len(Money)-1
为什么一定要把数据后面的最后一位减掉呢？？还是不明白，是不是浮点数后面带什么特殊符号啊？
paoluo 2006-05-09
  • 打赏
  • 举报
 回复
如果有“250.55块”这样的數據，就改為

cast(left(Money,len(Money)-1) as Money)
winxql 2006-05-09
  • 打赏
  • 举报
 回复
有个小问题，加这一句有什么用处，如果金额里面有这样的数250.55块，那统计的数据不就不精确了吗？？
cast(left(Money,len(Money)-1) as int)
paoluo 2006-05-09
  • 打赏
  • 举报
 回复
可以啊。看這個答復，我都得到結果了。


--建立測試環境
Create Table TB_Statistic(ID Int,Money Nvarchar(20),BuyTime Datetime,TypeID Int)
Create Table TB_Type
(TypeIDInt,
TypeNameNvarchar(10))
--插入數據
Insert TB_Statistic Select 1, N'200块', '2005-05-01 10:40', 1
Union All Select 2, N'250块', '2005-05-06 11:20', 1
Union All Select 3, N'220块', '2005-05-07 12:55', 2
Union All Select 4, N'300块', '2005-06-01 11:25', 2
Union All Select 5, N'240块', '2005-07-21 12:35', 3
Union All Select 6, N'400块', '2005-07-11 13:45', 4
Union All Select 7, N'450块', '2005-08-01 01:20', 1
Union All Select 8, N'200块', '2006-05-01 10:40', 1
Union All Select 9, N'250块', '2006-05-06 11:20', 2
Union All Select 10, N'220块', '2006-05-07 12:55', 3
Union All Select 11, N'300块', '2006-06-01 11:25', 5
Union All Select 12, N'240块', '2006-07-21 12:35', 3
Union All Select 13, N'400块', '2006-07-11 13:45', 2
Union All Select 14, N'450块', '2006-08-01 01:20', 1

Insert TB_Type Select 1, N'一类'
Union All Select 2, N'二类'
Union All Select 3, N'三类'
Union All Select 4, N'四类'
Union All Select 5, N'五类'
--測試
Select
B.TypeName,
SUM(Case month(BuyTime) When 1 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [1月],
SUM(Case month(BuyTime) When 2 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [2月],
SUM(Case month(BuyTime) When 3 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [3月],
SUM(Case month(BuyTime) When 4 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [4月],
SUM(Case month(BuyTime) When 5 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [5月],
SUM(Case month(BuyTime) When 6 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [6月],
SUM(Case month(BuyTime) When 7 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [7月],
SUM(Case month(BuyTime) When 8 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [8月],
SUM(Case month(BuyTime) When 9 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [9月],
SUM(Case month(BuyTime) When 10 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [10月],
SUM(Case month(BuyTime) When 11 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [11月],
SUM(Case month(BuyTime) When 12 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [12月]
from TB_Statistic A
Inner Join TB_Type B
On A.TypeID=B.TypeID
Where Year(BuyTime)=2006
Group By B.TypeName
--刪除測試環境
Drop Table TB_Statistic,TB_Type
--結果
/*
TypeName1月2月3月4月5月6月7月8月9月10月11月12月
一类0000200004500000
二类0000250040000000
三类0000220024000000
五类00000300000000
*/

winxql 2006-05-09
  • 打赏
  • 举报
 回复
谢谢帮忙。
我希望有的效果是这样的，对各种类型的统计进行比较
类型 1月 2月3月4月…12月
一类
二类
三类
四类
五类

现在好像还是不行哦，只能得出一种类型的
winxql 2006-05-09
  • 打赏
  • 举报
 回复
COUNT得出的不是个数的吗？我要算的是总和，应该是用SUM吧。
paoluo 2006-05-08
  • 打赏
  • 举报
 回复
yanglszt(YangLing) ( ) 信誉：100 2006-5-8 20:02:21 得分: 0



這麼長,建議換成COUNT

----------------------------------------------------------------
1.是count長，還是SUM長？？

2.正確的語句為什麼要改成錯誤的？？
yanglszt 2006-05-08
  • 打赏
  • 举报
 回复
這麼長,建議換成COUNT

Select
B.TypeName,
COUNT(Case month(BuyTime) When 1 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [1月],
COUNT(Case month(BuyTime) When 2 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [2月],
COUNT(Case month(BuyTime) When 3 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [3月],
COUNT(Case month(BuyTime) When 4 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [4月],
COUNT(Case month(BuyTime) When 5 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [5月],
COUNT(Case month(BuyTime) When 6 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [6月],
COUNT(Case month(BuyTime) When 7 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [7月],
COUNT(Case month(BuyTime) When 8 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [8月],
COUNT(Case month(BuyTime) When 9 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [9月],
COUNT(Case month(BuyTime) When 10 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [10月],
COUNT(Case month(BuyTime) When 11 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [11月],
COUNT(Case month(BuyTime) When 12 Then cast(left(Money,len(Money)-1) as int) BuyTime End) As [12月]
from TB_Statistic A
Inner Join TB_Type B
On A.TypeID=B.TypeID
Where Year(BuyTime)=2006 And TypeName=N'二类'
Group By Rtrim(Month(BuyTime))+'月',B.TypeName
paoluo 2006-05-08
  • 打赏
  • 举报
 回复
--建立測試環境
Create Table TB_Statistic(ID Int,Money Nvarchar(20),BuyTime Datetime,TypeID Int)
Create Table TB_Type
(TypeID Int,
TypeName Nvarchar(10))
--插入數據
Insert TB_Statistic Select 1, N'200块', '2005-05-01 10:40', 1
Union All Select 2, N'250块', '2005-05-06 11:20', 1
Union All Select 3, N'220块', '2005-05-07 12:55', 2
Union All Select 4, N'300块', '2005-06-01 11:25', 2
Union All Select 5, N'240块', '2005-07-21 12:35', 3
Union All Select 6, N'400块', '2005-07-11 13:45', 4
Union All Select 7, N'450块', '2005-08-01 01:20', 1
Union All Select 8, N'200块', '2006-05-01 10:40', 1
Union All Select 9, N'250块', '2006-05-06 11:20', 2
Union All Select 10, N'220块', '2006-05-07 12:55', 3
Union All Select 11, N'300块', '2006-06-01 11:25', 5
Union All Select 12, N'240块', '2006-07-21 12:35', 3
Union All Select 13, N'400块', '2006-07-11 13:45', 2
Union All Select 14, N'450块', '2006-08-01 01:20', 1

Insert TB_Type Select 1, N'一类'
Union All Select 2, N'二类'
Union All Select 3, N'三类'
Union All Select 4, N'四类'
Union All Select 5, N'五类'
--測試
Select
B.TypeName,
SUM(Case month(BuyTime) When 1 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [1月],
SUM(Case month(BuyTime) When 2 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [2月],
SUM(Case month(BuyTime) When 3 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [3月],
SUM(Case month(BuyTime) When 4 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [4月],
SUM(Case month(BuyTime) When 5 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [5月],
SUM(Case month(BuyTime) When 6 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [6月],
SUM(Case month(BuyTime) When 7 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [7月],
SUM(Case month(BuyTime) When 8 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [8月],
SUM(Case month(BuyTime) When 9 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [9月],
SUM(Case month(BuyTime) When 10 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [10月],
SUM(Case month(BuyTime) When 11 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [11月],
SUM(Case month(BuyTime) When 12 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [12月]
from TB_Statistic A
Inner Join TB_Type B
On A.TypeID=B.TypeID
Where Year(BuyTime)=2006
Group By B.TypeName
--刪除測試環境
Drop Table TB_Statistic,TB_Type
--結果
/*
TypeName 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
一类 0 0 0 0 200 0 0 450 0 0 0 0
二类 0 0 0 0 250 0 400 0 0 0 0 0
三类 0 0 0 0 220 0 240 0 0 0 0 0
五类 0 0 0 0 0 300 0 0 0 0 0 0
*/
paoluo 2006-05-08
  • 打赏
  • 举报
 回复
Select
B.TypeName,
SUM(Case month(BuyTime) When 1 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [1月],
SUM(Case month(BuyTime) When 2 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [2月],
SUM(Case month(BuyTime) When 3 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [3月],
SUM(Case month(BuyTime) When 4 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [4月],
SUM(Case month(BuyTime) When 5 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [5月],
SUM(Case month(BuyTime) When 6 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [6月],
SUM(Case month(BuyTime) When 7 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [7月],
SUM(Case month(BuyTime) When 8 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [8月],
SUM(Case month(BuyTime) When 9 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [9月],
SUM(Case month(BuyTime) When 10 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [10月],
SUM(Case month(BuyTime) When 11 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [11月],
SUM(Case month(BuyTime) When 12 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [12月]
from TB_Statistic A
Inner Join TB_Type B
On A.TypeID=B.TypeID
Where Year(BuyTime)=2006 And TypeName=N'二类'
Group By B.TypeName
paoluo 2006-05-08
  • 打赏
  • 举报
 回复
Select
B.TypeName,
SUM(Case month(BuyTime) When 1 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [1月],
SUM(Case month(BuyTime) When 2 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [2月],
SUM(Case month(BuyTime) When 3 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [3月],
SUM(Case month(BuyTime) When 4 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [4月],
SUM(Case month(BuyTime) When 5 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [5月],
SUM(Case month(BuyTime) When 6 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [6月],
SUM(Case month(BuyTime) When 7 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [7月],
SUM(Case month(BuyTime) When 8 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [8月],
SUM(Case month(BuyTime) When 9 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [9月],
SUM(Case month(BuyTime) When 10 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [10月],
SUM(Case month(BuyTime) When 11 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [11月],
SUM(Case month(BuyTime) When 12 Then cast(left(Money,len(Money)-1) as int) Else 0 End) As [12月]
from TB_Statistic A
Inner Join TB_Type B
On A.TypeID=B.TypeID
Where Year(BuyTime)=2006 And TypeName=N'二类'
Group By Rtrim(Month(BuyTime))+'月',B.TypeName
pengdali 2006-05-08
  • 打赏
  • 举报
 回复
SELECT 类型,
SUM(CASE month(BuyTime) WHEN 1 THEN cast(left(a.Money,len(a.Money)-1) as int) ELSE 0 END) AS [1月],
SUM(CASE month(BuyTime) WHEN 2 THEN cast(left(a.Money,len(a.Money)-1) as int) ELSE 0 END) AS [2月],
SUM(CASE month(BuyTime) WHEN 3 THEN cast(left(a.Money,len(a.Money)-1) as int) ELSE 0 END) AS [3月],
SUM(CASE month(BuyTime) WHEN 4 THEN cast(left(a.Money,len(a.Money)-1) as int) ELSE 0 END) AS [4月],
......
FROM 表
GROUP BY 类型
经典SQL语句大全
SQL语句参考，包含Access、MySQL 以及 SQL Server基础创建数据库CREATE DATABASE database-name 删除数据库drop database dbname 备份sql server 创建 备份数据的 device USE master EXEC sp_addumpdevice 'disk', ...
SQL统计语句
1、SQL COUNT() 函数 ：SQL COUNT() 函数 2、SQL SUM() 函数：SQL SUM() 函数 3、GROUP BY 语句：SQL GROUP BY 语句
SQL语句统计每天、每月、每年的销售总额
SQL语句统计每天、每月、每年的销售总额 每月 SQL语句统计每天、每月、每年的销售总额 每日 SQL语句统计每天、每月、每年的销售总额 总结 SQL语句统计每天、每月、每年的销售总额 SQL语句统计每天、每月、每年的...
根据日期统计sql语句
根据日期查询 的 sql语句 天/周/月/季度/年
数据库常用的sql语句大全--sql
每个SQL语句都是亲身实验验证的，并且经过自己的思考的。能够保证sql语句的可运行性。 sql语句的命令不区分大小写，但储存的数据是区分大小写的。在这里我们统一使用英文小写进行命令编辑。如果喜欢大写的可以使用...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章