是不是这个意思select a.* from (select (select count(*) from t where 排序字段<=a.排序字段) as 序号,* from t a) aorder by order by case when a.序号>50 then a.序号-50 else id end
select a.* from (select (select count(*) from t where 排序字段<=a.排序字段) as 序号,* from t a) aorder by order by case when a.序号>50 then a.序号-50 else a.序号 end
有错误 呵呵 select a.* from (select (select count(*) from t where 排序字段<=a.排序字段) as 序号,* from t a) aorder by case when a.序号>50 then a.序号-50 else a.序号 end
--如果id 是主键,按id顺序决定记录号,则可以用:select * from 表 a order by case when (select count(*) from 表 where id<=a.id)>50 then (select count(*) from 表 where id<=a.id)-50 else (select count(*) from 表 where id<=a.id) end
借用邹建的:declare @a int select @a = ceiling(count(*) * 1.0/2) from 表select * from 表 a order by case when (select count(*) from 表 where id<=a.id)>@a then (select count(*) from 表 where id<=a.id)-@a else (select count(*) from 表 where id<=a.id) end或: select * from 表 a order by (case when (select count(*) from 表 where id<=a.id)>(select ceiling(count(*) * 1.0/2) from 表) then (select count(*) from 表 where id<=a.id)-(select ceiling(count(*) * 1.0/2) from 表) else (select count(*) from 表 where id<=a.id) end)好象有点乱,结果应该差不多。
--测试数据 declare @t table(id varchar(10)) insert @t select '004' union all select '003' union all select '005' union all select '008' union all select '007' union all select '009' union all select '001' union all select '006' union all select '002' union all select '000' union all select '011'--查询 select id from @t a order by case when (select count(*) from @t where id<=a.id)>6 then (select count(*) from @t where id<=a.id)-6 else (select count(*) from @t where id<=a.id) end,id/*--测试结果id ---------- 000 006 001 007 002 008 003 009 004 011 005(所影响的行数为 11 行) --*/
SELECT * FROM tA ORDER BY (Id % 6), Id
SELECT @COUNT:=CEILING(COUNT(KSBH)/2) FROM net_sbm; SELECT @Id:=-1; SELECT KSBH, IMG FROM ( SELECT @Id := @Id + 1 Id, @Id%@COUNT, KSBH, IMG FROM net_sbm ORDER BY KSBH ) tA;
SELECT @COUNT:=CEILING(COUNT(KSBH)/2) FROM net_sbm; SELECT @Id:=-1; SELECT KSBH, IMG FROM ( SELECT @Id := @Id + 1 Id, @Id%@COUNT Index_2, KSBH, IMG FROM net_sbm ORDER BY KSBH ) tA ORDER BY Index_2, Id;呵呵,应该是这个.
--处了,给你写成动态的,你自己修改一下表名就行了,其他的不用改--测试数据 declare @t table(id varchar(10)) insert @t select '004' union all select '003' union all select '005' union all select '008' union all select '007' union all select '009' union all select '001' union all select '006' --union all select '002' --union all select '000' --union all select '011'--查询 select a.* from @t a,(select cnt=(count(*)+1)/2 from @t)b order by case when (select count(*) from @t where id<=a.id)>b.cnt then (select count(*) from @t where id<=a.id)-b.cnt else (select count(*) from @t where id<=a.id) end,id/*--测试结果id ---------- 001 006 003 007 004 008 005 009(所影响的行数为 8 行) --*/
order by (case when id>50 then id-50 else id end),id
(select (select count(*) from t where 排序字段<=a.排序字段) as 序号,* from t a) aorder by order by case when a.序号>50 then a.序号-50 else id end
(select (select count(*) from t where 排序字段<=a.排序字段) as 序号,* from t a) aorder by order by case when a.序号>50 then a.序号-50 else a.序号 end
select a.* from
(select (select count(*) from t where 排序字段<=a.排序字段) as 序号,* from t a) aorder by case when a.序号>50 then a.序号-50 else a.序号 end
因为在报表 中我没有办法实现这种效果,只好求助于打开记录的时候排序有没有办法了??
order by case when (select count(*) from 表 where id<=a.id)>50 then (select count(*) from 表 where id<=a.id)-50 else (select count(*) from 表 where id<=a.id) end
select @a = ceiling(count(*) * 1.0/2) from 表select * from 表 a
order by case when (select count(*) from 表 where id<=a.id)>@a then (select count(*) from 表 where id<=a.id)-@a else (select count(*) from 表 where id<=a.id) end或:
select * from 表 a
order by (case when (select count(*) from 表 where id<=a.id)>(select ceiling(count(*) * 1.0/2) from 表)
then (select count(*) from 表 where id<=a.id)-(select ceiling(count(*) * 1.0/2) from 表) else (select count(*) from 表 where id<=a.id) end)好象有点乱,结果应该差不多。
declare @t table(id varchar(10))
insert @t select '004'
union all select '003'
union all select '005'
union all select '008'
union all select '007'
union all select '009'
union all select '001'
union all select '006'
union all select '002'
union all select '000'
union all select '011'--查询
select id from @t a
order by case
when (select count(*) from @t where id<=a.id)>6
then (select count(*) from @t where id<=a.id)-6
else (select count(*) from @t where id<=a.id) end,id/*--测试结果id
----------
000
006
001
007
002
008
003
009
004
011
005(所影响的行数为 11 行)
--*/
ORDER BY (Id % 6), Id
SELECT @Id:=-1;
SELECT KSBH, IMG FROM
(
SELECT @Id := @Id + 1 Id, @Id%@COUNT, KSBH, IMG FROM net_sbm ORDER BY KSBH
) tA;
SELECT @Id:=-1;
SELECT KSBH, IMG FROM
(
SELECT @Id := @Id + 1 Id, @Id%@COUNT Index_2, KSBH, IMG FROM net_sbm ORDER BY KSBH
) tA
ORDER BY Index_2, Id;呵呵,应该是这个.
shuixin13(犬犬(心帆)) ::您的方法完全正确,但现在还没有办法将其应用到VB中去执行,还要加工。
待这个问题彻底解决后,本人另开100分贴,到时短消息请你们来领分,绝不食言。
declare @t table(id varchar(10))
insert @t select '004'
union all select '003'
union all select '005'
union all select '008'
union all select '007'
union all select '009'
union all select '001'
union all select '006'
--union all select '002'
--union all select '000'
--union all select '011'--查询
select a.* from @t a,(select cnt=(count(*)+1)/2 from @t)b
order by case
when (select count(*) from @t where id<=a.id)>b.cnt
then (select count(*) from @t where id<=a.id)-b.cnt
else (select count(*) from @t where id<=a.id) end,id/*--测试结果id
----------
001
006
003
007
004
008
005
009(所影响的行数为 8 行)
--*/
我正在测试您的数据。
我正在测试您的语句。
(@Id := @Id + 1) Id,
@Id%@COUNT Index_2,
KSBH
FROM net_sbm,
(SELECT @Id := -1) tA,
(SELECT @COUNT:=CEILING(COUNT(KSBH)/2) FROM net_sbm) tB
ORDER BY KSBH嗯.由于他用的 ODBC 无法支持执行多条语句.
所以修改成上面的语句应该就可以了.