求sql语句

[sdjnjdxc]

group by 有一个原则,就是 select 后面的所有列中,没有使用聚合函数的列,必须出现在 group by 后面
----------------------
sql语句如下
csql='select 结论,关联号,姓名,单位,sum(指标) as 指标, sum(实际) as 实际,sum(差额) as 差额 ,sum(计数) as 计数 from main where 差额>=0 group by 关联号 into dbf mydbf'

分组是用关联号，进行求和，结果中还想保留姓名、单位、结论等字符型的字段，怎么办？

[sdjnjdxc]

8.0与9.0差距还不小啊 形如：select 关联号,姓名,单位,sum(工资),sum(奖金) from tt group by 关联号,姓名,单位 姓名，单位放在group by 后面。只是语法要求吗？对结果有没有影响？

[sdjnjdxc]

工资总额>1000 不是针对分组的，而是统计完成后再过滤符合这个条件 的

[WWWWA]

Having 应该可以 select * from ( Select 关联号,姓名,单位,Sum(工资) As 工资总额,Sum(奖金) As 奖金总额 From tt Group By 关联号) a where 工资总额>1000

[sdjnjdxc]

IF thisform.optiongroup3.Value=1 csql='select 结论,关联号,姓名,单位,sum(指标) as 指标, sum(实际) as 实际 sum(差额) as 差额 ,sum(计数) as 计数 from main where 日期>=d2 and 日期<=d3 group by 关联号,结论,姓名,单位 into dbf mydbf' endif *完成 IF thisform.optiongroup3.Value=2 csql='select * from (select 结论,关联号,姓名,单位,sum(指标) as 指标, sum(实际) as 实际,sum(差额) as 差额 ,sum(计数) as 计数 from main where 日期>=d2 and 日期<=d3 group by 关联号,结论,姓名,单位 ) a where 差额>=0 into dbf mydbf' ENDIF *未完成 IF thisform.optiongroup3.Value=3 csql='select * from (select 结论,关联号,姓名,单位,sum(指标) as 指标, sum(实际) as 实际,sum(差额) as 差额 ,sum(计数) as 计数 from main where 日期>=d2 and 日期<=d3 group by 关联号,结论,姓名,单位) a where 差额<0 into dbf mydbf' ENDIF thisform.grid1.RecordSourceType=4 thisform.grid1.RecordSource=csql thisform.grid1.Refresh d2,d3是日期值 这样写，一条记录也没有过滤出来

[sdjnjdxc]

上面的代码第一句能得出正确结果，后两条一条记录也没有过滤出来

[WWWWA]

你的SQL语句中有INTO
&CSQL

thisform.grid1.RecordSourceType=1
thisform.grid1.RecordSource='MYDBF'
thisform.grid1.Refresh

[sdjnjdxc]

后两条语句好象根本没有执行

[sdjnjdxc]

mydbf.dbf中没有记录，实际情况应该有记录的

[sdjnjdxc]

select * from (select 结论,关联号,姓名,单位,sum(指标) as 指标, sum(实际) as 实际,sum(差额) as 差额 ,sum(计数) as 计数 from main group by 关联号,结论,姓名,单位 ) a where 差额>=0 这样运行，系统提示语法错误

[十豆三]

你试一下这个例子：

Create Cursor t1 (a1 i,a2 c(10),a3 i)
Insert Into t1 Values (1,'a',100)
Insert Into t1 Values (1,'a',1000)
Insert Into t1 Values (1,'b',200)
Insert Into t1 Values (2,'a',900)
Sys(3099,70)  	&& vfp9.0
Select a1,a2,Sum(a3) As a3hj From t1 Group By a1,a2
Select a1,a2,Sum(a3) As a3hj From t1 Group By a1
Select a1,a2,Sum(a3) As a3hj From t1 Group By a1,a2 Having a3hj>1000
Select a1,a2,Sum(a3) As a3hj From t1 Group By a1 Having a3hj>1000

