CREATE PROCEDURE FetchCount
@InstoreID varchar(50)='',
@StreamID varchar(50)=''
AS
BEGIN
declare @count int
select @count=count(*) from T_CHART_IN_STORE_DETAIL where StreamID=@StreamID and InstoreID=@InstoreID and IsDelete=0
--上面的这句话能够找出符合条件的数量
END
GO比如说查出来50条,但是这50条纪律比如说都是入库的数据,但是在50条数据中可能是这样的如:1-25 30-50 53-57
客户要求显示出范围就是1-25 30-50 53-57 这样的。但是我目前不知道如何形成这样的样式。
谁能帮我把这个写完呢?
USE AdventureWorks;
GO
WITH OrderedOrders AS
(
SELECT SalesOrderID, OrderDate,
ROW_NUMBER() OVER (ORDER BY OrderDate) AS 'RowNumber'
FROM Sales.SalesOrderHeader
)
SELECT *
FROM OrderedOrders
WHERE RowNumber BETWEEN 50 AND 60;
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- ================================================
-- 创 建 人:Dp
-- 创建日期:2009-09-17
-- 描 述:读取数量
-- ================================================
ALTER PROCEDURE [dbo].[FetchCount]
@InstoreID varchar(50)='',
@StreamID varchar(50)=''
AS
BEGIN
BEGIN TRAN
DECLARE @JL INT
DECLARE @Num varchar(50)
DECLARE @J int
set @J=2
DECLARE @I int
set @I=1
DECLARE @Number int
set @Number=0
DECLARE @SubNumber int
DECLARE @COUNT int
select @COUNT=count(*) from T_CHART_IN_STORE_DETAIL where StreamID=@StreamID and InstoreID=@InstoreID and IsDelete=0
DECLARE Row_CurSor CURSOR FOR
select SubNumber from T_CHART_IN_STORE_DETAIL where StreamID=@StreamID and InstoreID=@InstoreID and IsDelete=0 order by SubNumber
OPEN Row_Cursor
FETCH NEXT FROM Row_Cursor INTO @SubNumber
WHILE @@Fetch_Status = 0
BEGIN
if @I!=2
begin
if @SubNumber-@Number=1
begin
set @Number=@SubNumber
--set @Num=isnull(@Num,'')+'-'+CONVERT(VARCHAR(2),@Number,120)
set @Num=CONVERT(VARCHAR(2),@Number,120)
set @I=@I+1
end
else
begin
set @Number=@SubNumber
set @Num=CONVERT(VARCHAR(2),@Number,120)
set @I=@I+1
end
end
else
begin
if @SubNumber-@Number=1
begin
if @J!=@COUNT
begin
set @Number=@SubNumber
set @JL=@SubNumber
set @J=@J+1
end
else
begin
set @Number=@SubNumber
set @Num=@Num+'-'+CONVERT(VARCHAR(2),@SubNumber,120)
end
end
else
begin
set @Number=@SubNumber
set @Num=@Num+'-'+CONVERT(VARCHAR(2),@JL,120)+','+CONVERT(VARCHAR(2),@Number,120)
end
end
--INSERT INTO TT_CountNum(CountName) VALUES (@SubNumber)
FETCH NEXT FROM Row_Cursor INTO @SubNumber
END
CLOSE Row_Cursor
DEALLOCATE Row_Cursor
select @Num ROLLBACK TRAN
END写好了,自己写的,哈哈哈哈