select *,IDENTITY(int,1,1) as idxxx into #tmp from table1 where ...select case when a.id=1 then a.a1 when a.id<>1 and a.a1=b.a1 then null else a.a1 end as a1, case when a.id=1 then a.a2 when a.id<>1 and a.a1=b.a1 and a.a2=b.a2 then null else a.a2 end as a2, case when a.id=1 then a.a3 when a.id<>1 and a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3 then null else a.a3 end as a3, a.a4 from #tmp a left join #tmp b on a.idxxx=b.idxxx+1
更正:select *,IDENTITY(int,1,1) as idxxx into #tmp from table1select case when a.idxxx=1 then a.a1 when a.idxxx<>1 and a.a1=b.a1 then null else a.a1 end as a1, case when a.idxxx=1 then a.a2 when a.idxxx<>1 and a.a1=b.a1 and a.a2=b.a2 then null else a.a2 end as a2, case when a.idxxx=1 then a.a3 when a.idxxx<>1 and a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3 then null else a.a3 end as a3, a.a4 from #tmp a left join #tmp b on a.idxxx=b.idxxx+1结果: a1 a2 a3 a4 ----------- ----------- ----------- ----------- 1 1 1 1 NULL NULL NULL 2 NULL NULL 2 1 NULL NULL NULL 2 NULL 2 1 1 NULL NULL NULL 2 NULL NULL 2 1 NULL NULL NULL 2(所影响的行数为 8 行)
请叁看 Query plan是否有分别.我从 interbase看来:distinct 在运算过程中, 使用 sort作排列 而group by 会选用和字段有关的索引,因些若数据数量小, distinct会较快, 相反 group by会较快
其实我自己把问题分解了,我遇到的情况是下面这样,请教:(分数我再加50,因为这个版块我只能给到100)table1列名: a1 a2 a3 a4
1 1 1 1
1 1 1 2
1 1 2 1
1 1 2 2
1 2 1 1
1 2 1 2
1 2 2 1
1 2 2 2
...这是个权限关系表,比如说第二行表示的是此用户属于'1大类'、'1小类'、'1细类'里的'2号成员',依次类推
我想显示整体情况,显示成表格
1 1 1 1
2
2 1
2
2 1 1
2
2 1
2
因为这儿没法划表格线,只能写成上述这样,其实就是相同的类就显示一个
在asp里我的实现办法很笨拙,我嵌套了多层查询,当rows多了就不可行了,请问有没有办法简单解决?
我还需要维护这张表格,有和建议吗?
into #tmp
from table1
where ...select case when a.id=1 then a.a1
when a.id<>1 and a.a1=b.a1 then null
else a.a1 end as a1,
case when a.id=1 then a.a2
when a.id<>1 and a.a1=b.a1 and a.a2=b.a2 then null
else a.a2 end as a2,
case when a.id=1 then a.a3
when a.id<>1 and a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3 then null
else a.a3 end as a3,
a.a4
from #tmp a left join #tmp b
on a.idxxx=b.idxxx+1
into #tmp
from table1select case when a.idxxx=1 then a.a1
when a.idxxx<>1 and a.a1=b.a1 then null
else a.a1 end as a1,
case when a.idxxx=1 then a.a2
when a.idxxx<>1 and a.a1=b.a1 and a.a2=b.a2 then null
else a.a2 end as a2,
case when a.idxxx=1 then a.a3
when a.idxxx<>1 and a.a1=b.a1 and a.a2=b.a2 and a.a3=b.a3 then null
else a.a3 end as a3,
a.a4
from #tmp a left join #tmp b
on a.idxxx=b.idxxx+1结果:
a1 a2 a3 a4
----------- ----------- ----------- -----------
1 1 1 1
NULL NULL NULL 2
NULL NULL 2 1
NULL NULL NULL 2
NULL 2 1 1
NULL NULL NULL 2
NULL NULL 2 1
NULL NULL NULL 2(所影响的行数为 8 行)
而group by 会选用和字段有关的索引,因些若数据数量小, distinct会较快, 相反 group by会较快