sql-tools/Import/首选/SQLQuery3.sql

SELECT *INTO #Item FROM TItem WHERE (FName LIKE 
'%<EFBFBD><EFBFBD><EFBFBD><EFBFBD>%' 
OR FName LIKE '%<EFBFBD><EFBFBD><EFBFBD><EFBFBD>%'
OR FName LIKE '%<EFBFBD><EFBFBD><EFBFBD><EFBFBD>%'
OR FName LIKE '%<EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD><EFBFBD>%'
OR FName LIKE '%A+D%'
OR FName LIKE '%<EFBFBD>޸<EFBFBD>˹%'
OR FName LIKE '%OVO%'
)


SELECT C.FAbbr AS <EFBFBD>ŵ<EFBFBD>,SUM(1) AS <EFBFBD><EFBFBD><EFBFBD><EFBFBD> FROM TConsumeBill AS A,TCompany AS C
WHERE FState = 1
AND A.FCompId = C.FId
AND FBillTime < '2016-01-01' 
GROUP BY FCompId,C.FAbbr

--
DROP TABLE #Hist
DROP TABLE #ItemCust


SELECT *INTO #Hist 
FROM TMemberTransHist 
WHERE FContentId IN (SELECT FId FROM #Item) AND FTime < '2016-01-01'

CREATE TABLE #ItemCust
(
    FCompId BIGINT,
    FItemId BIGINT,
    FMemCount INT,
    FVisitorCount INT
)
INSERT #ItemCust(FCompId,FItemId,FMemCount,FVisitorCount)
SELECT C.FId,I.FId,0,0
FROM TCompany AS C,#Item AS I
WHERE C.FId > 0

UPDATE A
   SET A.FMemCount = B.FMemCount
  FROM #ItemCust AS A,
  (

SELECT FCompId,FContentId AS FItemId,SUM(1) AS FMemCount
FROM 
(
SELECT FContentId,FMemId,FCompId FROM #Hist
GROUP BY  FContentId,FMemId,FCompId
) AS A
GROUP BY FContentId,FCompId
) AS B
WHERE A.FCompID = B.FCompId
AND A.FItemId = B.FItemId



UPDATE A
   SET A.FVisitorCount = B.FCount
FROM #ItemCust AS A,(
SELECT FCompId,FItemID,SUM(1) AS FCount
FROM (

SELECT A.FCompId,A.FBillId,B.FItemId 
FROM TConsumeItem AS B,TConsumeBill AS A
 WHERE B.FItemId IN (SELECT FId FROM #Item)
AND A.FState = 1 
AND A.FMemId = 0 
AND A.FBillTime < '2016-01-01'
AND A.FBillId = B.FBillId
GROUP BY FCompId,A.FBillId,B.FItemId 

) AS T
GROUP BY FItemID,FCompId
) AS B 
WHERE A.FItemId = B.FItemId
and A.FCompId = B.FCompId


SELECT A.FName AS <EFBFBD><EFBFBD>Ŀ<EFBFBD><EFBFBD><EFBFBD><EFBFBD>,B.FMemCount AS <EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD>,FVisitorCount AS ɢ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> 
FROM #Cust AS B,TItem AS A
WHERE B.FItemId = A.FId


SELECT C.FAbbr AS <EFBFBD>ŵ<EFBFBD>,I.FName AS <EFBFBD><EFBFBD>Ŀ,A.FMemCount AS <EFBFBD><EFBFBD>Ա<EFBFBD><EFBFBD>,FVisitorCount AS ɢ<EFBFBD><EFBFBD><EFBFBD><EFBFBD> 
 FROM #ItemCust AS A
 INNER JOIN TCompany AS C ON C.FId = A.FCompId
 INNER JOIN TItem AS I ON I.FId = A.FItemId
 

SELECT C.FAbbr AS <EFBFBD>ŵ<EFBFBD>, A.FName as <EFBFBD><EFBFBD><EFBFBD><EFBFBD>, FMobilePhone AS <EFBFBD>绰 ,dbo.FNNumToStr(FAmount) AS <EFBFBD><EFBFBD><EFBFBD><EFBFBD>,FTimes AS <EFBFBD><EFBFBD><EFBFBD><EFBFBD>
FROM #Member AS A,TCompany AS C
WHERE C.FId = A.FCompId
ORDER BY C.FNo,faMOUNT desc 

update #Cust SET FVisitorCount = 0


SELECT * FROM #Hist

DROP TABLE #Member

SELECT M.FMemId,M.FName,M.FMobilePhone,M.FCompId,SUM(FAmount) AS FAmount,CAST(0 AS INT) AS FTimes 
INTO #Member 
FROM TMemberTransHist AS H,TMember AS M
WHERE M.FMemId IN (SELECT FMemId FROM #Hist )
AND M.FMemId = H.FMemId
AND H.FBillType = 'XF'
GROUP BY M.FMemId,M.FName,M.FMobilePhone,M.FCompId


UPDATE A
   SET A.FTimes = B.FTimes
  FROM #Member AS A,(
  
  SELECT A.FMemId,SUM(1) AS FTimes
  FROM (
  
SELECT H.FMemId,H.FTransId  
FROM TMemberTransHist AS H 
WHERE H.FMemId IN (SELECT FMemId FROM #Hist )
AND H.FBillType = 'XF'
AND H.FTime < '2016-01-01'
GROUP BY H.FMemId ,H.FTransId  

) AS A
GROUP BY FMemId



) AS B
WHERE A.FMemId = B.FMemId


SELECT FMemId FROM TMemberTransHist WHERE FCOntentId = 4695386159011280002
group by FMemId