34,594
社区成员
发帖
与我相关
我的任务
分享
会员表 客户ID 会员ID 会员类型ID 部门ID 注册日期
CREATE TABLE #Member (ID INT,CusID INT,MemberID INT,DepaID VARCHAR(50),createdate DATETIME)
会员类型表 会员类型ID 会员类型0为充值卡1为次数卡 会员名字
CREATE TABLE #MemberType (MemberID INT,TypeID INT,MemName NVARCHAR(50))
充值记录表 会员ID 金额 充值的部门ID
CREATE TABLE #Chongzhi (ID INT,MemberID INT,Amount MONEY,CashDepaID VARCHAR(50))
INSERT INTO #Member
( ID ,
CusID ,
MemberID ,
DepaID ,
createdate
)
VALUES ( 1 ,1 , 1 , '010101' ,'2016-03-01'),
( 2 ,2 , 2 , '010102' ,'2016-03-02'),
( 3 ,3 , 3 , '010102' ,'2016-02-01'),
( 4 ,4 , 4 , '010101' ,'2016-03-05')
INSERT INTO #MemberType
( MemberID, TypeID, MemName )
VALUES ( 1, 0, N'VIP卡'),
( 2, 0, N'会员卡'),
( 3, 0, N'金卡'),
( 4, 0, N'银卡')
INSERT INTO #Chongzhi
( ID, MemberID ,Amount, CashDepaID )
VALUES ( 1,1,310,'010101'),
( 2,2,200,'010102'),
( 3,3,506,'010101'),
( 4,4,150,'010102')
DECLARE @DepaID VARCHAR(50) = '010101'
SELECT CASE WHEN GROUPING(b.MemName) = 1 THEN '合计' ELSE b.MemName END AS [会员卡名称],
COUNT((CASE WHEN a.DepaID = @DepaID THEN a.ID ELSE NULL END)) AS [售卡数],
SUM((CASE WHEN a.DepaID = @DepaID THEN c.Amount ELSE 0 END)) AS [本店销售],
SUM((CASE WHEN a.DepaID <> @DepaID THEN c.Amount ELSE 0 END)) AS [别店在本店销售]
FROM #Member a
INNER JOIN #MemberType b ON a.MemberID = b.MemberID
LEFT JOIN #Chongzhi c ON a.MemberID = c.MemberID
WHERE a.createdate >= '2016-02-01'
AND a.createdate < '2016-04-01'
GROUP BY ROLLUP (b.MemName)
会员卡名称 售卡数 本店销售 别店在本店销售
-------------------------------------------------- ----------- --------------------- ---------------------
VIP卡 1 310.00 0.00
会员卡 0 0.00 200.00
金卡 0 0.00 506.00
银卡 1 150.00 0.00
合计 2 460.00 706.00 SELECT b.VipName VipName,
COUNT(DISTINCT a.ID) AS 数量
SUM(CASE WHEN a.deptno = c.DeptNo THEN c.RechAmount ELSE 0 END) AS price,
SUM(CASE WHEN a.deptno <> c.DeptNo THEN c.RechAmount ELSE 0 END) AS Kdprice
FROM Customer a
left join VIpType b on a.vipno=b.vipno
left join RechargeRecord c on a.customerNo=c.customerNo
WHERE a.deptno='010101'
GROUP BY b,VipName
SELECT b.VipName VipName
,e.VipCount
,SUM(c.RechAmount) price
,SUM(ISNULL(d.RechAmount , 0)) Kdprice
FROM Customer a
LEFT JOIN VIpType b ON a.vipno = b.vipno
LEFT JOIN RechargeRecord c ON a.customerNo = c.customerNo
AND a.deptno = '010101'
AND c.DeptNo = '010101'
LEFT JOIN RechargeRecord d ON a.customerNo = d.customerNo
AND a.deptno = '010101'
AND d.DeptNo <> '010101'
INNER JOIN (SELECT b.VipName
,COUNT(b.VipName) AS VipCount
FROM Customer a
LEFT JOIN VIpType b ON a.vipno = b.vipno
WHERE a.RegisterTime >= '2016-02-23 00:00:00'
AND a.RegisterTime < '2016-03-09 00:00:00'
AND b.VipType = 0
AND a.deptno = '010101'
GROUP BY b.VipName) AS e ON e.VipName=b.VipName
WHERE a.RegisterTime >= '2016-02-23 00:00:00'
AND a.RegisterTime < '2016-03-09 00:00:00'
AND b.VipType = 0
AND c.RechAmount IS NOT NULL
GROUP BY b.VipName,e.VipCount
ORDER BY b.VipName;
SELECT b.VipName
,isnull(SUM(b.VipCount),0) AS VipCount
,ISNULL(SUM(c.price) ,0) AS price
,isnull(SUM(c.Kdprice) ,0) AS Kdprice
FROM Customer a
LEFT JOIN (SELECT VipName,vipno,COUNT(*) AS VipCount FROM VIpType WHERE VipType = 0 GROUP BY VipName,vipno) AS b ON a.vipno = b.vipno
LEFT JOIN (SELECT customerNo,DeptNo,SUM(CASE WHEN DeptNo = '010101' THEN RechAmount ELSE 0 END) AS price,SUM(CASE WHEN DeptNo != '010101' THEN RechAmount ELSE 0 END) AS Kdprice FROM RechargeRecord GROUP BY customerNo,DeptNo) AS c ON a.customerNo = c.customerNo
WHERE a.RegisterTime >= '2016-02-23 00:00:00'
AND a.RegisterTime < '2016-03-09 00:00:00'
GROUP BY b.VipName
ORDER BY b.VipName