请教一条sql 一个表A 2个字段 saletime 例子:2009-3-16 2:15:10 salevalue(金额) :35.5要根据 日期 对金额 进行加法 看每天的营业额 但是一天有好多条记录 不同的时分秒 我该怎么按天 统计呢?请大虾指点! 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 select sub(salevalue) from TableA where to_date(saletime,'yyyy-MM-dd') = to_date('传入的日期','yyyy-MM-dd') select convert(varchar,saletime,111),sum(salevalue) from tbgroup by convert(varchar,saletime,111) SELECT CONVERT(NVARCHAR(8),saleTime,112),SUM(金额) FROM A SELECT CONVERT(NVARCHAR(8) as 日期,saleTime,112),SUM(金额) as 金额 FROM A GROUP BY CONVERT(NVARCHAR(8), --年select client_id , sum(case when datepart(month,order_time) = 1 then pro_amount*pro_price else 0 end) '1月', sum(case when datepart(month,order_time) = 2 then pro_amount*pro_price else 0 end) '2月', sum(case when datepart(month,order_time) = 3 then pro_amount*pro_price else 0 end) '3月', sum(case when datepart(month,order_time) = 4 then pro_amount*pro_price else 0 end) '4月', sum(case when datepart(month,order_time) = 5 then pro_amount*pro_price else 0 end) '5月', sum(case when datepart(month,order_time) = 6 then pro_amount*pro_price else 0 end) '6月', sum(case when datepart(month,order_time) = 7 then pro_amount*pro_price else 0 end) '7月', sum(case when datepart(month,order_time) = 8 then pro_amount*pro_price else 0 end) '8月', sum(case when datepart(month,order_time) = 9 then pro_amount*pro_price else 0 end) '9月', sum(case when datepart(month,order_time) = 10 then pro_amount*pro_price else 0 end) '10月', sum(case when datepart(month,order_time) = 11 then pro_amount*pro_price else 0 end) '11月', sum(case when datepart(month,order_time) = 12 then pro_amount*pro_price else 0 end) '12月'from order,item where order.order_id = item.order_idgroup by client_id-------------------------------------------季度select client_id , sum(case when datepart(quarter,order_time) = 1 then pro_amount*pro_price else 0 end) '第一季度', sum(case when datepart(quarter,order_time) = 2 then pro_amount*pro_price else 0 end) '第二季度', sum(case when datepart(quarter,order_time) = 3 then pro_amount*pro_price else 0 end) '第三季度', sum(case when datepart(quarter,order_time) = 4 then pro_amount*pro_price else 0 end) '第四季度'from order,item where order.order_id = item.order_idgroup by client_id-------------------------------------------------create table [order](order_id int,client_id int,order_time datetime)create table item(item_idint,order_id int,pro_id int,pro_amount int,pro_price int)insert into [order] select 1,1,'2007-1-5'union allselect 2,1,'2007-1-7'union allselect 3,1,'2007-6-5'union allselect 4,3,'2007-2-5'union allselect 5,3,'2007-2-18'insert into itemselect 1,1,1,10,10union allselect 2,1,3,5,15union allselect 3,2,1,5,12union allselect 4,3,2,10,8union allselect 5,4,3,2,15union allselect 6,5,2,6,10select client_id , sum(case when month(order_time) = 1 then pro_amount*pro_price else 0 end) '1月', sum(case when month(order_time) = 2 then pro_amount*pro_price else 0 end) '2月', sum(case when month(order_time) = 3 then pro_amount*pro_price else 0 end) '3月', sum(case when month(order_time) = 4 then pro_amount*pro_price else 0 end) '4月', sum(case when month(order_time) = 5 then pro_amount*pro_price else 0 end) '5月', sum(case when month(order_time) = 6 then pro_amount*pro_price else 0 end) '6月', sum(case when month(order_time) = 7 then pro_amount*pro_price else 0 end) '7月', sum(case when month(order_time) = 8 then pro_amount*pro_price else 0 end) '8月', sum(case when month(order_time) = 9 then pro_amount*pro_price else 0 end) '9月', sum(case when month(order_time) = 10 then pro_amount*pro_price else 0 end) '10月', sum(case when month(order_time) = 11 then pro_amount*pro_price else 0 end) '11月', sum(case when month(order_time) = 12 then pro_amount*pro_price else 0 end) '12月'from [order],item where [order].order_id = item.order_idgroup by client_id-----------------------------------------------------周select client_id , sum(case when datepart(week,order_time) = 1 then pro_amount*pro_price else 0 end) '第一周', sum(case when datepart(week,order_time) = 2 then pro_amount*pro_price else 0 end) '第二周', sum(case when datepart(week,order_time) = 3 then pro_amount*pro_price else 0 end) '第三周', sum(case when datepart(week,order_time) = 4 then pro_amount*pro_price else 0 end) '第四周', ......................from order,item where order.order_id = item.order_idgroup by client_id-------------------------------------------------------------------------日select client_id , convert(varchar(7),order_time,120) 月份, sum(case when datepart(day,order_time) = 1 then pro_amount*pro_price else 0 end) '1', sum(case when datepart(day,order_time) = 2 then pro_amount*pro_price else 0 end) '2', sum(case when datepart(day,order_time) = 3 then pro_amount*pro_price else 0 end) '3', sum(case when datepart(day,order_time) = 4 then pro_amount*pro_price else 0 end) '4', ...................... sum(case when datepart(day,order_time) = 4 then pro_amount*pro_price else 0 end) '31'from order,item where order.order_id = item.order_idgroup by client_id,convert(varchar(7),order_time,120)------------------------------------------------------------------------按周一、二计算(假设order_time为日期型数据,即不含有时,分,秒等)select client_id , sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) - 1 then pro_amount*pro_price else 0 end) '周日', sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) then pro_amount*pro_price else 0 end) '周一', sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 1 then pro_amount*pro_price else 0 end) '周二', sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 2 then pro_amount*pro_price else 0 end) '周三', sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 3 then pro_amount*pro_price else 0 end) '周四', sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 4 then pro_amount*pro_price else 0 end) '周五', sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 5 then pro_amount*pro_price else 0 end) '周六'from order,item where order.order_id = item.order_idgroup by client_id select datename(mm,date),sum(money) from table group by datename(mm,date) select datename(mm,date),sum(money) from table group by datename(d,date) SELECT convert(varchar(10),saletime,120) as saletime,sum(salevalue) as Total FROM T GROUP BY convert(varchar(10),saletime,120) where datapart(day,'数据库时间')=datapart(day,'传入时间') 再取和就可以了sum TrackBar调节系统声音大小 对象的问题? 网站主题文件夹中有css文件夹和style文件夹。。。 菜鸟问题~~ 怎么打开 mdf 文件 谁知道? 一个socket套接字连接的问题!在线急等 关于ToString()和Convert.toString()的区别 CSDN论坛 第8期专家问答——C#开发答疑 树形结构 我在将窗口最大化时(或者是在启动时全屏),不想让任务栏显示出来有什么办法可以作到! 怎么取得ascx文件里的控件的值,请看 谁给我推荐个IE内核的浏览器啊?
select sub(salevalue) from TableA where to_date(saletime,'yyyy-MM-dd') = to_date('传入的日期','yyyy-MM-dd')
group by convert(varchar,saletime,111)
--年
select client_id ,
sum(case when datepart(month,order_time) = 1 then pro_amount*pro_price else 0 end) '1月',
sum(case when datepart(month,order_time) = 2 then pro_amount*pro_price else 0 end) '2月',
sum(case when datepart(month,order_time) = 3 then pro_amount*pro_price else 0 end) '3月',
sum(case when datepart(month,order_time) = 4 then pro_amount*pro_price else 0 end) '4月',
sum(case when datepart(month,order_time) = 5 then pro_amount*pro_price else 0 end) '5月',
sum(case when datepart(month,order_time) = 6 then pro_amount*pro_price else 0 end) '6月',
sum(case when datepart(month,order_time) = 7 then pro_amount*pro_price else 0 end) '7月',
sum(case when datepart(month,order_time) = 8 then pro_amount*pro_price else 0 end) '8月',
sum(case when datepart(month,order_time) = 9 then pro_amount*pro_price else 0 end) '9月',
sum(case when datepart(month,order_time) = 10 then pro_amount*pro_price else 0 end) '10月',
sum(case when datepart(month,order_time) = 11 then pro_amount*pro_price else 0 end) '11月',
sum(case when datepart(month,order_time) = 12 then pro_amount*pro_price else 0 end) '12月'
from order,item where order.order_id = item.order_id
group by client_id
-----------------------------------------
--季度
select client_id ,
sum(case when datepart(quarter,order_time) = 1 then pro_amount*pro_price else 0 end) '第一季度',
sum(case when datepart(quarter,order_time) = 2 then pro_amount*pro_price else 0 end) '第二季度',
sum(case when datepart(quarter,order_time) = 3 then pro_amount*pro_price else 0 end) '第三季度',
sum(case when datepart(quarter,order_time) = 4 then pro_amount*pro_price else 0 end) '第四季度'
from order,item where order.order_id = item.order_id
group by client_id
-------------------------------------------------
create table [order]
(
order_id int,
client_id int,
order_time datetime
)
create table item
(
item_idint,
order_id int,
pro_id int,
pro_amount int,
pro_price int
)
insert into [order]
select 1,1,'2007-1-5'
union all
select 2,1,'2007-1-7'
union all
select 3,1,'2007-6-5'
union all
select 4,3,'2007-2-5'
union all
select 5,3,'2007-2-18'
insert into item
select 1,1,1,10,10
union all
select 2,1,3,5,15
union all
select 3,2,1,5,12
union all
select 4,3,2,10,8
union all
select 5,4,3,2,15
union all
select 6,5,2,6,10select client_id ,
sum(case when month(order_time) = 1 then pro_amount*pro_price else 0 end) '1月',
sum(case when month(order_time) = 2 then pro_amount*pro_price else 0 end) '2月',
sum(case when month(order_time) = 3 then pro_amount*pro_price else 0 end) '3月',
sum(case when month(order_time) = 4 then pro_amount*pro_price else 0 end) '4月',
sum(case when month(order_time) = 5 then pro_amount*pro_price else 0 end) '5月',
sum(case when month(order_time) = 6 then pro_amount*pro_price else 0 end) '6月',
sum(case when month(order_time) = 7 then pro_amount*pro_price else 0 end) '7月',
sum(case when month(order_time) = 8 then pro_amount*pro_price else 0 end) '8月',
sum(case when month(order_time) = 9 then pro_amount*pro_price else 0 end) '9月',
sum(case when month(order_time) = 10 then pro_amount*pro_price else 0 end) '10月',
sum(case when month(order_time) = 11 then pro_amount*pro_price else 0 end) '11月',
sum(case when month(order_time) = 12 then pro_amount*pro_price else 0 end) '12月'
from [order],item where [order].order_id = item.order_id
group by client_id
---------------------------------------------------
--周
select client_id ,
sum(case when datepart(week,order_time) = 1 then pro_amount*pro_price else 0 end) '第一周',
sum(case when datepart(week,order_time) = 2 then pro_amount*pro_price else 0 end) '第二周',
sum(case when datepart(week,order_time) = 3 then pro_amount*pro_price else 0 end) '第三周',
sum(case when datepart(week,order_time) = 4 then pro_amount*pro_price else 0 end) '第四周',
......................
from order,item where order.order_id = item.order_id
group by client_id
-----------------------------------------------------------------------
--日
select client_id , convert(varchar(7),order_time,120) 月份,
sum(case when datepart(day,order_time) = 1 then pro_amount*pro_price else 0 end) '1',
sum(case when datepart(day,order_time) = 2 then pro_amount*pro_price else 0 end) '2',
sum(case when datepart(day,order_time) = 3 then pro_amount*pro_price else 0 end) '3',
sum(case when datepart(day,order_time) = 4 then pro_amount*pro_price else 0 end) '4',
......................
sum(case when datepart(day,order_time) = 4 then pro_amount*pro_price else 0 end) '31'
from order,item where order.order_id = item.order_id
group by client_id,convert(varchar(7),order_time,120)
----------------------------------------------------------------------
--按周一、二计算(假设order_time为日期型数据,即不含有时,分,秒等)
select client_id ,
sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) - 1 then pro_amount*pro_price else 0 end) '周日',
sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) then pro_amount*pro_price else 0 end) '周一',
sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 1 then pro_amount*pro_price else 0 end) '周二',
sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 2 then pro_amount*pro_price else 0 end) '周三',
sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 3 then pro_amount*pro_price else 0 end) '周四',
sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 4 then pro_amount*pro_price else 0 end) '周五',
sum(case when order_time = DATEADD(wk, DATEDIFF(wk,0,getdate()), 0) + 5 then pro_amount*pro_price else 0 end) '周六'
from order,item where order.order_id = item.order_id
group by client_id
再取和就可以了sum