--示例--测试数据
declare @t table(id int)
insert @t select 1--记录数少于10条时的查询
select id from @t
union all
select top 10 null
from @t a,sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from @t)--记录数多于10条记录的情况
--补充记录
insert @t select id from sysobjects--同样的查询
select id from @t
union all
select top 10 null
from @t a,sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from @t)
declare @t table(id int)
insert @t select 1--记录数少于10条时的查询
select id from @t
union all
select top 10 null
from @t a,sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from @t)--记录数多于10条记录的情况
--补充记录
insert @t select id from sysobjects--同样的查询
select id from @t
union all
select top 10 null
from @t a,sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from @t)
declare @count in
declare @a int
select @count=count(*) from 表A
if @count=10
begin
select * from 表A
print @count
end
else if @count<10
set @a=@count
begin
while @a<=10
begin
insert into 表A
select null,null
set @a=@a+1
end
print @a
end
else
begin
select * from 表A
print @count
end
不知道樓主是不是 這個意思哦
要是不是的話請說清楚。最好是給一個實例。
select *,num=
case
when count(*)<10 then 10
when count(*)=10 then 10
when count(*)>10 then count(*)
end
from people
group by people.name,people.age,people.sex
1、若A中的记录为
p q
1 1
2 2
3 3
一共三条记录,则查询结果为:
p q
1 1
2 2
3 3
null null
null null
null null
null null
null null
null null
null null
查到的记录为10条
2、若A中的记录为
p q
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
一共10条记录,则查询结果为:
p q
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
查到的记录为10条
3、若A中的记录为
p q
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13 一共10条记录,则查询结果为:
p q
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13
查到的记录为13条
当库中符合条件的记录少于10条时,你写的语句不能得到正确的结果
select p,q from 表
union all
select top 10 null,null
from 表 a,sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from 表)
create table tb(p int,q int)
insert tb select 1,1
union all select 2,2
union all select 3,3
go--查询
select p,q from tb
union all
select null,null
from sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from tb)
go
--删除测试
drop table tb/*--结果
p q
----------- -----------
1 1
2 2
3 3
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL(所影响的行数为 10 行)
--*/
create table tb(p int,q int)
insert tb select 1,1
union all select 2,2
union all select 3,3
union all select 4,4
union all select 5,5
union all select 6,6
union all select 7,7
union all select 8,8
union all select 9,9
union all select 10,10
go--查询
select p,q from tb
union all
select null,null
from sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from tb)
go
--删除测试
drop table tb/*--结果
p q
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10(所影响的行数为 10 行)
--*/
create table tb(p int,q int)
insert tb select 1,1
union all select 2,2
union all select 3,3
union all select 4,4
union all select 5,5
union all select 6,6
union all select 7,7
union all select 8,8
union all select 9,9
union all select 10,10
union all select 11,11
union all select 12,12
union all select 13,13
go--查询
select p,q from tb
union all
select null,null
from sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from tb)
go
--删除测试
drop table tb/*--结果p q
----------- -----------
1 1
2 2
3 3
4 4
5 5
6 6
7 7
8 8
9 9
10 10
11 11
12 12
13 13(所影响的行数为 13 行)
--*/
--其中tb是要查询数据的表名,p,q是要查询的字段名,select null,null对应记录不足时,补充记录的p,q两个字段值,如果有更多的字段,则补充更多的null,保证union all 后面的查询与union all前面的查询字段数匹配.select p,q from tb
union all
select null,null
from sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from tb)
select p,q from tb
union all
select null,null
from sysobjects b
where (select count(*) from sysobjects where id<=b.id)
between 1 and 10-(select count(*) from tb)
向你学习!!!