[十豆三]

引用 10 楼 sdjnjdxc 的回复:

8.0与9.0差距还不小啊 形如：select 关联号,姓名,单位,sum(工资),sum(奖金) from tt group by 关联号,姓名,单位 姓名，单位放在group by 后面。只是语法要求吗？对结果有没有影响？

可能会有影响，如：关联号是一个，但姓名由于输入等原因造成同音不同字，就变成2个不同的记录了。 这个如果你要求带上 姓名,单位，但只按 关联号 分组，vfp9.0运行此语句前加一句 Sys(3099,70) 就可以这样了：select 关联号,姓名,单位,sum(工资),sum(奖金) from tt group by 关联号

[sdjnjdxc]

还存一个问题， sum(工资) as 工资总额, 这个“工资总额”不能出现在where 语句中，否则会出现“不能识别的...” 但是确实需要根据“工资总额”不同而查出不同的结果 比如：where 工资总额>1000

[十豆三]

分组条件，应该这样：

Sys(3099,70)  	&& vfp9.0
Select 关联号,姓名,单位,Sum(工资) As 工资总额,Sum(奖金) As 奖金总额 From tt Group By 关联号 Having 工资总额>1000

[sdjnjdxc]

thisform.grid1.RecordSourceType=4 thisform.grid1.RecordSource=csql thisform.grid1.Refresh 是不是不支持SQL语句嵌套？

[十豆三]

我的习惯： thisform.grid1.RecordSourceType=1 打开表或 SQL 语句生成表 thisform.grid1.RecordSource='表名' thisform.grid1.Refresh

[sdjnjdxc]

我变通了一下，先用 select 结论,关联号,姓名,单位,sum(指标) as 指标, sum(实际) as 实际,sum(差额) as 差额 ,sum(计数) as 计数 from main group by 关联号,结论,姓名,单位 into dbf my 再 csql='select * from my where 差额>=0 into dbf mydbf' thisform.grid1.RecordSourceType=4 thisform.grid1.RecordSource=csql thisform.grid1.Refresh 数据量不大，还能接受。呵呵 关键是纠结于：通过sum()计算的结果能不能用在where条件中。

[xuegaopeng]

第一.我不懂的是既然group by 关联号，那每一个关联号对应的姓名和单位是否唯一，如果唯一，那就一起写在group by后面，如果不唯一，那要怎样显示，是要显示哪个姓名？ 第二.建议你先查询出关联号，在用关联号查询其他信息 第二.使用MAX试试

[十豆三]

一句就可以： Thisform.grid1.RecordSource=Null Select 结论,关联号,姓名,单位,Sum(指标) As 指标合计,Sum(实际) As 实际合计,Sum(差额) As 差额合计,Sum(计数) As 计数合计 Into Table my From Main Group By 关联号,结论,姓名,单位 Having 差额合计>=0 Thisform.grid1.RecordSourceType=1 Thisform.grid1.RecordSource='my' Thisform.grid1.Refresh

[sdjnjdxc]

引用 27 楼 apple_8180 的回复:

一句就可以： Thisform.grid1.RecordSource=Null Select 结论,关联号,姓名,单位,Sum(指标) As 指标合计,Sum(实际) As 实际合计,Sum(差额) As 差额合计,Sum(计数) As 计数合计 Into Table my From Main Group By 关联号,结论,姓名,单位 Having 差额合计>=0 Thisform.gri……

上面的语句是对的，但我的还有一些条件：日期>=d2 and 日期<=d3 日期是字段名 d2,d3是日期型的值 一加上这个条件上面的语句就出错

[十豆三]

Select 结论,关联号,姓名,单位,Sum(指标) As 指标合计,Sum(实际) As 实际合计,Sum(差额) As 差额合计,Sum(计数) As 计数合计 Into Table my From Main Where 日期>=d2 and 日期<=d3 Group By 关联号,结论,姓名,单位 Having 差额合计>=0