2018-6-22

2018 年 6 月 22 日 by 沧海之树 In 工作

-------------------------------------------

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
--select * from StatusOfCustomer where CustomerID not in (select CustomerID from t)
update StatusOfCustomer set Status=0 where CustomerID not in (select CustomerID from t)

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
update SuccessOfCustomer set Success=0 where CustomerID not in (select CustomerID from t)

-----------------------------------

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
--select * from StatusOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt=1;
update StatusOfCustomer set Status=3 from StatusOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt=1;

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
update SuccessOfCustomer set Success=1 from SuccessOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt=1;

---------------------------------------------

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
update StatusOfCustomer set Status=4 from StatusOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt>1;

with t
as
(
select CustomerID,count(*) cnt from CustomerComeLog group by CustomerID
)
update SuccessOfCustomer set Success=1 from SuccessOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt>1;

---------------------------------------------

with t
as
(
select CustomerID,count(*) cnt from FinanceTotal group by CustomerID
)
update StatusOfCustomer set Status=5 from StatusOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt>0;

with t
as
(
select CustomerID,count(*) cnt from FinanceTotal group by CustomerID
)
update SuccessOfCustomer set Success=2 from SuccessOfCustomer a inner join t on t.CustomerID=a.CustomerID where cnt>0;

---------------------------------------------