之前的帖子
http://topic.csdn.net/u/20110412/19/c9282db2-36d8-4589-9f62-140550cb2b3d.html?50425
http://topic.csdn.net/u/20110328/17/a50d7782-b44d-43d6-bb6c-d13f44e1d833.html
数据表类型:测试数据下载地址:http://adocc.ys168.com/note/fd.htm?http://ys-B.ys168.com/?测试数据.rar_5l0bs2b9d6e4bkks2btl1bsqlrl0cppm7bk5bsqlrjt0bu14z97f14z
用下面的这段SQL语句,排序出现问题
在加上一个条件:{算下面这个时间的时候,要把上面那个时间大于18hour的删除,该怎么改!}
select
ltrim(isnull(DATEPART(hh,a.过帐时间),f.time)) as 发货过帐时间,
ISNULL(ltrim(sum(体积)),'') as 汇总体积
from
f full join sell a
on
DATEPART(hh,a.过帐时间)=f.time
group by
DATEPART(hh,过帐时间),f.time
union all
select '过帐时间','体积汇总'
union all
select
ltrim(isnull(DATEPART(hh,a.接收时间),f.time)) as 接收时间,
ISNULL(ltrim(sum(体积)),'')
from
f full join sell a
on
DATEPART(hh,a.接收时间)=f.time
group by
DATEPART(hh,接收时间),f.time当问题解决了,这三个贴子马上结贴!谢谢各位
http://topic.csdn.net/u/20110412/19/c9282db2-36d8-4589-9f62-140550cb2b3d.html?50425
http://topic.csdn.net/u/20110328/17/a50d7782-b44d-43d6-bb6c-d13f44e1d833.html
数据表类型:测试数据下载地址:http://adocc.ys168.com/note/fd.htm?http://ys-B.ys168.com/?测试数据.rar_5l0bs2b9d6e4bkks2btl1bsqlrl0cppm7bk5bsqlrjt0bu14z97f14z
用下面的这段SQL语句,排序出现问题
在加上一个条件:{算下面这个时间的时候,要把上面那个时间大于18hour的删除,该怎么改!}
select
ltrim(isnull(DATEPART(hh,a.过帐时间),f.time)) as 发货过帐时间,
ISNULL(ltrim(sum(体积)),'') as 汇总体积
from
f full join sell a
on
DATEPART(hh,a.过帐时间)=f.time
group by
DATEPART(hh,过帐时间),f.time
union all
select '过帐时间','体积汇总'
union all
select
ltrim(isnull(DATEPART(hh,a.接收时间),f.time)) as 接收时间,
ISNULL(ltrim(sum(体积)),'')
from
f full join sell a
on
DATEPART(hh,a.接收时间)=f.time
group by
DATEPART(hh,接收时间),f.time当问题解决了,这三个贴子马上结贴!谢谢各位
ltrim(isnull(DATEPART(hh,a.过帐时间),f.time)) as 发货过帐时间,
ISNULL(ltrim(sum(体积)),'') as 汇总体积
from
f full join sell a
on
DATEPART(hh,a.过帐时间)=f.time
group by
DATEPART(hh,过帐时间),f.time
union all
select '过帐时间','体积汇总'
union all
select
ltrim(isnull(DATEPART(hh,a.接收时间),f.time)) as 接收时间,
ISNULL(ltrim(sum(体积)),'')
from
f full join sell a
on
DATEPART(hh,a.接收时间)=f.time
where datediff(hh,发货时间,过账时间)<18----
group by
DATEPART(hh,接收时间),f.time
消息 207,级别 16,状态 1,第 20 行
列名 '发货时间' 无效。
消息 207,级别 16,状态 1,第 20 行
列名 '过账时间' 无效。
把这句给注释掉,排序也是乱七八糟的,希望色狼哥哥帮忙看看
where datediff(hh,发货时间,过账时间)<18----
在应使用条件的上下文(在 'group' 附近)中指定了非布尔类型的表达式。排序还是没有得到解决!
ltrim(isnull(DATEPART(hh,a.过帐时间),f.time)) as 发货过帐时间,
ISNULL(ltrim(sum(体积)),'') as 汇总体积
from
f full join sell a
on
DATEPART(hh,a.过帐时间)=f.time
group by
DATEPART(hh,过帐时间),f.time
union all
select '过帐时间','体积汇总'
union all
select
ltrim(isnull(DATEPART(hh,a.接收时间),f.time)) as 接收时间,
ISNULL(ltrim(sum(体积)),'')
from
f full join sell a
on
DATEPART(hh,a.接收时间)=f.time
group by
DATEPART(hh,接收时间),f.time
order by 1
并不一定非要在sql语句中家orderby,你可以把楼上的语句做成视图或者放到临时表中,然后order by。这样简单明了,出错页容易排查
select
isnull(DATEPART(hh,a.过帐时间),f.time) as 过帐时间,
ISNULL(sum(体积),0)
from
f full join sellss a
on
DATEPART(hh,a.过帐时间)=f.time
group by
DATEPART(hh,过帐时间),f.time
order by f.time
select
isnull(DATEPART(hh,a.接收时间),f.time) as 接收时间,
ISNULL(sum(体积),0)
from
f full join sellss a
on
DATEPART(hh,a.接收时间)=f.time
group by
DATEPART(hh,接收时间),f.time
order by f.time
用上面这代码排序正常了,还有一个问题没有解决了就是下面这个代码加上老是出错,请大大帮忙看看where datediff(hh,a.接收时间,a.过账时间)<18----
--这样不行?
select
isnull(DATEPART(hh,a.过帐时间),f.time) as 过帐时间,
ISNULL(sum(体积),0)
from
f full join sellss a
on
DATEPART(hh,a.过帐时间)=f.time
group by
DATEPART(hh,过帐时间),f.time
order by f.time
select
isnull(DATEPART(hh,a.接收时间),f.time) as 接收时间,
ISNULL(sum(体积),0)
from
f full join sellss a
on
DATEPART(hh,a.接收时间)=f.time
where datediff(hh,a.接收时间,a.过账时间)<18
group by
DATEPART(hh,接收时间),f.time
order by f.time
--你自己好好看看,学习下,这都是小问题,让别人给写出来,你什么都没学到
select
isnull(DATEPART(hh,a.过帐时间),f.time) as 过帐时间,
ISNULL(sum(体积),0)
from
f full join sellss a
on
DATEPART(hh,a.过帐时间)=f.time
group by
DATEPART(hh,a.过帐时间),f.time
order by f.time
select
isnull(DATEPART(hh,a.接收时间),f.time) as 接收时间,
ISNULL(sum(体积),0)
from
f full join sellss a
on
DATEPART(hh,a.接收时间)=f.time
where datediff(hh,a.接收时间,a.过账时间)<18
group by
DATEPART(hh,a.接收时间),f.time
order by f.time
where convert(varchar(2),SCRQ,108)<18