格式如下:
Item Color QtySum Item Color QtySum
Chair Blue 50 Chair Blue 40
Chair Blue 60 Chair Blue 30
Chair 小计 180
Chair RED 20 Chair RED 30
Chair RED 40 Chair RED 60
Chair 小计 150
Chair 合计 330
Item Color QtySum Item Color QtySum
Chair Blue 50 Chair Blue 40
Chair Blue 60 Chair Blue 30
Chair 小计 180
Chair RED 20 Chair RED 30
Chair RED 40 Chair RED 60
Chair 小计 150
Chair 合计 330
Chair Blue 50 Chair Blue 40
Chair Blue 60 Chair Blue 30
Chair 小计 180
Chair RED 20 Chair RED 30
Chair RED 40 Chair RED 60
Chair 小计 150
Chair 合计 330
1、将数据如何这样同行显示?
2、小计、合计怎么解决?
如果想SELECT這樣的結果或者DBGRID也實現不了.
查詢分析器可以PRINT
试一吧:
select Item, Color, Qtysum from(
select Item, Color, Qtysum=sum(Qtysum), s1=0, s2=Item, s3=0 from table
Uuion All
select Item, '小计', Qtysum=sum(qtysum), s1=0, s2=Item, s3=1 from table Group by Item, Color
Union All
select Item, '合计', Qtysum=sum(Qtysum), s1=1, s2='', s3=1 from table) a
Order by s1,s2,s3
Item varchar(10)
Color varchar(10)
QtySum float想得到的查询(报表)结果是:Item Color QtySum Item Color QtySum
Chair Blue 50 Chair Blue 40
Chair Blue 60 Chair Blue 30
Chair 小计 180
Chair RED 20 Chair RED 30
Chair RED 40 Chair RED 60
Chair 小计 150
Chair 合计 330其实我基本上已经弄出来了,如果没有小计的情况已经胜利完工了,但是加小计就出现了问题。
以颜色的不同作为小计,如果数据是偶数条就正常,如果是奇数条就会加一条其它颜色的数据。
我一直没有查出原因,所以希望大家指点:)))
set @i=''
set @c=''
set @td=0
set @tsum=0
set @lc=''
open table1_cursorFetch next from table1_cursor into @i,@c,@d1
while @@FEtch_status=0
begin
if @lc='' or @lc=@c
set @td=@td+@d1
else
insert into #temp1 values(@i,'ddd',@td)
set @lc=@c
set @tsum=@tsum+@d1
insert #temp1 values(@i,@c,@d1)
Fetch next from table1_cursor into @i,@c,@d1
end
insert into #temp1 values(@i,'total',@tsum)
select * from #temp1
close table1_cursor
乱写的,你可以试试
MM(是不是?),看你可怜样子,我都动了怜悯之心,于是我倾我所能,折腾一番,终于出来!算了,分随便你给吧。数据结构:
Item varchar(10)
Color varchar(10)
QtySum float
ID int //增加辅助标识 ID 字段原数据表内容:Item Color qtysum id
====== ===== ===== =====
Chair Blue 50 1
Chair Blue 40 2
Chair Blue 60 3
Chair Blue 30 4Chair RED 20 5
Chair RED 30 6
Chair RED 40 7
Chair RED 60 8
===================================select item1, color1, qtysum1, item2, color2, qtysum2 from (
select a.item as item1, a.Color as Color1, a.qtysum as qtysum1,
b.item as item2, b.Color as Color2, b.qtysum as qtysum2,
s1=a.item, s2=a.color, s3=0 from (select * from @t where id%2<>0) a
inner join (select * from @t where id%2=0) b ON b.id=a.id+1
union all
select max(item), '小计', qtysum=sum(qtysum), null, null, null,
s1=max(item), s2=max(color), s3=1 from @t group by item,color
union all
select max(item), '合计', qtysum=sum(qtysum), null, null, null,
s1=max(item), s2=max(color), s3=2 from @t ) tt order by s1,s2,s3
效果如下:
Chair Blue 50 Chair Blue 40
Chair Blue 60 Chair Blue 30
Chair 小计 180 NULL NULL NULL
Chair RED 20 Chair RED 30
Chair RED 40 Chair RED 60
Chair 小计 150 NULL NULL NULL
Chair 合计 330 NULL NULL NULL