Access下的SQL语句怎么写 (100分)

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-04

• #1

1.access 支持的时间函数都有什么，怎么好象只有year(),month(),day()好用
2.下面的语句怎么实现
select a.rmpwr,a.buypwr,yd=case
when b.bdl>0 then a.ydl+b.rmpwr-b.bdl
else a.yd-b.yd
end
from
(select mid,rmpwr,buypwr,(buypwr-rmpwr) as yd from main where month(dt)=2) a,
(select mid,rmpwr,buypwr,(buypwr-rmpwr) as yd,bdl from main where month(dt)=1) b
where a.mid=b.mid
case 部分好象access 数据库不认，不知怎么处理

 

P

pcsunflower

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #2

在ACCESS的查询生成器中你可以看到他的所有自带函数

 

E

expect

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #3

ACCESS 不支持 Case

 

A

antic_ant

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #4

分两条语句来写

 

Y

Yong

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #5

select a.rmpwr,a.buypwr,
a.ydl+b.rmpwr-b.bdl as yd
from
(select mid,rmpwr,buypwr,(buypwr-rmpwr) as yd from main where month(dt)=2) a,
(select mid,rmpwr,buypwr,(buypwr-rmpwr) as yd,bdl from main where month(dt)=1) b
where a.mid=b.mid and b.bdl>0
union all
select a.rmpwr,a.buypwr,
a.yd-b.yd as yd
from
(select mid,rmpwr,buypwr,(buypwr-rmpwr) as yd from main where month(dt)=2) a,
(select mid,rmpwr,buypwr,(buypwr-rmpwr) as yd,bdl from main where month(dt)=1) b
where a.mid=b.mid and b.bdl<=0

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #6

上面的语句只有一条结果，好象不行。
希望大家提供函数和方法
(b.bdl<=0) or (b.bdl is null) 可以

 

G

guojun

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #7

Access 有Switch可用，只是我不知道Else怎么处理

值=switch(表达式1为真，返回值1，表达式2为真，返回值2，表达式3为真，返回值3)

例如：字段 Status Int
St=switch(Status=1,'未确认',Status=2,'已确认')

有知道怎么处理默认值的，说说！

 

莫

莫知

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #8

select a.rmpwr,a.buypwr,yd=IIF(b.bdl>0,a.ydl+b.rmpwr-b.bdl,a.yd-b.yd)
from
(select mid,rmpwr,buypwr,(buypwr-rmpwr) as yd from main where month(dt)=2) a,
(select mid,rmpwr,buypwr,(buypwr-rmpwr) as yd,bdl from main where month(dt)=1) b
where a.mid=b.mid

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #9

提示参数不足，期待是1

 

U

ugvanxk

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2002-02-05

• #10

多人接受答案了。