ایجاد رکوردهای تکراری از یک رکورد به تعداد فیلد QTY از همان رکورد

[PalizeSoftware]

من سوالی برام پیش اومده و اینکه:
اگر جدولی داشته باشیم به نام tbl_Order که حاوی چند فیلد منجمله QTY‌ باشد،
آیا امکانش هست که هر رکورد از این جدول را به تعداد مقدار فیلد QTY از همان رکورد، تکراری داشته باشیم (در نتیجه SELECT) ؟
می‌دونم که از طریق Cursor‌ قابل پیاده‌سازی است (با استفاده از حلقه)، منتها روش دیگه‌ای هم سراغ دارید بفرمائید.
ممنون میشم.

[AminSobati]

دوست عزیزم،
انجام این کار به یک Table Valued Function نیاز داره که رکورد مورد نظر رو به تعداد Qty برگردونه:


USE Northwind
GO
CREATE FUNCTION Qty (@PID INT)
RETURNS @MyTab TABLE (ProductID INT, ProductName VARCHAR(50), UnitsInStock INT)
AS
BEGIN
DECLARE @Counter INT
DECLARE @ProductID INT, @ProductName VARCHAR(50), @Qty INT
SET @Counter=0
SELECT @ProductID=ProductID, @ProductName=ProductName, @Qty=UnitsInStock FROM Products WHERE ProductID=@PID

WHILE @Counter <> @Qty
BEGIN
INSERT @MyTab VALUES(@ProductID, @ProductName, @Qty)
SET @Counter=@Counter+1
END
RETURN
END
GO

من در این مثال از فیلد UnitsInStock که مقدار موجودی در انبار (جدول Products) رو نشون میده استفاده کردم. حالا برای فعال کردن این تابع مینویسیم:


SELECT * FROM DBO.Qty (19)

همونطور که دیدید استفاده از Loop اجتناب ناپذیره. ولی قسمت مشکل کار زمانیه که شما برای تک تک Productها در جدول Products چنین Resultی لازم داشته باشید. در این حالت اگر SQL Server 2005 استفاده میکنید، Outer Apply که در حقیقت Join جدیدی هستش کمک میکنه. ولی در SQL Server 2000 ناچارا باید یا چندین SELECT رو با هم UNION کنین:


SELECT * FROM DBO.Qty (19)
UNION ALL
SELECT * FROM DBO.Qty (15)
UNION ALL
SELECT * FROM DBO.Qty (10)

یا به قول خودتون با یک Cursor نتیجه بگیرید...

[PalizeSoftware]

ممنون از پاسخ شما. حالا بفرمائید که نحوه استفاده از Cursor‌ بصورت زیر صحیح است:


create procedure Ord_sp as

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tORDERTBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tORDERTBL]


CREATE TABLE [dbo].[tORDERTBL]
(
[ORDER_NO] [int] NULL ,
[ORDER_DATE] [nvarchar] (10) NULL ,
[CUSTOMER_NAME] [nvarchar] (100) NULL ,
[GOODS_NAME] [nvarchar] (100) NULL ,
[COLOR_NAME] [nvarchar] (100) NULL ,
[BARCODE] [nvarchar] (17) NULL
)

declare @ORDER_NO int,
@ORDER_DATE nvarchar(10),
@Customer_Name nvarchar(100),
@GOODSName nvarchar(100),
@COLOR_Name nvarchar(100),
@ORDER_QTY int,
@BARCODE nvarchar(17)

declare ord_cursor cursor for
SELECT *
FROM temp_order

open ord_cursor
fetch ord_cursor into @ORDER_NO,@ORDER_DATE,@Customer_Name,
@GOODSName,@COLOR_Name,@ORDER_QTY,@BARCODE

while (@@fetch_status=0)
begin
while(@ORDER_QTY>0)
begin
insert into [dbo].[tORDERTBL] ([Order_NO],[ORDER_DATE],[Customer_NAME],[GOODS_NAME],
[COLOR_NAME],[BARCODE])
values (@ORDER_NO,@ORDER_DATE,@Customer_Name,
@GOODSName,@COLOR_Name,@BARCODE)

SET @ORDER_QTY=@ORDER_QTY-1
end

fetch ord_cursor into @ORDER_NO,@ORDER_DATE,@Customer_Name,
@GOODSName,@COLOR_Name,@ORDER_QTY,@BARCODE
end

close ord_cursor
DEALLOCATE ord_cursor

SELECT *
FROM [tORDERTBL];

drop table [dbo].[tORDERTBL]
go

[S.Azish]

میتونید از Temp table ها هم استفاده کنید



SELECT * INTO ##Temp FROM TblOrder

SET IDENTITY_INSERT ##Temp ON

WHILE 1 = 1
BEGIN

INSERT INTO ##Temp (ID, NAME, QTY) SELECT * FROM tblOrder WHERE [Id] IN
(
SELECT [Id] FROM ##Temp GROUP BY [Id] HAVING COUNT([Id]) <> MIN(QTY)
)

IF (@@ROWCOUNT = 0)
BREAK
END

SELECT * FROM ##Temp ORDER BY [Id]

DROP TABLE ##Temp


با فرض اینکه نام فیلدها Id, Name و Qty باشه.

[PalizeSoftware]

جالب بود جناب آذیش دستتون درد نکنه.
به نظرم روش شما خیلی زیرکانه بود.

[AminSobati]

من هم این روش رو تحسین میکنم. ولی در استفاده از Function قائدتا Performance بهتری باید بدست بیاد. چون در روش دوم Group By مرتبا تکرار میشه که با افزایش حجم اطلاعات، پردازش بیشتری میطلبه.
یک نکته مهم: تابعی که در روش اول قید شده، برای رکوردی که UnitsInStock یا همون Qty برابر با صفر باشه، چیزی برنمیگردونه. اگر نیاز برنامه شما اینه که حتی این رکوردها هم در نتیجه ظاهر بشن، باید تابع به شکل زیر اصلاح بشه(البته معقول نیست چون در اینصورت رکوردی که Qty=0 داشته باشه و رکوردی که Qty=1 داشته باشه هر دو، یکبار در نتیجه ظاهر میشن)


ALTER FUNCTION Qty (@PID INT)
RETURNS @MyTab TABLE (ProductID INT, ProductName VARCHAR(50), UnitsInStock INT)
AS
BEGIN
DECLARE @Counter INT
DECLARE @ProductID INT, @ProductName VARCHAR(50), @Qty INT
SET @Counter=0
SELECT @ProductID=ProductID, @ProductName=ProductName, @Qty=UnitsInStock FROM Products WHERE ProductID=@PID
IF @Qty=0
BEGIN
INSERT @MyTab VALUES(@ProductID, @ProductName, @Qty)
RETURN
END
WHILE @Counter <> @Qty
BEGIN
INSERT @MyTab VALUES(@ProductID, @ProductName, @Qty)
SET @Counter=@Counter+1
END
RETURN
END

در روش دوم، احتمال داره وجود رکوردهایی با Qty=0 نتیجه خارج از انتظار ایجاد کنند که به این شکل قابل اصلاحه:


INSERT INTO ##Temp (ID, NAME, QTY) SELECT * FROM tblOrder WHERE [Id] IN
(
SELECT [Id] FROM ##Temp GROUP BY [Id] HAVING COUNT([Id]) <> MIN(QTY)
)
AND QTY<>0