有关日期问题的sql

fft123 2003-12-17 11:10:40
数据库中有两个字段:appDate,money
值如下:
appDate money
2003-10-22 00:00:00 400
2003-10-22 12:30:00 600
2003-10-23 00:00:00 2000

现在报表要求取这样的结果:
Date weekday money
----------- -------- -------
2003-10-22 星期三 1000
2003-10-23 星期四 2000

用这样的语句取出来的日期值不能合并;
SELECT Convert(Char(10),appDate,20) as Day,
Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END as weekday,
sum(money)
from table
group by appDate

而用这样的语句就报错
SELECT Convert(Char(10),appDate,20) as Day,
Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END as weekday,
sum(money)
from table
group by Convert(Char(10),appDate,20)

麻烦大家帮忙看看怎么解决这个问题,或者用其他方式取出报表要求的结果
...全文
38 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
fft123 2003-12-17
  • 打赏
  • 举报
回复
多谢
结贴
victorycyz 2003-12-17
  • 打赏
  • 举报
回复


select
convert(varchar(10),appdate,120) as "date",
datename(dw,appdate) as weekday,
money
from tablenamne
wzh1215 2003-12-17
  • 打赏
  • 举报
回复
楼上的弓虽!
zjcxc 元老 2003-12-17
  • 打赏
  • 举报
回复
--下面是例子:
declare @t table(appDate datetime,[money] int)
insert into @t
select '2003-10-22 00:00:00',400
union all select '2003-10-22 12:30:00',600
union all select '2003-10-23 00:00:00',2000

--查询
select [date],[weekday]=datename(weekday,[date]),[money]
from(
select [date]=convert(varchar(10),appdate,120),[money]=sum([money])
from @t group by convert(varchar(10),appdate,120)
) a

/*--测试结果
date weekday money
---------- ------------------------------ -----------
2003-10-22 星期三 1000
2003-10-23 星期四 2000

(所影响的行数为 2 行)
--*/
zjcxc 元老 2003-12-17
  • 打赏
  • 举报
回复
--上面的再改一下:
select [date],[weekday]=datename(weekday,[date]),[money]
from(
select [date]=convert(varchar(10),appdate,120),[money]=sum([money])
from 表 group by convert(varchar(10),appdate,120)
) a
zjcxc 元老 2003-12-17
  • 打赏
  • 举报
回复
select [date],[weekday]=datename(weekday,datediff(day,-1,[date]),[money]
from(
select [date]=convert(varchar(10),appdate,120),[money]=sum([money])
from 表 group by convert(varchar(10),appdate,120)
) a
vc_human 2003-12-17
  • 打赏
  • 举报
回复
SELECT Convert(Char(10),appDate,20) as Day,
Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END as weekday,
sum(money)
from table
group by
Convert(Char(10),appDate,20) ,
Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END
wzh1215 2003-12-17
  • 打赏
  • 举报
回复
SELECT Convert(Char(10),appDate,20) as Day,
max(Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END) as weekday,
sum(money)
from table
group by Convert(Char(10),appDate,20)
wzh1215 2003-12-17
  • 打赏
  • 举报
回复
select * from
(
SELECT Convert(Char(10),appDate,20) as Day,
Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END as weekday,
sum(money)
from table
) temp
group by Day,weekday
txlicenhe 2003-12-17
  • 打赏
  • 举报
回复
SELECT Convert(Char(10),appDate,20) as Day,
Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END as weekday,
sum(money)
from table
group by
Convert(Char(10),appDate,20) ,
Case (DateDiff(day,'1999-09-12',appDate)%7) WHEN 1 THEN '星期一' WHEN 2 THEN '星期二' WHEN 3 THEN '星期三'
WHEN 4 THEN '星期四' WHEN 5 THEN '星期五' WHEN 6 THEN '星期六' WHEN 0 THEN '星期日' END

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

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