--测试数据
select V=1,BN='a' into # union all
select V=2,BN='a' union all
select V=3,BN='a' union all
select V=7,BN='b' union all
select V=8,BN='b' union all
select V=9,BN='b'
go--查询
select a=min(case BN when 'a' then V end),
b=min(case BN when 'b' then V end)
from(select *,gid=(select count(*) from # where BN=a.BN and V<=a.V) from # a)a
group by gid
go--删除测试
drop table #/*--结果
a b
----------- -----------
1 7
2 8
3 9(所影响的行数为 3 行)
--*/
select V=1,BN='a' into # union all
select V=2,BN='a' union all
select V=3,BN='a' union all
select V=7,BN='b' union all
select V=8,BN='b' union all
select V=9,BN='b'
go--查询
select a=min(case BN when 'a' then V end),
b=min(case BN when 'b' then V end)
from(select *,gid=(select count(*) from # where BN=a.BN and V<=a.V) from # a)a
group by gid
go--删除测试
drop table #/*--结果
a b
----------- -----------
1 7
2 8
3 9(所影响的行数为 3 行)
--*/
to csdnzm(明飞) :
如果不是a b
case BN when @a then V end
select V=2,BN='a' union all
select V=3,BN='a' union all
select V=7,BN='b' union all
select V=8,BN='b' union all
select V=9,BN='b'
--------------------------------------这个是建临时表吗?看不懂,能帮忙解释下语法吗?还有这个union all 看了半天联机丛书,还是搞不清楚。
//-- os: windows 2000
//-- sql调试器: access 2000
//-- 注释符号: //--
//--- 附加说明:
//-- 对数据的假定,
//-- 1.内容只有两项,a,和b,
//-- 2.a,和b的个数应该是一样的,(不一样也没有关系)
//-- 3. 任意一个b的数据对应的id必须比 任意的a的 id大。
//-- 否则做这样的查询会有二义性。
//-- 还有些没有完成下班了。明天再来写create table t1
(
f1 int,
f2 char(10)
)insert into t1 values(1,'a')
insert into t1 values(2,'a')
insert into t1 values(3,'a')
insert into t1 values(4,'a')insert into t1 values(6,'b')
insert into t1 values(7,'b')
insert into t1 values(8,'b')
select * from (select A.f1 ,(select count(*) from t1 B where B.f2=A.f2 and b.f1<=A.f1) As rownum from t1 A ) T2 ,
(select A.f1 ,(select count(*) from t1 B where B.f2=A.f2 and b.f1<=A.f1) As rownum from t1 A ) T3
where t2.rownum = t3.rownum and t2.f1<>t3.f1
select * from (select A.f1 ,(select count(*) from t1 B where B.f2=A.f2 and b.f1<=A.f1) As rownum from t1 A ) T2 ,
(select A.f1 ,(select count(*) from t1 B where B.f2=A.f2 and b.f1<=A.f1) As rownum from t1 A ) T3
where t2.rownum = t3.rownum and t2.f1<>t3.f1 and t2.f1 > t3.f1
不好意思,好久没关注这个贴子了.不是一定是对半的,就是相合并成二列的一个表,如果对不齐,就NULL