帮帮忙，把下列SQL语句转成SQL SERVER可识别的 (100分)

涛

涛涛小财主

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-03

• #1

'select *
from (select ATM网点名,穿墙或大堂,网点名称,d14022.终端号,sum(业务量) as 业务量,sum(吐钞量) as 吐钞量
from (select * from atmmx right outer join d14022 on atmmx.终端号= d14022.终端号 where d14022.日期>=#'+FormatDateTime('yyyy-mm-dd',RzDateTimePicker1.DateTime)+'# and d14022.日期<=#'+FormatDateTime('yyyy-mm-dd',RzDateTimePicker2.DateTime)+'# and d14022.终端号<> '+''''+'总 计'+''''+ 'order by d14022.日期)
group by 网点名称,d14022.终端号,ATM网点名,穿墙或大堂)
order by 业务量 desc'

以上代码在使用ACCESS数据库时正常，SQL server下如何写？？

 

A

another_benlei

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-03

• #2

'select ATM网点名,穿墙或大堂,网点名称,d14022.终端号,sum(业务量) as 业务量,sum(吐钞量) as 吐钞量
from (select * from atmmx right outer join d14022 on atmmx.终端号= d14022.终端号
where d14022.日期>='''+FormatDateTime('yyyy-mm-dd',RzDateTimePicker1.DateTime)+
''' and d14022.日期<='''+FormatDateTime('yyyy-mm-dd',RzDateTimePicker2.DateTime)+
''' and d14022.终端号<> ''总 计''order by d14022.日期)
group by 网点名称,d14022.终端号,ATM网点名,穿墙或大堂 order by 业务量 desc'

 

C

chenxz

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-03

• #3

晕~
sql应该都是一个标准，可以通用的

 

涛

涛涛小财主

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-03

• #4

another_benlei的通不过，没这么简单，
提示'除非同时使用TOP，否则ORDER BY子句在视图和子查询中无效'

 

B

book523

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-04

• #5

'select ATM网点名,穿墙或大堂,网点名称,d14022.终端号,sum(业务量) as 业务量,sum(吐钞量) as 吐钞量
from (select * from atmmx right outer join d14022 on atmmx.终端号= d14022.终端号
where d14022.日期>='''+FormatDateTime('yyyy-mm-dd',RzDateTimePicker1.DateTime)+
''' and d14022.日期<='''+FormatDateTime('yyyy-mm-dd',RzDateTimePicker2.DateTime)+
''' and d14022.终端号<> ''总 计''order by d14022.日期) T1
group by 网点名称,d14022.终端号,ATM网点名,穿墙或大堂 order by sum(业务量) desc'

 

E

eagle_l

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-04

• #6

用存储过程
@date ....
@total....
select * into #tmp from atmmx right outer join d14022 on atmmx.终端号= d14022.终端号 where d14022.日期>=#'+FormatDateTime('yyyy-mm-dd',RzDateTimePicker1.DateTime)+'# and d14022.日期<=@date and d14022.终端号<> @total
select ATM网点名,穿墙或大堂,网点名称,d14022.终端号,sum(业务量) as 业务量,sum(吐钞量) as 吐钞量 from #tmp
group by 网点名称,d14022.终端号,ATM网点名,穿墙或大堂
order by 业务量 desc

 

E

eagle_l

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-04

• #7

改过

用存储过程
@date1 ....
@date2
@total....
select * into #tmp from atmmx right outer join d14022 on atmmx.终端号= d14022.终端号 where d14022.日期>=@date1 and d14022.日期<=@date2 and d14022.终端号<> @total
select ATM网点名,穿墙或大堂,网点名称,d14022.终端号,sum(业务量) as 业务量,sum(吐钞量) as 吐钞量 from #tmp
group by 网点名称,d14022.终端号,ATM网点名,穿墙或大堂
order by 业务量 desc

 

涛

涛涛小财主

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-06-04

• #8

我自己已经解决了，谢谢各位的热心。散分了。