34,838
社区成员
发帖
与我相关
我的任务
分享求一条SQL语句帮帮忙吧
3个表
会员表 客户ID 会员ID 会员类型ID 部门ID 注册日期
会员类型表 会员类型ID 会员类型0为充值卡1为次数卡 会员名字
充值记录表 会员ID 金额 充值的部门ID
因为他们有6个店所有充值的时候要考虑这个卡是否卡段充值了
会员卡 数量 本店销售金额 跨店销售金额(会员卡充值)
学生卡 10 180.00 0.00
会员卡储值 58 17650.00 4160.00
我现在取不出数量，因为关联后会员表和充值表示一对多关系，一分组求count取出来的数量是充值次数而不是卖卡数量。然后我就分2次取，第一次取销售卡，本店充值，别的店在本店充值。 第2次取只取会员表取卖出去的卡数量。 然后2个结果插入临时表。
取会员卡， 本店销售金额，跨店销售金额
select b.VipName VipName,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'
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 order by b.VipName
取数量
select b.VipName,count(b.VipName) 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 order by b.VipName
太麻烦了。 哪位大神能帮帮我
会员表 客户ID 会员ID 会员类型ID 部门ID 注册日期
会员类型表 会员类型ID 会员类型0为充值卡1为次数卡 会员名字
充值记录表 会员ID 金额 充值的部门ID
因为他们有6个店所有充值的时候要考虑这个卡是否卡段充值了
会员卡 数量 本店销售金额 跨店销售金额(会员卡充值)
学生卡 10 180.00 0.00
会员卡储值 58 17650.00 4160.00
我现在取不出数量，因为关联后会员表和充值表示一对多关系，一分组求count取出来的数量是充值次数而不是卖卡数量。然后我就分2次取，第一次取销售卡，本店充值，别的店在本店充值。 第2次取只取会员表取卖出去的卡数量。 然后2个结果插入临时表。
取会员卡， 本店销售金额，跨店销售金额
select b.VipName VipName,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'
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 order by b.VipName
取数量
select b.VipName,count(b.VipName) 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 order by b.VipName
太麻烦了。 哪位大神能帮帮我
...全文
请发表友善的回复…
发表回复
好奇都是要学的 2016-03-21
- 打赏
- 举报
我现在有个更难的需求。 你能帮我看看么
好奇都是要学的 2016-03-21
- 打赏
- 举报
恩， 效果是这样的。 能给我一个你的联系方式么。 QQ什么的， 跟你学习下SQL 不会的问你下。
Ginnnnnnnn 2016-03-16
- 打赏
- 举报
我自己猜了点数据，你看下是不是这个效果
会员表 客户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)好奇都是要学的 2016-03-16
- 打赏
- 举报
我给你发图片了，你不在了么？
Ginnnnnnnn 2016-03-15
- 打赏
- 举报
试下这个，我没有测试数据，只能猜
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好奇都是要学的 2016-03-15
- 打赏
- 举报
就是这样
Ginnnnnnnn 2016-03-15
- 打赏
- 举报
你能列出一下你想要的效果吗？我也猜不出你到底这个结果集要怎么弄？给点测试数据帮助理解
好奇都是要学的 2016-03-15
- 打赏
- 举报
那怎么求合计啊。 我按照你说的改了下。 确实可以 但是没有办法 group by
[type]
with rollup
中国风 2016-03-15
- 打赏
- 举报
如果要改写你最好表的栏位和表之间那些为一对多关系
结果集直接合并这样用
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
