access的table表结构如下:
company_id ,value,unit....
内容如下:
company_id ,value , unit
001 25 米
003 130 斤
001 40 米
002 80 克
001 90 米
003 60 克
001 40 米
002 50 克
. .
. .有900多个company_id,要做这样的查询:
计算同一company_id所对应的全部value的和,同按这个和从大到小排序,如果单位统一则显示该单位,如不同则显示“混合”。
如上的排序结果为
company_id value unit
001 195 米
003 190 混合
002 130 克
. .
. .
company_id ,value,unit....
内容如下:
company_id ,value , unit
001 25 米
003 130 斤
001 40 米
002 80 克
001 90 米
003 60 克
001 40 米
002 50 克
. .
. .有900多个company_id,要做这样的查询:
计算同一company_id所对应的全部value的和,同按这个和从大到小排序,如果单位统一则显示该单位,如不同则显示“混合”。
如上的排序结果为
company_id value unit
001 195 米
003 190 混合
002 130 克
. .
. .
(IIf(count(unit)=1,unit,'混合') as unit
from table
group by company_id ,unit
order by company_id
case when max(unit)=min(unit) then max(unit) else '混合' end asunit
from xhh group by company_id
order by company_id company_id value unit
---------- ----------- ----------
001 155 米
002 130 克
003 190 混合(所影响的行数为 3 行)
create table xhh(company_id varchar(10),value int ,unit varchar(10))insert into xhh
select '001',25,'米'
union select '003',130,'斤'
union select'001',40,'米'
union select'002',80,'克'
union select'001',90,'米'
union select'003',60,'克'
union select'001',40,'米'
union select'002',50,'克'
from table a,
(select company_id,decode(unit,aa=1,unit,'混合') unit
from (
select company_id,unit,count(*) aa
from table
group by company_id,unit
)
group by company_id,unit
) b
where a.company_id=b.company_id
group by a.company_id
from table a,
(select company_id,decode(unit,aa=1,unit,'混合') as unit
from (
select company_id,unit,count(*) as aa
from table
group by company_id,unit
)
group by company_id,unit
) b
where a.company_id=b.company_id
group by a.company_id,b.unit
比较有创意,呵呵
(select company_id,sum(value) as value,max(unit) as unit from table group by company_id) a,
(select company_id,sum(value) as value,min(unit) as unit from table group by
company_id) b where a.unit=b.unit) union all ( select distinct a.company_id company_id,a.value value,"混合" unit from
(select company_id,sum(value) as value,max(unit) as unit from table group by company_id) a,
(select company_id,sum(value) as value,min(unit) as unit from table group by
company_id) b where a.unit<>b.unit order by value )
他说的是ACCESS系统根本不是SQL SERVER,要是SQL SERVER就简单多了,
select company_id,sum(value) as value1,
iif(max(unit)=min(unit),max(unit),'混合') as unit
from xhh group by company_id
order by company_id //不过value是关键字作字段名不能通过
iif(max(unit)=min(unit),max(unit), '混合') as unit
from table
group by company_id
order by company_id
…………………………………………加方括号就可以了