Transact-SQL:删除烦人的注册表 [英] Transact-SQL: Deleting anoying registries
问题描述
您好,情况属实:
操作  天     &月NBSP;    &年NBSP;  帐户   量NBSP;   Aplication
1111111       1          1          2000      5555        $ 1 NBSP;             P
1111111       1          1          2000      8888        $ 1 NBSP;             P
1111111       1          1          2000      1111        $ 1 NBSP;            我
1111111       1          1          2000      2222        $ 1 NBSP;             L
1111111       2          1          2000      5555        $ 2 NBSP;             P
1111111       2          1          2000      6666        $ 2 NBSP;             P
1111111       2          1          2000      8888        $ 2 NBSP;             P
1111111       3          1          2000      8888        $ 3 NBSP;             P
我有一张表,包含操作,日,月,年,帐户,金额,适用列。任务是删除帐户8888的行如果和仅如果操作日 - 月 - 年 - 应用 - 金额组合有重复。没有
代码发布它,但是,我使用CTE(WITH命令)使用以下约束提取帐户:
GROUP BY Operation,Year,月,日,应用,金额
H AVING COUNT(AMOUNT)> 1
稍后选择使用左外连接 使用原始表格:
左外加入CtaToRemove为B ON
                    A.Opertion = B.Operation AND
                    A.Year = B.Year AND
                    A.Month = B.Month AND
                    A.Day = B.Day AND
                    A.Aplication = B.Aplication AND
                    A.Amount = B.Amount AND
                    B.Account ='888?'
但是仍在提交所有注册表,有人可以帮助我吗?
如果我理解你想要的东西,那么像
声明@Sample表(Operation int, Day int,Month int,Year int,Account int,Amount decimal(9,5),Aplication char(10));
插入@Sample(操作,日,月,年,帐户,金额,应用程序)值
(1111111,1,1,2000,5555,1,'P'),
( 1111111,1,1,2000,8888,1,'P'),
(1111111,1,1,2000,1111,1,'I'),
(1111111,1,1, 2000,2222,1,'L'),
(1111111,2,1,2000,5555,2,'P'),
(1111111,2,1,2000,6666,2, 'P'),
(1111111,2,1,2000,8888,2,'P'),
(1111111,3,1,2000,8888,3,'P');
选择'之前',*来自@Sample;
从s1
删除来自@Sample s1
其中s1.Account = 8888和
存在(选择*来自@Sample s2其中
s1.Operation = s2.Operation和s1.Day = s2.Day和s1.Month = s2.Month
和s1.Year = s2.Year和s1.Amount = s2.Amount和s1.Aplication = s2.Aplication
和s2.Account<> 8888);
选择'之后',*来自@Sample;
与往常一样,在生产数据库上运行删除之前,请仔细测试并确保备份良好。
汤姆
Hello, This is the case:
Operation Day Month Year Account Amount Aplication
1111111 1 1 2000 5555 $1 P
1111111 1 1 2000 8888 $1 P
1111111 1 1 2000 1111 $1 I
1111111 1 1 2000 2222 $1 L
1111111 2 1 2000 5555 $2 P
1111111 2 1 2000 6666 $2 P
1111111 2 1 2000 8888 $2 P
1111111 3 1 2000 8888 $3 P
I have a table With Operation, Day, Month, Year, Account, Amount, Aplicaction Columns. The Mission is to remove the rows with account 8888 If and Only If there are duplication for the Operation-Day-Month-year-Aplication-Amount Combination. Don't have the code to post it, but, I was using CTE (WITH command) to extract the account with this constrain using:
GROUP BY Operation, Year, Month, Day, Aplication, Amount
HAVING COUNT(AMOUNT) > 1
And later a Select Using Left Outer Join with the Original table using:
Left Outer Join CtaToRemove as B ON
A.Opertion = B.Operation AND
A.Year = B.Year AND
A.Month = B.Month AND
A.Day = B.Day AND
A.Aplication = B.Aplication AND
A.Amount = B.Amount AND
B.Account = '888?'
But Still geting all the registries, someone can help me?
If I understand what you want, then something like
Declare @Sample Table(Operation int, Day int, Month int, Year int, Account int, Amount decimal(9,5), Aplication char(10)); Insert @Sample(Operation, Day, Month, Year, Account, Amount, Aplication) Values (1111111, 1, 1, 2000, 5555, 1, 'P'), (1111111, 1, 1, 2000, 8888, 1, 'P'), (1111111, 1, 1, 2000, 1111, 1, 'I'), (1111111, 1, 1, 2000, 2222, 1, 'L'), (1111111, 2, 1, 2000, 5555, 2, 'P'), (1111111, 2, 1, 2000, 6666, 2, 'P'), (1111111, 2, 1, 2000, 8888, 2, 'P'), (1111111, 3, 1, 2000, 8888, 3, 'P'); Select 'Before', * From @Sample; Delete From s1 From @Sample s1 Where s1.Account = 8888 And Exists(Select * From @Sample s2 Where s1.Operation = s2.Operation And s1.Day = s2.Day And s1.Month = s2.Month And s1.Year = s2.Year And s1.Amount = s2.Amount And s1.Aplication = s2.Aplication And s2.Account <> 8888); Select 'After', * From @Sample;As always, carefully test and be sure you have a good backup before running a delete on you production database.
Tom
这篇关于Transact-SQL:删除烦人的注册表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!