--如果是最表2中最大的表1id的就没有小数。 select a.id,a.name, case when a.id=max(b.aid) then convert(varchar,convert(int,sum(b.sum))) else convert(varchar,sum(b.sum)) end as sum from 表1 a,表2 b where a.id=b.aid group by a.id,a.name
--测试数据 declare @T1 table (id int ,name varchar(5)) declare @T2 table (id int ,T1id int,sum decimal(18,2)) set nocount on insert into @T1 select 1,'a' union all select 2,'a' union all select 3,'a' union all select 4,'a' union all select 5,'a' insert into @T2 select 1,1,20 union all select 2,2,40 union all select 3,1,30 union all select 4,3,50 union all select 5,1,10 union all select 6,1,25/*查询语句*/ select T1.[id],'ss'=(case when exists(select T21.[T1id] from @T2 T21 where T21.[T1id] > T1.[id]) then convert(varchar(10),sum(T2.[sum])) else convert(varchar(10),convert(int,sum(T2.[sum]))) end) from @T1 T1 inner join @T2 T2 on T1.[id] = T2.[T1id] group by T1.[id]/* id ss ----------- ---------- 1 85.00 2 40.00 3 50 */
select a.id,a.name, case when a.id=max(b.aid) then convert(varchar,convert(int,sum(b.sum))) else convert(varchar,sum(b.sum)) end as sum from 表1 a,表2 b where a.id=b.aid group by a.id,a.name
借助 MorningTea(一勺抹茶) 的数据查询如下:select a.id,a.name,'sum'=(select sum([sum]) from t2 where t2.t1id=a.id) from t1 a ,t2 b where a.id=b.t1id group by a.id,a.name
select a.id,a.name,
case when a.id=max(b.aid) then convert(varchar,convert(int,sum(b.sum)))
else convert(varchar,sum(b.sum)) end as sum
from 表1 a,表2 b
where a.id=b.aid
group by a.id,a.name
-》
如果表2中的表1id是本表中最大的那个id。就没有小数。
declare @T1 table (id int ,name varchar(5))
declare @T2 table (id int ,T1id int,sum decimal(18,2))
set nocount on
insert into @T1
select 1,'a' union all
select 2,'a' union all
select 3,'a' union all
select 4,'a' union all
select 5,'a' insert into @T2
select 1,1,20 union all
select 2,2,40 union all
select 3,1,30 union all
select 4,3,50 union all
select 5,1,10 union all
select 6,1,25/*查询语句*/
select T1.[id],'ss'=(case when exists(select T21.[T1id] from @T2 T21 where T21.[T1id] > T1.[id]) then convert(varchar(10),sum(T2.[sum]))
else convert(varchar(10),convert(int,sum(T2.[sum])))
end)
from @T1 T1
inner join
@T2 T2
on T1.[id] = T2.[T1id]
group by T1.[id]/*
id ss
----------- ----------
1 85.00
2 40.00
3 50
*/
case when a.id=max(b.aid) then convert(varchar,convert(int,sum(b.sum)))
else convert(varchar,sum(b.sum)) end as sum
from 表1 a,表2 b
where a.id=b.aid
group by a.id,a.name