2018-04-26
--/****** Script for SelectTopNRows command from SSMS ******/
--SELECT TOP 1000 [ID]
-- ,[Original]
-- ,[Now]
-- FROM [bh20180420].[dbo].[EmployeeID]
-- update Employee set PositionID='12',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('2031','2069','2070','2802','2805','2806','2808','2809','2810','8819'))
-- update Employee set PositionID='10',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1220','1283','3001','3002','3007','3102','3203','3218','3219','3220','3223','3225','3568','3997','3999'))
-- update Employee set PositionID='61',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1100','1155','1156','1157','1158','1159'))
-- update Employee set PositionID='22',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('2022','2901'))
-- update Employee set PositionID='47',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1007','1013','1016','1020','1030','1266','1268','1271','1279','1308','1621','1830','1831','1835'))
-- update Employee set PositionID='04',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1001','1015','1101','1826','1836','1845','1850','1851','9001'))
-- update Employee set PositionID='23',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1106','1116','1117','1118','1123','1132','1133','1134','1135','1605'))
-- update Employee set PositionID='06',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1601','1602','1609','1613','1620','1623','1624','1625','1626','1628'))
-- update Employee set PositionID='01',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1284','1285','1286','1287'))
-- update Employee set PositionID='07',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('8003','8010'))
-- update Employee set PositionID='60',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1503','1619','1622','6603','6604','6606','6607','6608','6609','6610','6611','6612'))
-- update Employee set PositionID='39',WorkStatus='在职' where ID in(select Now from EmployeeID where Original in ('1265','88001','88002','88005','88010','88011','88015','88019','88021','88023','88028','88029','88030','88032','88033','88037','88040','88068','88069','88070','88071','88072','88073','88074'))
-- select * from Employee
-- select * from PositionInfo
-- --update PositionInfo set ScontoPower=1
-- select * from Department
-- select * from H_Employee where sDept='001004' and sStau='正常'
-- select * from H_Employee where sName='金楠楠L'
-- select * from H_Employee where sDept='001005' and sStau='正常'
-- select * from H_Employee where sDept='001007' and sStau='正常'
-- select * from H_Employee where sDept='001014' and sStau='正常'
-- select * from H_Employee where sDept='001015' and sStau='正常'
-- select * from H_Employee where sDept='001015001' and sStau='正常'
-- select * from H_Employee where sDept='001015002' and sStau='正常'
-- select * from H_Employee where sDept='001015004' and sStau='正常'
-- select * from H_Employee where sDept='001015005' and sStau='正常'
-- select * from H_Employee where sDept='001018003' and sStau='正常'
-- select * from H_Employee where sDept='001020' and sStau='正常'
-- select * from H_Employee where sDept='001021' and sStau='正常'
select * from GoodsInfo
FactoryID
select * from GoodsInfoID
select * from GoodsFactoryInfo ID
select * from GoodsFactoryInfo
select * from GoodsInfo
select * from GoodsFactoryInfo
select * from GoodsFactoryInfo
select * from GoodsInfo
select * from GoodsFactoryInfo
select * from MedicineFactoryInfo
--update GoodsFactoryInfo set ID=replace(ID,'f','0')
select dDT from H_FrontSK_Project where dDT>'2018-04-20 00:00:00.000'
select * from CustomerConsumptionDetails;
select count(*) from CustomerConsumptionDetails;
select count(*) from CustomerConsumptionDetailsID;
select max(Original) from CustomerConsumptionDetailsID ;
select Original from CustomerConsumptionDetailsID order by Original DESC;
select * from CustomerConsumptionDetailsID where Original='170422181223121871'
select * from CustomerConsumptionDetailsID
select * from CustomerConsumptionDetails
select Original from CustomerConsumptionDetailsID order by Original DESC;
select * from YUN_HIS20180420.dbo.H_FrontSK_Project where DJType='收费' and Keyid like '170316%'
select count(*) from bh20180420.dbo.CustomerConsumptionDetailsID where Original like '170316%';
85433
select Original from CustomerConsumptionDetailsID order by Original DESC;
select * from H_FrontSK_Paylst;
select * from CustomerConsumptionDetails
160531181551158902
select * from YUN_HIS20180420.dbo.H_FrontSK where dDT>'2016-05-31 00:00:00.000' and dDT<'2016-06-01 00:00:00.000';
select Original from bh20180420.dbo.CustomerConsumptionDetailsID where Original like '160531%';
select Keyid from YUN_HIS20180420.dbo.H_FrontSK_Project where Keyid like '160531%';
select * from YUN_HIS20180420.dbo.H_FrontSK_Project where Keyid ='160531094942498271'
select Original from bh20180420.dbo.MorHKeepAccountsDetailsID where Original like '160531%';
select * from YUN_HIS20180420.dbo.H_FrontSK_Project where Keyid like '170113%' and Keyid not in (select Original from bh20180420.dbo.CustomerConsumptionDetailsID where Original like '170113%' union select Original from bh20180420.dbo.MorHKeepAccountsDetailsID where Original like '170113%')
select count(*) from bh20180420.dbo.CustomerConsumptionInfoID where Original like '160531%';
select count(*) from H_FrontSK_Paylst;
select count(*) from ExcuteInfo
select count(Now) from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from CustomerConsumptionDetailsID
) as t
where rn>1
select * from CustomerConsumptionDetails
select * from CustomerConsumptionDetails where ID in(
select Now from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from CustomerConsumptionDetailsID
) as t
where rn>1 )
select * from CustomerConsumptionDetailsID
delete CustomerConsumptionDetailsID where ID in (
select Now from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from CustomerConsumptionDetailsID
) as t
where rn>1 )
select * from CustomerConsumptionInfo order by Date Desc
delete from CustomerConsumptionInfoID where Now in (
select Now from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from CustomerConsumptionInfoID
) as t
where rn>1 )
select * from ChargeForCertainDocInfo
select * from CustomerConsumptionDetails
select * from CustomerConsumptionInfoID order by Original Desc
select count(*) from CustomerConsumptionDetails
select * from CustomerConsumptionDetailsID
select * from CustomerConsumptionDetails where ID in(
select Now from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from CustomerConsumptionDetailsID
) as t
where rn>1 )
select * from MorHKeepAccountsDetailsID where ID in (
select Now from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from MorHKeepAccountsDetailsID
) as t
where rn>1 )
select * from MorHKeepAccountsInfo
select * from MorHKeepAccountsInfoID
delete from MorHKeepAccountsInfo where ID in(
select Now from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from MorHKeepAccountsInfoID
) as t
where rn>1 )
delete MorHKeepAccountsDetailsID where ID in (
select Now from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from MorHKeepAccountsDetailsID
) as t
where rn>1 )
select * from MorHKeepAccountsDetails
ID=
select * from MorHKeepAccountsDetailsID
Now
select * from MorHKeepAccountsDetails where ID in(
select Now from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from MorHKeepAccountsDetailsID
) as t
where rn>1 )
delete MorHKeepAccountsDetailsID where ID in (
select Now from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from MorHKeepAccountsDetailsID
) as t
where rn>1 )
delete from MorHKeepAccountsDetails where ID in (
'31522',
'31524',
'31526',
'35200',
'35201',
'35203',
'35204',
'35206',
'35212',
'35213',
'35215',
'35216',
'35217',
'35221',
'35232',
'35239',
'35257',
'35288',
'35301',
'35314',
'35334',
'35359',
'35408',
'35499',
'35505',
'35507',
'35519',
'35587',
'35589',
'35593',
'35595',
'35662',
'36203',
'36257',
'36346',
'36459',
'36461',
'36463',
'36465',
'36467',
'36469',
'36471',
'36477',
'36615',
'36948',
'37197',
'37199',
'37201',
'37466',
'37551',
'37553',
'37557',
'37561',
'37563',
'37685',
'37956',
'37958',
'37962',
'37964',
'37966',
'38169',
'38252',
'38254',
'38256',
'38258',
'38566',
'38568',
'38692',
'38694',
'38698',
'38798',
'38804',
'38808',
'38810',
'38922',
'39146')
delete from MorHKeepAccountsDetails
select * from ChargeForCertainDocInfo order by Date Desc
select * from ChargeForMHInfo order by Date Desc
select Original,count(*) from CustomerConsumptionDetailsID having count(*)>1
--select sKHZY from H_TMK;
select top 100 * from H_TMK where sSJ='15151398487';
select count(Now) from(
select ROW_NUMBER() over (partition by Original order by Original) rn, Original, Now from CustTest
) as t
where rn>1
select * from ReturnMoneyForCertainDoc
select * from ReturnMoneyForCertainDocID
select * from ReturnMoneyForMHInfo;
select * from ReturnMoneyForMHInfoID;
delete from ReturnMoneyForCertainDoc
delete from ReturnMoneyForCertainDocID
delete from ReturnMoneyForMHInfo;
delete from ReturnMoneyForMHInfoID;
DBCC CHECKIDENT('ReturnMoneyForCertainDoc', RESEED, 1)
DBCC CHECKIDENT('ReturnMoneyForCertainDocID', RESEED, 1)
DBCC CHECKIDENT('ReturnMoneyForMHInfo', RESEED, 1)
DBCC CHECKIDENT('ReturnMoneyForMHInfoID', RESEED, 1)
select * from H_FrontSK_Project where Keyid='121213162658269501'
select * from CardInfo where CardNumber='PZSN4QGW8A'
select * from MemberCardHandle where MemberCardNum='12101516'