求一条SQL语句帮帮忙吧

好奇都是要学的 2016-03-15 09:07:53
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

太麻烦了。 哪位大神能帮帮我
...全文
231 9 打赏 收藏 转发到动态 举报
AI 作业
写回复
用AI写文章
9 条回复
切换为时间正序
请发表友善的回复…
发表回复
  • 打赏
  • 举报
回复
引用 4 楼 KanzakiOrange 的回复:
你能列出一下你想要的效果吗?我也猜不出你到底这个结果集要怎么弄?给点测试数据帮助理解
我现在有个更难的需求。 你能帮我看看么
  • 打赏
  • 举报
回复
引用 7 楼 KanzakiOrange 的回复:
我自己猜了点数据,你看下是不是这个效果

会员表 客户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
恩, 效果是这样的。 能给我一个你的联系方式么。 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)
会员卡名称 售卡数 本店销售 别店在本店销售 -------------------------------------------------- ----------- --------------------- --------------------- 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
  • 打赏
  • 举报
回复
引用 4 楼 KanzakiOrange 的回复:
你能列出一下你想要的效果吗?我也猜不出你到底这个结果集要怎么弄?给点测试数据帮助理解
我给你发图片了,你不在了么?
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
  • 打赏
  • 举报
回复
引用 4 楼 KanzakiOrange 的回复:
你能列出一下你想要的效果吗?我也猜不出你到底这个结果集要怎么弄?给点测试数据帮助理解


就是这样
Ginnnnnnnn 2016-03-15
  • 打赏
  • 举报
回复
你能列出一下你想要的效果吗?我也猜不出你到底这个结果集要怎么弄?给点测试数据帮助理解
  • 打赏
  • 举报
回复
引用 1 楼 KanzakiOrange 的回复:
试下这个,我没有测试数据,只能猜
 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
那怎么求合计啊。 我按照你说的改了下。 确实可以 但是没有办法 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

34,838

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