sql-tools/Import/颜艺/阿健.sql

SELECT * FROM TMember WHERE FChargeEmpId = 5456136924168793655
SELECT * FROM TMember WHERE FChargeEmpId = 5234427053624503843

SELECT * FROM TEmployee WHERE FName LIKE '%建%'
SELECT * FROM TEmployee WHERE FName LIKE '%伟%'

SELECT * FROM TMemberImport WHERE FMemNo IN(SELECT FMemNo FROM TMember_Export)

OR FMobilePhone IN(SELECT FMobile FROM TMember_Export WHERE FMobile != '')

SELECT * FROM TMemberIMport WHERE FCompId = 5146190082460629410

DROP TABLE TMember_Export


SELECT *INTO TMember_Export FROM TMember WHERE FMemId IN
(
SELECT B.FMemId
 FROM TMemberCourseImportEx AS A,TMemberImport AS B
WHERE ISNULL(A.FName,'') = B.FName
 AND ISNULL(A.FMobilePhone,'') = B.FMobilePhone 
 AND A.FCompId = B.FCompId 
 AND A. BMMC00 = '颜艺二店' AND A.QCRYMC = '阿建'
)
OR FChargeEmpId = 5456136924168793655

DROP TABLE TMember_Export2

SELECT *INTO TMember_Export2 FROM TMember WHERE FMemId IN
(
SELECT B.FMemId
 FROM TMemberCourseImportEx AS A,TMemberImport AS B
WHERE ISNULL(A.FName,'') = B.FName
 AND ISNULL(A.FMobilePhone,'') = B.FMobilePhone 
 AND A.FCompId = B.FCompId 
 AND A. BMMC00 = '颜艺二店' AND A.QCRYMC = '阿伟'
)
OR FChargeEmpId = 5234427053624503843


SELECT *INTO TMember_Export2 FROM TMember WHERE FMemId IN(SELECT B.FMemId
 FROM TMemberCourseImportEx AS A,TMemberImport AS B
WHERE ISNULL(A.FName,'') = B.FName
 AND ISNULL(A.FMobilePhone,'') = B.FMobilePhone 
 AND A.FCompId = B.FCompId 
 AND A. BMMC00 = '颜艺二店' AND A.QCRYMC = '阿伟')
OR FChargeEmpId = 5234427053624503843

SELECT * FROM TMember_Export2

UPDATE TMember SET FMemo = FMemo + '[阿建的会员，已转]',FState = 4
WHERE FMemId IN(SELECT FMemId FROM TMember_Export)
AND FState != 4

SELECT * FROM TMemberCard  WHERE FMemId IN(SELECT FMemId FROM TMember_Export2)
AND FState != 1
SELECT * FROM TMember   WHERE FMemId IN(SELECT FMemId FROM TMember_Export2)
AND FState != 1

DELETE TMember_Export2 WHERE FMemId IN(SELECT FMemId FROM TMember_Export)

SELECT * FROM TMemberMerge WHERE FSourceMemId IN(SELECT FMemId FROM TMember_Export)
SELECT * FROM TMemberMerge WHERE FTargetMemId IN(SELECT FMemId FROM TMember_Export)
SELECT * FROM TMember WHERE FMemId = 5051759679318127977

SELECT * FROM TMemberCard WHERE FMemId = 5051759679318127977
SELECT * FROM TMemberCardaCCOUNT WHERE FMemId = 5051759679318127977

UPDATE TMemberCard SET FState = 3 WHERE FMemID = 5051759679318127977
UPDATE TMember SET FState = 3 WHERE FMemID = 5051759679318127977


UPDATE TMemberCard SET FMemo = FMemo + '[二店会员，已转]',FState = CASE WHEN FState = 1 THEN 4 ELSE FState End
WHERE FCompId IN(5146190082460629410)
AND FMemo NOT LIKE  '%[二店会员卡，已转]%'

UPDATE TMember  SET FMemo = FMemo + '[二店会员，已转]',FState = CASE WHEN FState = 1 THEN 4 ELSE FState End
WHERE FCompId = 5146190082460629410
AND FMemo NOT LIKE  '%[二店会员卡，已转]%'

UPDATE TMemberCourse SET FMemo = FMemo + '[二店会员卡，已转]',FState = CASE WHEN FState = 1 THEN 4 ELSE FState End
WHERE FCompId = 5146190082460629410
AND FMemo NOT LIKE  '%[二店会员卡，已转]%'

