菜鸟提问简单的sql 要求效率高 ( 积分: 50 )

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #21

我用的数据库是ACCESS
count(*) icount 之间操作符丢失

 

P

psynet123

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #22

那你加個as 阿,count(*) as icount

 

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #23

access报错阿：
试图执行的查询中不包含作为合计函数的一部分的特定表达式“sname”

 

P

psynet123

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #24

大哥 sname 是你學生名 字段啊

 

W

willing66

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #25

select sname
from aa
where cj='优'
group by sname, cj
having count(*) >= 次数,
因为在一个期间内,你的评的次就是确定的,
比如说:
1~3就三次

select sname
from aa
where cj='优'
group by sname, cj
having count(*) >= 3
这样的效率肯定比较高.先做了筛选,分组的工作就会少很多.

 

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #26

用的就是字段阿
access报错阿：
试图执行的查询中不包含作为合计函数的一部分的特定表达式“学生”

 

P

psynet123

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #27

select sname from aa
where cj='優'
group by sname
having count(*)=(select max(icount) from (select count(*) icount from aa group by stime) as a)
借鑑上面的朋友,確實應該這樣寫

 

P

psynet123

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #28

sql2000是通過的,至於其他,你應該看看語法上的差異

 

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #29

[]我菜阿
在access中报错

 

P

psynet123

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #30

我在access也測試過了,並沒有報錯
elect sname from aa
where cj='優'
group by sname
having count(*)=(select max(icount) from (select count(*) as icount from aa group by stime) as a)

 

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #31

select 学生 from aa
having count(*)>1
都报错
试图执行的查询中不包含作为合计函数的一部分的特定表达式“学生”

晕死了

 

P

psynet123

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #32

這樣肯定錯,你根本就不按上面的語法寫,上面的語法再access測試通過的

 

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #33

我就是按照上面写的阿
报错
一步步简化勒 都报错 只要有having count(*)

 

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #34

没有合适答案吗？？

 

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #35

明白维什马报错了 我要的是整个纪录
而select sname from aa
where cj='優'
group by sname
having count(*)=(select max(icount) from (select count(*) as icount from aa group by stime) as a)
只给出了sname 而 sname是分组条件 这样的话只能查询 这个分组的集合如sum(stim) count(cj)等
这个查询不能查出每条记录

 

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #36

我要查询的是符合条件的整个行

 

H

hitman007260

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #37

可以这样写
select * from aa where sname in(
select sname from aa
where cj='優'
group by sname
having count(*)=(select max(icount) from (select count(*) as icount from aa group by stime) as a))
但是效率太低
psynet123 你能还能改改吗？？ 改好分就是你的了

 

3

3868474

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #38

这个效率高
select * from aa a where not exists (select * from aa b where b.cj <> '优' and a.sname = b.sname)

 

D

dalmatians

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-14

• #39

select 学生 from score t1 group by 学生, 成绩
having 成绩='优'
and count(*) = (select count(*) from score where 学生=t1.学生)

 

P

psynet123

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2007-02-15

• #40

昨天上不了網,你的要求裡面說的是查出符合條件的學生,而不是整條紀錄
如果要查出整條紀錄
select * from aa where sname in(
select sname from aa
where cj='優'
group by sname
having count(*)=(select max(icount) from (select count(*) as icount from aa group by stime) as a))