access里实现查询

hlzyly　发表于：2010-08-19 15:31:31 　

我有一个acess表：表里有一个字段kkk,kkk字段里的内容形式为:aaa;111，现我想实现的对aaa这个进行分类汇总，并按从大到小排序。我的SQL语句是：

select kkk,count(left(kkk,instr(1,kkk,";")-1)) from aaa group by left(kkk,instr(1,kkk,";")-1) order by left(kkk,instr(1,kkk,";")-1)

结果执行的时候出错：

试图执行的查询中不包含作为合计函数一部分的特定表达式'kkk'

Top

煮江品茶发表于：2010-08-19 16:03:51

select kkk,count(*) as 计数 from aaa group by kkk,left(kkk,instr(1,kkk,";")-1) order by left(kkk,instr(1,kkk,";")-1)

Top

hlzyly 发表于：2010-08-19 16:45:46

我只想对left(kkk,instr(1,kkk,";")-1)这个进行分组统计。。。不对kkk,进行分组。因为对KKK分组出来的结果是不正确的。

Top

竹笛发表于：2010-08-19 16:47:07

上传你的示例。

Top

煮江品茶发表于：2010-08-19 16:48:16

select count(*) as 计数 from aaa group by left(kkk,instr(1,kkk,";")-1) order by left(kkk,instr(1,kkk,";")-1)

Top

红尘如烟发表于：2010-08-19 16:54:17

SELECT Left([kkk],InStr(1,[kkk],";")-1) AS 分类, Count(1) AS 计数
FROM aaa
GROUP BY Left([kkk],InStr(1,[kkk],";")-1)
ORDER BY Left([kkk],InStr(1,[kkk],";")-1);

Top

竹笛发表于：2010-08-19 17:52:34

SELECT IIf(Val(InStr([监视对象],";"))=0,[监视对象],Left([监视对象],Val(InStr([监视对象],";")-1))) AS B, Sum(1) AS 次数
FROM kkk
GROUP BY IIf(Val(InStr([监视对象],";"))=0,[监视对象],Left([监视对象],Val(InStr([监视对象],";")-1)))
ORDER BY Sum(1) DESC;

Top

总记录：6篇页次：1/1 9 1 :

Access交流中心

access里实现查询