UPDATE TMember SET FMemo = REPLACE(FMemo,'[二店会员，已转]','')
UPDATE TMemberCARD SET FMemo = REPLACE(FMemo,'[二店会员，已转]','')
UPDATE TMemberCourse SET FMemo = REPLACE(FMemo,'[二店会员，已转]','')


UPDATE TMember SET FMemo = FMemo + ''


SELECT * FROM TMemberCourse WHERE FMemId IN(SELECT FMemId FROM TMember_Export)
AND FCompId != 5146190082460629410

SELECT dbo.FNNewId(),FCompId,dbo.FNNewId(),MIN(FCardNo),dbo.FNNewId(),MIN(FCardNo),ISNULL(FName,''), 0 ,ISNULL(FMobilePhone,''),MIN(FCreateDate), Dateadd(yy,100,MIN(FCreateDate)),
    0,0,0,0,0
    FROM TMemberCourseImportEx
WHERE  BMMC00 = '颜艺二店'
    --WHERE FCardNo NOT IN(select FMemNo FROM TMember)

AND FMemNo IN(SELECT FMemNo)
    GROUP BY ISNULL(FName,''),FMobilePhone,FCompId

SELECT * FROM TUser

SELECT name FROM syscolumns 

SELECT * FROM TMemberSaleBill WHERE FBillId = 4701049931601903228

SELECT * FROM TMember WHERE FMemNo = '001550848'


SELECT * FROM TMemberMerge WHERE FSourceMemId IN(SELECT FMemId FROM TMember_Export)
SELECT * FROM TMemberMerge WHERE FTargetMemId IN(SELECT FMemId FROM TMember_Export)


SELECT FMemNo as 卡号,FName as 姓名,FMobilePhone AS 手机
 FROM TMember_Export2 
 
 



SELECT *INTO TMember_Export3 FROM TMember 
WHERE FChargeEmpId IN
(
 SELECT FId FROM TEmployee WHERE FNo IN('1007','1010','1009')
)
OR FMemId IN(

SELECT FMemId FROM TMemberCOurseImportEx 
WHERE 1=1
AND FCompId = 5146190082460629410
AND QCRYMC IN( '三郎','丁兆元','')
)


UPDATE A
   SET A.FMemo = '[丁姐，三郎，张世军的会员，已经停用]',
       A.FState = CASE WHEN A.FState = 1 THEN 4 ELSE A.FState End
 FROM TMember AS A,db108.ZiBo_YanYi.dbo.TMember_Export3 AS B
WHERE A.FMemId = B.FMemId

   
UPDATE A
   SET A.FMemo = '[丁姐，三郎，张世军的会员，已经停用]',
       A.FState = CASE WHEN A.FState = 1 THEN 4 ELSE A.FState End
 FROM TMemberCard AS A,db108.ZiBo_YanYi.dbo.TMember_Export3 AS B
WHERE A.FMemId = B.FMemId

UPDATE A
   SET A.FMemo = '[丁姐，三郎，张世军的会员，已经停用]',
       A.FState = CASE WHEN A.FState = 1 THEN 4 ELSE A.FState End
 FROM TMemberCourse AS A,db108.ZiBo_YanYi.dbo.TMember_Export3 AS B
WHERE A.FMemId = B.FMemId


SELECT * FROM TMember WHERE FMemId IN(SELECT FMemId FROM TMember_Export3)

UPDATE A
   SET A.FMemo = REPLACE(A.FMemo,'[这是二店的会员，只能在禅意消费]','[二店丁姐，三郎，张世军的会员，可以在本店使用]'),
       A.FState = CASE WHEN A.FState =4 THEN 1 ELSE A.FState END
  FROM TMember AS A,TMember_Export3 AS B
 WHERE A.FMemId = B.FMemId
 
 
UPDATE A
   SET A.FMemo = REPLACE(A.FMemo,'[这是二店的会员，只能在禅意消费]','[二店丁姐，三郎，张世军的会员，可以在本店使用]'),
       A.FState = CASE WHEN A.FState =4 THEN 1 ELSE A.FState END
  FROM TMemberCard AS A,TMember_Export3 AS B
 WHERE A.FMemId = B.FMemId
 
 
UPDATE A
   SET A.FMemo = REPLACE(A.FMemo,'[这是二店的会员，只能在禅意消费]','[二店丁姐，三郎，张世军的会员，可以在本店使用]'),
       A.FState = CASE WHEN A.FState =4 THEN 1 ELSE A.FState END
  FROM TMemberCourse AS A,TMember_Export3 AS B
 WHERE A.FMemId = B.FMemId