السلام عليكم
عندى مشكلة وهيا عندى جدول يحتوى على مجموعة من الصفوف بالشكل التالى
المشكلة انه تم تكرار البيانات فى الاسطر رقم 1 و 5 و 9
السؤال كيف يتم حذف بيانات التكرار فى حالة تكرار كود الصنف يتم حذف الاصناف الاعلى سعر وفى جالة التساوى فى السعر يتم حذف اى صنف منهم
بمعنى لو ناخد مثال على كود الصنف 28850-06J00 يتم حذف السطر رقم 9 و اى سطر اخر من التكرار معه ممكن يكون السطر رقم 5 او السطر رقم 1
مرفق اسكربت الجدول والبيانات
اقتباسSET NUMERIC_ROUNDABORT OFF
GO
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
IF EXISTS (SELECT * FROM tempdb..sysobjects WHERE id=OBJECT_ID('tempdb..#tmpErrors')) DROP TABLE #tmpErrors
GO
CREATE TABLE #tmpErrors (Error int)
GO
SET XACT_ABORT ON
GO
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
GO
BEGIN TRANSACTION
GO
PRINT N'Creating [dbo].[item_Priceoffer]'
GO
CREATE TABLE [dbo].[item_Priceoffer]
(
[sn] [bigint] NOT NULL IDENTITY(1, 1),
[itemCode] [nvarchar] (50) COLLATE Arabic_CI_AS NULL,
[itemPrice] [money] NULL,
[supplierID] [int] NULL,
[CurrentBalance] [int] NULL
)
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
PRINT N'Creating primary key [PK_Item_Priceoffer] on [dbo].[item_Priceoffer]'
GO
ALTER TABLE [dbo].[item_Priceoffer] ADD CONSTRAINT [PK_Item_Priceoffer] PRIMARY KEY CLUSTERED ([sn])
GO
IF @@ERROR<>0 AND @@TRANCOUNT>0 ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT=0 BEGIN INSERT INTO #tmpErrors (Error) SELECT 1 BEGIN TRANSACTION END
GO
IF EXISTS (SELECT * FROM #tmpErrors) ROLLBACK TRANSACTION
GO
IF @@TRANCOUNT>0 BEGIN
PRINT 'The database update succeeded'
COMMIT TRANSACTION
END
ELSE PRINT 'The database update failed'
GO
DROP TABLE #tmpErrors
GO
SET NUMERIC_ROUNDABORT OFF
GO
SET XACT_ABORT, ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIER, ANSI_NULLS, NOCOUNT ON
GO
SET DATEFORMAT YMD
GO
-- Pointer used for text / image updates. This might not be needed, but is declared here just in case
DECLARE @pv binary(16)
BEGIN TRANSACTION
-- Add 9 rows to [dbo].[item_Priceoffer]
SET IDENTITY_INSERT [dbo].[item_Priceoffer] ON
INSERT INTO [dbo].[item_Priceoffer] ([sn], [itemCode], [itemPrice], [supplierID], [CurrentBalance]) VALUES (1, N'28850-06J00', 10.0000, 11, 0)
INSERT INTO [dbo].[item_Priceoffer] ([sn], [itemCode], [itemPrice], [supplierID], [CurrentBalance]) VALUES (2, N'30100-F55XF', 2.0000, 11, 0)
INSERT INTO [dbo].[item_Priceoffer] ([sn], [itemCode], [itemPrice], [supplierID], [CurrentBalance]) VALUES (3, N'30210-VB002', 15.0000, 11, 0)
INSERT INTO [dbo].[item_Priceoffer] ([sn], [itemCode], [itemPrice], [supplierID], [CurrentBalance]) VALUES (4, N'30502-21000', 30.0000, 11, 0)
INSERT INTO [dbo].[item_Priceoffer] ([sn], [itemCode], [itemPrice], [supplierID], [CurrentBalance]) VALUES (5, N'28850-06J00', 10.0000, 21, 0)
INSERT INTO [dbo].[item_Priceoffer] ([sn], [itemCode], [itemPrice], [supplierID], [CurrentBalance]) VALUES (6, N'30100-F55XF', 1.0000, 21, 0)
INSERT INTO [dbo].[item_Priceoffer] ([sn], [itemCode], [itemPrice], [supplierID], [CurrentBalance]) VALUES (7, N'30210-VB002', 10.0000, 21, 0)
INSERT INTO [dbo].[item_Priceoffer] ([sn], [itemCode], [itemPrice], [supplierID], [CurrentBalance]) VALUES (8, N'30502-21000', 30.0000, 21, 0)
INSERT INTO [dbo].[item_Priceoffer] ([sn], [itemCode], [itemPrice], [supplierID], [CurrentBalance]) VALUES (9, N'28850-06J00', 200.0000, 26, 0)
SET IDENTITY_INSERT [dbo].[item_Priceoffer] OFF
COMMIT TRANSACTION
GO
