use 

ChongQing_DongXuan
 
 
 

DECLARE  @T TABLE
(
    FEmpId BIGINT
) 
INSERT @T
 
select FEmpId FROM (
SELECT FEmpId,FSerialNo FROM TEmployeeSub 
WHERE FEmpId IN(SELECT FId FROM TEmployee WHERE FDeleted = 0 AND FState = 1)
GROUP BY FEmpiD,FSerialNo
) AS A
GROUP BY FEmpId
HAVING SUM(1) > 1 

DECLARE @T2 TABLE
(
    FEmpId BIGINT,
    FName VARCHAR(100),
    FEmpNo VARCHAR(100),
    FCompId BIGINT,
    FSerialNo VARCHAR(100)
)
INSERT @T2(FEmpId,FName,FEmpNo,FCompId,FSerialNo)
SELECT E.FId,E.FName,E.FNo,B.FCompId,B.FSerialNo
 FROM TEmployeeSub AS B,TEmployee AS E 
 WHERE B.FEmpId = E.FId 
 AND  B.FEmpId IN(SELECT FEmpId FROM @T)
  
 
SELECT * FROM @T2 AS T2
LEFT JOIN (
  
SELECT  B.FEmpId,A.FCompId,MAX(A.FBillTime) AS FBillTime,SUM(1) AS FBilLCount
FROM @T2 AS T2
INNER JOIN TConsumeBill AS A ON A.FCompId = T2.FCompId
INNER JOIN TConsumeItemEmployee AS B ON A.FBillId = B.FBillId AND B.FEmpId = T2.FEmpId  
GROUP BY B.FEmpId,A.FCompId
)  AS B
ON T2.FCompId = B.FCompId
AND T2.FEmpId = B.FEmpId


/*

SELECT * FROM TBizLog WHERE FFunction = 'WEmployee'
AND FContent LIKE '%4698602290348971910%'

UPDATE TEmployeeSub SET FSerialNo = '2215' WHERE FEmpId = 5428859158878592391

UPDATE TEmployeeSub SET FSerialNo = '011' WHERE FEmpId = 8902821357010071811
UPDATE TEmployeeSub SET FSerialNo = '01189' WHERE FEmpId = 6543057893431160017
UPDATE TEmployeeSub SET FSerialNo = '0066' WHERE FEmpId = 4917689208352438723
UPDATE TEmployeeSub SET FSerialNo = '0066' WHERE FEmpId = 4917689208352438723
UPDATE TEmployeeSub SET FSerialNo = '0066' WHERE FEmpId = 4917689208352438723
UPDATE TEmployeeSub SET FSerialNo = '0066' WHERE FEmpId = 4917689208352438723


 SELECT * FROM TConsumeBill WHERE FBillId IN(SELECT FBillId FROM TConsumeItemEmployee WHERE FEmpId = 8828571636704883736)
ORDER BY FBillTime desc

SELECT * FROM TEmployeeSub WHERE FSerialNo = '33'
 AND FCompId = 5492588808367651809

SELECT * FROM TEmployeeSub WHERE FEmpId = 5588579186301931253
 SELECT * FROM TEmployee WHERE FId = 4637243477248008939
 


SELECT * FROM TConsumeBill ORDER BY FBillTime desc

UPDATE A
  SET A.FSerialNo = B.FNo
 FROM TEmployeeSub AS A,TEmployee AS B
 WHERE A.FEmpId = B.FId
 
 

SELECT A.FBillTime FROM TConsumeItemEmployee AS B,TConsumeBill AS A
WHERE A.FBillId=  B.FBillId
AND B.FEmpId = 9030637958489593742
AND A.FCompId = 3472330288284041216
ORDER BY A.FBillTime desc

SELECT * FROM TEmployee WHERE FNo = '603'*/