usuń duplikaty z tabeli w ms sql CREATE TABLE xprimerbufor.dbo.employee ( id int NOT…
- User
usuń duplikaty z tabeli w ms sql CREATE TABLE xprimer_bufor.dbo.employee ( id int NOT NULL, date_from datetime NOT NULL, date_to datetime NULL, start_work_date datetime NULL, end_work_date datetime NULL, division_id int NULL, position_id int NULL, working_time_id int NULL, working_system_id int NULL, accounting_period_id int NULL, wage_rate_per_hour numeric(19,2) NULL, wage_rate_per_month numeric(19,2) NULL, contract_type char(1) COLLATE Polish_CS_AS NULL, contract_type_id int NULL, breast_feeding_women int NULL, disability_id int NULL, inland_revenue varchar(100) COLLATE Polish_CS_AS NULL, pesel varchar(11) COLLATE Polish_CS_AS NULL, regon varchar(14) COLLATE Polish_CS_AS NULL, nip varchar(13) COLLATE Polish_CS_AS NULL, identity_card_no varchar(15) COLLATE Polish_CS_AS NULL, identity_card_issue_date datetime NULL, identity_card_expiry_date datetime NULL, identity_card_authority varchar(60) COLLATE Polish_CS_AS NULL, passport_no varchar(20) COLLATE Polish_CS_AS NULL, passport_issue_date datetime NULL, passport_expiry_date datetime NULL, bank_account_number varchar(100) COLLATE Polish_CS_AS NULL, external_number varchar(20) COLLATE Polish_CS_AS NULL, employment_accepted int NULL, forename varchar(50) COLLATE Polish_CS_AS NULL, surname varchar(50) COLLATE Polish_CS_AS NULL, email varchar(100) COLLATE Polish_CS_AS NULL, analityk int NULL, dzial_kadr int NULL, dzial_kadr_administrator int NULL, planer int NULL, podglad_grafikow int NULL, czlonek_zarzadu int NULL)
Assistant
Aby usunąć duplikaty z tabeli w MS SQL, możesz użyć kilku różnych podejść. Poniżej przedstawiam kilka metod.
Metoda 1: Użycie
ROW_NUMBER()lubRANK()Jeśli chcesz usunąć duplikaty na podstawie wszystkich kolumn, możesz użyć funkcji
ROW_NUMBER()lubRANK(). Tutaj przykład zROW_NUMBER():WITH CTE AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY id, date_from, date_to, start_work_date, end_work_date, division_id, position_id, working_time_id, working_system_id, accounting_period_id, wage_rate_per_hour, wage_rate_per_month, contract_type, contract_type_id, breast_feeding_women, disability_id, inland_revenue, pesel, regon, nip, identity_card_no, identity_card_issue_date, identity_card_expiry_date, identity_card_authority, passport_no, passport_issue_date, passport_expiry_date, bank_account_number, external_number, employment_accepted, forename, surname, email, analityk, dzial_kadr, dzial_kadr_administrator, planer, podglad_grafikow, czlonek_zarzadu ORDER BY id) AS RN FROM xprimer_bufor.dbo.employee ) DELETE FROM CTE WHERE RN > 1;Metoda 2: Użycie
DISTINCTJeśli chcesz usunąć duplikaty na podstawie wybranych kolumn, możesz wstawić dane do nowej tabeli z użyciem
DISTINCT:SELECT DISTINCT id, date_from, date_to, start_work_date, end_work_date, division_id, position_id, working_time_id, working_system_id, accounting_period_id, wage_rate_per_hour, wage_rate_per_month, contract_type, contract_type_id, breast_feeding_women, disability_id, inland_revenue, pesel, regon, nip, identity_card_no, identity_card_issue_date, identity_card_expiry_date, identity_card_authority, passport_no, passport_issue_date, passport_expiry_date, bank_account_number, external_number, employment_accepted, forename, surname, email, analityk, dzial_kadr, dzial_kadr_administrator, planer, podglad_grafikow, czlonek_zarzadu INTO xprimer_bufor.dbo.employee_new FROM xprimer_bufor.dbo.employee; DROP TABLE xprimer_bufor.dbo.employee; EXEC sp_rename 'xprimer_bufor.dbo.employee_new', 'employee', 'OBJECT';Metoda 3: Bez użycia CTE
Możesz również użyć kursora lub zwykłego
DELETEz warunkami, ale te metody są zwykle mniej efektywne i bardziej skomplikowane.Przed wykonaniem operacji usunięcia duplikatów zalecam wykonanie kopii zapasowej danych lub przeniesienie ich do innej tabeli, aby w razie problemów móc przywrócić oryginalne dane.
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.