求sql语句(100分)

G

gxw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-13

• #1

想根据s1Value和s2value求出其对应的排名s1和s2，效果如下。最好一句update能搞定[]。数据库是access

表名：aaa

code s1Value s2value s1 s2
-----------------------------------
a1 150.00 60 3 2
a2 125.00 80 4 1
a3 175.00 80 2 1
a4 175.00 10 2 4
a5 200.00 30 1 3

 

P

power255

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-13

• #2

access又没有像Oracle有Number,sql server有 identity(int , 1,1)的功能。一句update搞定你就别想了。
只能是先建临时表，里面搞个自增字段，之后再通过临时表更新。

 

G

gxw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-13

• #3

目前暂时order by后配合循环实现。好不郁闷。。。
另外，access也是有id自增键的，不过这里好象用不上？

 

P

power255

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-14

• #4

1、建临时表
CREATE TABLE Tmp20081233233 (
OrderId AUTOINCREMENT (1, 1),
SValue string)
2、把数据放在临时表
insert into Tmp20081233233 (SValue)
select distinct s1Value from aaa order by s1Value
3、更新
update aaa a inner join Tmp20081233233 b on A.s1Value = B.SValue
set A.s1 = B.OrderId
4、删除临时表
drop table Tmp20081233233

如果你有办法把select distinct s1Value from aaa order by s1Value多加一个自增id字段就可以用一个update就能搞定。

 

S

shadowpj

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-14

• #5

嘿嘿！不晓得是不是对的。但是大概测试没错。你多做点特殊数据来测试。

update aaaa set s1=
(select count(*) from (select distinct s1value from aaaa) a where a.s1value>=b.s1value),

s2=
(select count(*) from (select distinct s2value from aaaa) c where c.s2value>=b.s2value)

from aaaa b

 

L

lanyaoshi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-14

• #6

shadowpj的写法蛮有启发的，他的语句应该只能在SQL Server下运行吧，我的写法如下：
SELECT a.s1value AS v1, Count(*) AS s1
FROM [select distinct s1value from aaa]. AS a, [select distinct s1value from aaa]. AS b
WHERE (((b.s1value)>=[a].[s1value]))
GROUP BY a.s1value;
上面这个查询可以得出s1值，将这个查询保存为ccc
如下面的SQL可以进行更新了
UPDATE aaa SET aaa.s1 = dsum("s1","ccc","v1=" & aaa.s1value);

这所以这麻烦，是因为ACCESS的SQL功能差一些呀

 

L

lanyaoshi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-14

• #7

如果这样写，会出现错误："操作必须使用一个可更新的查询"
update aaa,(select a.s1value as v1,count(*) as s1 from (select distinct s1value from aaa) a,(select distinct s1value from aaa) b where b.s1value>=a.s1value group by a.s1value) c set aaa.s1=c.s1 where aaa.s1value=c.v1

 

P

power255

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-14

• #8

to：shadowpj,lanyaoshi
这样写会有一个问题,如果有相同的s1Value就会出错，例如
a1就会得到4而不是3了。

 

L

lanyaoshi

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-14

• #9

我按你提供的数据来测试，没有问题呀，a3，a4的s1Value都是175.00，是相同的，都是2呀。

 

P

power255

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-14

• #10

搞错了，lanyaoshi是正确的。

 

G

gxw

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2008-03-14

• #11

谢谢各位。最终使用了临时表分步处理,大概思路是这样：

Creat table temp_A(id int auto_increment,vv float default 0);
insert into temp_A(vv) select distinct s1Value from aaa order by s1Value desc;
update aaa a,temp_A b set a.s1Value=b.id where a.s1Value=b.vv;

drop table temp_A;
Creat table temp_A(id int auto_increment,vv float default 0);
insert into temp_A(vv) select distinct s2Value from aaa order by s2Value desc;
update aaa a,temp_A b set a.s2Value=b.id where a.s2Value=b.vv;