求一条SQL语句帮帮忙吧

2016-03-15 09:07:53 295 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

解决linux 2.5G网卡驱动问题解决linux 2.5G网卡驱动问题关键词：2.5G网卡驱动，ubuntu，安装，2.5G Ethernet LINUX driver，Realtek网卡驱动由于2.5G网卡较新，各linux发行版的通用驱动已不适用，新装的系统会出现无法上网问题，由于国内网络环境去Realtek官网很难下载到驱动文件，故本文提供github上的2.5G Ethernet LINUX driver r8125 for kernel up to 5.6的版本驱动文件r8125-9.003.05.tar.bz2各位可以自阅读详情

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

太麻烦了。哪位大神能帮帮我

...全文

ICT调试技巧：从基础到实战的全面指南本文提供了一份全面的ICT调试技巧指南，涵盖从基础认知到实战应用的全过程。详细解析了ICT设备硬件结构、程序文件构成，并重点讲解了调试界面的核心参数与高效快捷键。通过跳线、电阻、电容及二极管等常见元器件的实战调试案例，分享了参数设置要点与典型问题解决方案，帮助工程师快速掌握ICT调试核心技能，提升测试效率与准确性。阅读详情

好奇都是要学的

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

好奇都是要学的 2016-03-21

打赏
举报

引用 4 楼 KanzakiOrange 的回复:

你能列出一下你想要的效果吗？我也猜不出你到底这个结果集要怎么弄？给点测试数据帮助理解

我现在有个更难的需求。你能帮我看看么

好奇都是要学的 2016-03-21

打赏
举报

引用 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

好奇都是要学的 2016-03-16

打赏
举报

引用 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

好奇都是要学的 2016-03-15

打赏
举报

引用 4 楼 KanzakiOrange 的回复:

你能列出一下你想要的效果吗？我也猜不出你到底这个结果集要怎么弄？给点测试数据帮助理解

就是这样

Ginnnnnnnn 2016-03-15

打赏
举报

你能列出一下你想要的效果吗？我也猜不出你到底这个结果集要怎么弄？给点测试数据帮助理解

好奇都是要学的 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

求一条SQL语句帮帮忙吧

相关推荐