select
t.箱子号,
t.物品 ,
t.物品个数,
(case
when exists(select
1
from
表A
where
箱子号=t.箱子号
and
箱子尺寸=t.箱子尺寸
and
物品<t.物品) then ''
else t.箱子尺寸
end)
from
表A t
order by
t.箱子号,t.物品
t.箱子号,
t.物品 ,
t.物品个数,
(case
when exists(select
1
from
表A
where
箱子号=t.箱子号
and
箱子尺寸=t.箱子尺寸
and
物品<t.物品) then ''
else t.箱子尺寸
end)
from
表A t
order by
t.箱子号,t.物品
into #tmp
from tableselect 箱子号,
物品,
物品个数,
箱子尺寸 = case when rowid =(select min(rowid) from #tmp where 箱子号=a.箱子号 ) then 箱子尺寸 else '' end
from table a
into #tmp
from tableselect 箱子号,
物品,
物品个数,
箱子尺寸 = case rowid when
(select min(rowid)
from #tmp
where 箱子号=a.箱子号
) then 箱子尺寸 else '' end
from table a
a.物品,
a.物品个数,
b.箱子尺寸
from 表A a left join (select 箱子号,箱子尺寸,min(物品) as 物品
from 表A
group by 箱子号,箱子尺寸) b
on a.箱子号=b.箱子号 and a.物品=b.物品--结果------------------------------------------------箱子号 物品 物品个数 箱子尺寸
---------- ---------- ----------- ----------------------------------------------------------------------------------------------------
01 a 10 10*20*10
01 b 5 NULL
01 c 2 NULL
02 d 20 NULL
02 3 3 20*30*50(所影响的行数为 5 行)
(case when(select top 1 物品 from A where 箱子号=t.箱子号)=物品 then 箱子尺寸 else '' end) as 箱子尺寸
from A t
order by 箱子号
结帖了。再次谢谢大家。