总是返回记录SQL服务器
问题描述:
指定数量的我有一个查询,我一直想为返回10个记录:总是返回记录SQL服务器
set rowcount 10
select row_number() over(order by count(*) desc) row_num
,hist_report_id
,max(rpt_report_name) report_name
from utility.dbo.tbl_report_history
join utility.dbo.tbl_report_definitions
on hist_report_id = rpt_report_id
where hist_user_id = 1038
group by hist_report_id
,如果我有10分或更多的记录,工作正常。问题是当记录少于10条时,我仍然需要在report_id和report_name字段中返回带有空值的rownumber字段。
如果只有7条记录返回,结果应该是这样的:
row_num report_id report_name
1 id1 name1
2 id2 name2
3 id3 name3
4 id4 name4
5 id5 name5
6 id6 name6
7 id7 name7
8 null null
9 null null
10 null null
有什么建议?
我使用SQL Server 2008的
答
绝不能count()
回报小于零......所以很合理牛逼通过工会在数列与附加-110个空行
另外,不要使用SET ROWCOUNT,因为它影响中间结果
SELECT TOP 10
row_number() over(order by TheCount desc) AS row_num,
hist_report_id,
report_name
FROM
(
select
,count(*) AS TheCount
,hist_report_id
,max(rpt_report_name) AS report_name
from
utility.dbo.tbl_report_history
join
utility.dbo.tbl_report_definitions on hist_report_id = rpt_report_id
where hist_user_id = 1038
group by hist_report_id
UNION ALL
SELECT TOP 10
-1, NULL, NULL
FROM sys.columns
) T
+0
太棒了!我希望有一个单一的声明解决方案,这完美的作品。 – ChandlerPelhams 2012-01-30 14:56:46
答
,我记得最简单的方法:
-
Insert
的select result
在#temptable
-
While count(*) < 10
insert
行into #temptable
答
你可以考虑使用一个TVF返回给你一组的,你可以用外连接数:
CREATE FUNCTION SetOfValues (@beginningAt int, @endingAt int, @step int = 1)
RETURNS
@result TABLE (value int)
AS BEGIN
declare @counter int set @counter = @beginningAt
while(@counter<[email protected])
begin
insert into @result values(@counter)
set @counter = @counter + @step
end
RETURN
END
GO
-- USAGE
with cte as (
select row_number() over (order by hist_report_Id) as row_num,
hist_report_Id, report_name
from tbl_report_history join tbl_report_definitions on hist_report_Id = rpt_reportId)
select value, hist_reportId, report_name
from dbo.SetOfValues(1,10,1) as t on t.value = cte.row_num
答
with
/* Create 10 dummy rows using recursion */
RowSequence as (
select 0 Idx
union all
select RowSequence.Idx+1 Idx from RowSequence where RowSequence.Idx+1<10
),
/* Your Query */
YourQuery as(
select row_number() over(order by count(*) desc) row_num
,hist_report_id
,max(rpt_report_name) report_name
from utility.dbo.tbl_report_history
join utility.dbo.tbl_report_definitions
on hist_report_id = rpt_report_id
where hist_user_id = 1038
group by hist_report_id
)
/* Now all together */
select top 10 * from (
select * from YourQuery
union all
select null, null, null from RowSequence
) a
/* To avoid the recursion maximum level 100 */
option (maxrecursion 0)
SQL Server版本?我想你需要某种与临时表或表变量的联合。 – Blorgbeard 2012-01-30 14:40:15