Читайте также:
|
1 Ram 1000
1 Ram 1000
1 Ram 1000
2 Joe 1000
2 Joe 2000
2 Joe 1000
3 Mary 1000
4 Julie 5000
1 Ram 1000
2 Joe 1000
2 Joe 2000
3 Mary 1000
4 Julie 5000
---------------------------------
ProblemForExam08
1> USE demodb
2> GO
Changed database context to 'demodb'.
1> IF OBJECT_ID(N'dbo.Employee', 'U') IS NOT NULL
2> DROP TABLE dbo.Employee
3> GO
1> CREATE TABLE dbo.Employee
2> (
3> Id int NOT NULL,
4> Name nvarchar(20) NOT NULL,
5> Salary decimal NOT NULL
6>);
7> GO
1> INSERT INTO dbo.Employee (Id, Name, Salary)
2> VALUES
3> (1, N'Ram', 1000),
4> (1, N'Ram', 1000),
5> (2, N'Joe', 2000),
6> (2, N'Joe', 1000),
7> (3, N'Mary', 1000),
8> (4, N'Julie', 5000),
9> (2, N'Joe', 1000),
10> (1, N'Ram', 1000)
11> GO
(8 rows affected)
1> SELECT Id, Name, Salary FROM dbo.Employee
2> GO
Id Name Salary
----------- -------------------- --------------------
1 Ram 1000
1 Ram 1000
2 Joe 2000
2 Joe 1000
3 Mary 1000
4 Julie 5000
2 Joe 1000
1 Ram 1000
(8 rows affected)
1> -- ************************************************************
2> -- Сценарий удаления дубликатов (второго или следующих экземпляров)
3> -- записей из таблицы Employee
4> -- ************************************************************
5> DECLARE @id int, @name varchar (20), @cnt int, @salary numeric
6> DECLARE GetAllDuplicateRecords CURSOR LOCAL STATIC FOR
7> SELECT COUNT (*), Id, Name, Salary
8> FROM dbo.Employee (NOLOCK)
9> GROUP BY Id, Name, Salary
10> HAVING COUNT(*)>1
11> OPEN GetAllDuplicateRecords
12> FETCH NEXT FROM GetAllDuplicateRecords INTO @cnt, @id, @name, @salary
13> WHILE @@FETCH_STATUS = 0
14> BEGIN
15> SET @cnt = @cnt-1
16> SET ROWCOUNT @cnt
17> DELETE FROM Employee WHERE Id=@id and Name=@name and Salary=@salary
18> SET ROWCOUNT 0
19> -- DELETE TOP (@cnt)FROM Employee WHERE Id=@id and Name=@name and Salary
=@salary
20> FETCH NEXT FROM GetAllDuplicateRecords INTO @cnt, @id, @name, @salary
21> END
22> CLOSE GetAllDuplicateRecords
23> DEALLOCATE GetAllDuplicateRecords
24> -- ************************************************************
25> GO
(2 rows affected)
1> SELECT Id, Name, Salary FROM dbo.Employee ORDER BY Id
2> GO
Id Name Salary
----------- -------------------- --------------------
1 Ram 1000
2 Joe 1000
2 Joe 2000
3 Mary 1000
4 Julie 5000
(5 rows affected)
USE demodb
GO
IF OBJECT_ID(N'dbo.Employee', 'U') IS NOT NULL
DROP TABLE dbo.Employee
GO
CREATE TABLE dbo.Employee
(
Id int NOT NULL,
Name nvarchar(20) NOT NULL,
Salary decimal NOT NULL
);
GO
INSERT INTO dbo.Employee (Id, Name, Salary)
VALUES
(1, N'Ram', 1000),
(1, N'Ram', 1000),
(2, N'Joe', 2000),
(2, N'Joe', 1000),
(3, N'Mary', 1000),
(4, N'Julie', 5000),
(2, N'Joe', 1000),
(1, N'Ram', 1000)
GO
SELECT Id, Name, Salary FROM dbo.Employee
GO
-- ************************************************************
-- Сценарий удаления дубликатов (второго или следующих экземпляров)
-- записей из таблицы Employee –
-- ************************************************************
DECLARE @id int, @name varchar (20), @cnt int, @salary numeric
DECLARE GetAllDuplicateRecords CURSOR LOCAL STATIC FOR
SELECT COUNT (*), Id, Name, Salary
FROM dbo.Employee (NOLOCK)
GROUP BY Id, Name, Salary
HAVING COUNT(*)>1
OPEN GetAllDuplicateRecords
FETCH NEXT FROM GetAllDuplicateRecords INTO @cnt, @id, @name, @salary
WHILE @@FETCH_STATUS = 0
BEGIN
SET @cnt = @cnt-1
SET ROWCOUNT @cnt
DELETE FROM Employee WHERE Id=@id and Name=@name and Salary=@salary
SET ROWCOUNT 0
-- DELETE TOP (@cnt)FROM Employee WHERE Id=@id and Name=@name and Salary=@salary
FETCH NEXT FROM GetAllDuplicateRecords INTO @cnt, @id, @name, @salary
END
CLOSE GetAllDuplicateRecords
DEALLOCATE GetAllDuplicateRecords
-- ************************************************************
GO
SELECT Id, Name, Salary FROM dbo.Employee ORDER BY Id
GO
Вариант 13
Создать и протестировать хранимую процедуру на T-SQL, которая загружает XML-файл, спецификация которого указывается параметром процедуры, и выводит его в виде реляционного набора строк.
------------------------------
Дата добавления: 2015-02-16; просмотров: 59 | Поможем написать вашу работу | Нарушение авторских прав |