有这样的表kk
inv(vch 15) je(money) zy (vch 100) id (int)'这是结构
----------------------------------------
inv01 100 aaaaa 1
inv01 200 bbbbbb 2
inv02 300 ccccc 3
inv02 400 ddddd 4
_____________________________________ '以上是记录
现在要各个inv的合计金额,我用这样的语句。
select inv,sum(je) from kk group by inv order by inv
这是成功的。
接下来的问题我解决不了,希望大虾帮助。
那就是要把zy这个字段也放到查询中来。
规则是这样的,如果某个inv的zy只有一种值,就采用这种值,如果超过一种
就采用最早的那个值。
这个不知道怎么写。
我原来常识用
select inv,sum(je),zy from kk group by inv order by inv
提示语法错误
用select inv,sum(je),zy from kk group by inv,zy order by inv
又不是需要的结果.............
晕啊,怎么办啊?
inv(vch 15) je(money) zy (vch 100) id (int)'这是结构
----------------------------------------
inv01 100 aaaaa 1
inv01 200 bbbbbb 2
inv02 300 ccccc 3
inv02 400 ddddd 4
_____________________________________ '以上是记录
现在要各个inv的合计金额,我用这样的语句。
select inv,sum(je) from kk group by inv order by inv
这是成功的。
接下来的问题我解决不了,希望大虾帮助。
那就是要把zy这个字段也放到查询中来。
规则是这样的,如果某个inv的zy只有一种值,就采用这种值,如果超过一种
就采用最早的那个值。
这个不知道怎么写。
我原来常识用
select inv,sum(je),zy from kk group by inv order by inv
提示语法错误
用select inv,sum(je),zy from kk group by inv,zy order by inv
又不是需要的结果.............
晕啊,怎么办啊?
inv,
sum(je) as je,
min(zy) as zy
from kk
group by inv
order by inv
inv,
sum(je) as je,
min(zy) as zy --不论一种还是多种,都取最小即可
from kk
group by inv
order by inv
就采用最早的那个值。
应该是指ID最小的那个吧?select m.inv,m.je,kk.zy from
(
select inv,sum(je) je , min(id) id from tb group by inv
) m,kk
where m.inv = tb.inv and m.id = kk.id
select 'inv01',100,'aaaaa' , 1 union all
select 'inv01',200,'bbbbbb' , 2 union all
select 'inv02',300,'ccccc' , 3 union all
select 'inv02',400,'ddddd' , 4select
inv,
sum(je) as je,
(select top 1 zy from #t where inv=T.inv) as zy
from #t as T
group by inv
order by invdrop table #t
drop table kk
gocreate table kk(inv varchar(10),je money,zy varchar(10),id int)
insert into kk(inv,je,zy,id) values('inv01', 100, 'aaaaa' , 1)
insert into kk(inv,je,zy,id) values('inv01', 200, 'bbbbbb', 2)
insert into kk(inv,je,zy,id) values('inv02', 300, 'ccccc' , 3)
insert into kk(inv,je,zy,id) values('inv02', 400, 'ddddd' , 4)select m.inv,m.je,kk.zy from
(
select inv,sum(je) je , min(id) id from kk group by inv
) m,kk
where m.inv = kk.inv and m.id = kk.iddrop table kk/*
inv je zy
---------- --------------------- ----------
inv01 300.0000 aaaaa
inv02 700.0000 ccccc(所影响的行数为 2 行)*/
inv,
sum(je) as je,
(select top 1 zy from kk where inv=T.inv) as zy
from kk as T
group by inv
order by inv
inv,
sum(je) as je,
(select top 1 zy from #t where inv=T.inv and id=min(T.ID)) as zy
from #t as T
group by inv
order by inv
inv,
sum(je) as je,
(select top 1 zy from kk where inv=T.inv and id=min(T.ID)) as zy
from kk as T
group by inv
order by inv
select inv,sum(je),(select top 1 zy from kk a where a.inv=kk.inv) from kk group by inv order by inv其实如果你有时间字段,才能说可以求出来最早出现的zy的值。