sql统计查询问题

[q476604254]

假设源数据是这个样子。
declare @send table(
[serverId] [int] NOT NULL,
[actorId] [bigint] NOT NULL,
[uuid] [bigint] NOT NULL,
[redPackageId] [int] NOT NULL,
[WriteTime] [datetime] NOT NULL
)

insert @send select 1,100,1,2,'2017/12/12 17:45:27'
union all select 1,100,2,2,'2017/12/12 17:45:27'
union all select 1,101,3,2,'2017/12/12 17:45:27'
union all select 1,101,4,2,'2017/12/12 17:45:27'
union all select 2,102,5,2,'2017/12/12 17:45:27'
union all select 2,102,6,2,'2017/12/12 17:45:27'
union all select 2,102,7,2,'2017/12/12 17:45:27'
union all select 3,103,9,2,'2017/12/12 17:45:27'



declare @get table(
[serverId] [int] NOT NULL,
[actorId] [bigint] NOT NULL,
[uuid] [bigint] NOT NULL,
[type] [int] NOT NULL,
[redPackageId] [int] NOT NULL,
[state] [int] NOT NULL,
[money] [int] NOT NULL,
[WriteTime] [datetime] NOT NULL
)

insert @get 1,100,1,1,0,1,1000,'2017/12/12 17:45:27'
union all select 1,100,2,1,0,1,1000,'2017/12/12 17:45:27'
union all select 1,101,2,1,0,1,1200,'2017/12/12 17:45:27'
union all select 1,101,2,1,0,1,1300,'2017/12/12 17:45:27'
union all select 1,101,2,1,0,1,1400,'2017/12/12 17:45:27'
union all select 1,101,6,1,0,1,1000,'2017/12/12 17:45:27'
union all select 2,102,7,1,0,1,1000,'2017/12/12 17:45:27'
union all select 2,102,8,1,0,1,1000,'2017/12/12 17:45:27'
union all select 2,102,9,1,0,1,1000,'2017/12/12 17:45:27'
union all select 3,104,9,1,0,1,1000,'2017/12/12 17:45:27'

就是统计send 和get 每个actor的出现次数，汇总在一个表里面，在根据send总次数和get总次数排序
最后的结果要如何才能成这样？


我自己的思路是分开统计了在把这个表合起来，，但是我不知道如何把这两个表合成下面的样子


希望各位大神能给点帮助，啥实现方法都行，只要能输出成最后的样子。想了一天也没找到办法。半路出家的db，sql太菜了。

[吉普赛的歌]

SET NOCOUNT ON
declare @send table(
[serverId] [int] NOT NULL,
[actorId] [bigint] NOT NULL,
[uuid] [bigint] NOT NULL,
[redPackageId] [int] NOT NULL,
[WriteTime] [datetime] NOT NULL
) 
insert @send select 1,100,1,2,'2017/12/12 17:45:27'
union all select 1,100,2,2,'2017/12/12 17:45:27'
union all select 1,101,3,2,'2017/12/12 17:45:27'
union all select 1,101,4,2,'2017/12/12 17:45:27'
union all select 2,102,5,2,'2017/12/12 17:45:27'
union all select 2,102,6,2,'2017/12/12 17:45:27'
union all select 2,102,7,2,'2017/12/12 17:45:27'
union all select 3,103,9,2,'2017/12/12 17:45:27'
declare @get table(
[serverId] [int] NOT NULL,
[actorId] [bigint] NOT NULL,
[uuid] [bigint] NOT NULL,
[type] [int] NOT NULL,	
[redPackageId] [int] NOT NULL,
[state] [int] NOT NULL,
[money] [int] NOT NULL,
[WriteTime] [datetime] NOT NULL
) 
insert @get select 1,100,1,1,0,1,1000,'2017/12/12 17:45:27'
union all select 1,100,2,1,0,1,1000,'2017/12/12 17:45:27'
union all select 1,101,2,1,0,1,1200,'2017/12/12 17:45:27'
union all select 1,101,2,1,0,1,1300,'2017/12/12 17:45:27'
union all select 1,101,2,1,0,1,1400,'2017/12/12 17:45:27'
union all select 1,101,6,1,0,1,1000,'2017/12/12 17:45:27'
union all select 2,102,7,1,0,1,1000,'2017/12/12 17:45:27'
union all select 2,102,8,1,0,1,1000,'2017/12/12 17:45:27'
union all select 2,102,9,1,0,1,1000,'2017/12/12 17:45:27'
union all select 3,104,9,1,0,1,1000,'2017/12/12 17:45:27'

;WITH cte1 AS(
	SELECT t.actorId,COUNT(1) AS sendNumber 
	FROM @send t
	GROUP BY t.actorId
)
,cte2 AS (
	SELECT t.actorId,COUNT(1) AS getNumber 
	FROM @get t
	GROUP BY t.actorId
)
SELECT 
	CASE WHEN cte1.actorId IS NOT NULL THEN cte1.actorId ELSE cte2.actorId END
	AS actorId
	,ISNULL(cte1.sendNumber,0) AS sendNumber
	,ISNULL(cte2.getNumber,0) AS getNumber
FROM cte1 FULL JOIN cte2 ON cte1.actorId=cte2.actorId
/*
actorId              sendNumber  getNumber
-------------------- ----------- -----------
100                  2           2
101                  2           4
102                  3           3
103                  1           0
104                  0           1
*/

[二月十六]

提问方式非常好

declare @send table(
[serverId] [int] NOT NULL,
[actorId] [bigint] NOT NULL,
[uuid] [bigint] NOT NULL,
[redPackageId] [int] NOT NULL,
[WriteTime] [datetime] NOT NULL
) 
 
insert @send select 1,100,1,2,'2017/12/12 17:45:27'
union all select 1,100,2,2,'2017/12/12 17:45:27'
union all select 1,101,3,2,'2017/12/12 17:45:27'
union all select 1,101,4,2,'2017/12/12 17:45:27'
union all select 2,102,5,2,'2017/12/12 17:45:27'
union all select 2,102,6,2,'2017/12/12 17:45:27'
union all select 2,102,7,2,'2017/12/12 17:45:27'
union all select 3,103,9,2,'2017/12/12 17:45:27'



declare @get table(
[serverId] [int] NOT NULL,
[actorId] [bigint] NOT NULL,
[uuid] [bigint] NOT NULL,
[type] [int] NOT NULL,	
[redPackageId] [int] NOT NULL,
[state] [int] NOT NULL,
[money] [int] NOT NULL,
[WriteTime] [datetime] NOT NULL
) 
 
insert @get SELECT 1,100,1,1,0,1,1000,'2017/12/12 17:45:27'
union all select 1,100,2,1,0,1,1000,'2017/12/12 17:45:27'
union all select 1,101,2,1,0,1,1200,'2017/12/12 17:45:27'
union all select 1,101,2,1,0,1,1300,'2017/12/12 17:45:27'
union all select 1,101,2,1,0,1,1400,'2017/12/12 17:45:27'
union all select 1,101,6,1,0,1,1000,'2017/12/12 17:45:27'
union all select 2,102,7,1,0,1,1000,'2017/12/12 17:45:27'
union all select 2,102,8,1,0,1,1000,'2017/12/12 17:45:27'
union all select 2,102,9,1,0,1,1000,'2017/12/12 17:45:27'
union all select 3,104,9,1,0,1,1000,'2017/12/12 17:45:27'


SELECT  CASE WHEN get.actorId IS NOT NULL THEN get.actorId
             ELSE [send].actorId
        END AS actorId ,
        ISNULL(getNumber,0) AS getNumber ,
        ISNULL(sendNumber,0) AS sendNumber
FROM    ( SELECT    actorId ,
                    COUNT(1) AS getNumber
          FROM      @get
          GROUP BY  actorId
        ) get
        FULL JOIN ( SELECT  actorId ,
                            COUNT(1) AS sendNumber
                    FROM    @send
                    GROUP BY actorId
                  ) send ON send.actorId = get.actorId
ORDER BY getNumber + sendNumber DESC

[q476604254]

谢谢大佬，就是要这个效果了，我知道怎么写with AS但是不会后面的case when所以自己写的查询语言要不是缺数据就是数据出错有很多行。

[q476604254]

现在出现了一个新问题，，我本地机器上跑没问题。但是过程写到服务器上就报错了

加;就显示 ';' 附近有语法错误。
去掉就显示 如果此语句是公用表表达式或 xmlnamespaces 子句，那么前一个语句必须以分号结尾。

[听雨停了]

引用 4 楼 q476604254 的回复:

现在出现了一个新问题，，我本地机器上跑没问题。但是过程写到服务器上就报错了 加;就显示 ';' 附近有语法错误。 去掉就显示 如果此语句是公用表表达式或 xmlnamespaces 子句，那么前一个语句必须以分号结尾。

既然你搞不定这个cte，那就不要用cte了，直接把每个cte的结果插入一个临时表，在过程结尾删除掉这些就行了，这样绝对不会报错。过程名称就没必要打马赛克了

[q476604254]

引用 5 楼 qq_37170555 的回复:

[quote=引用 4 楼 q476604254 的回复:] 现在出现了一个新问题，，我本地机器上跑没问题。但是过程写到服务器上就报错了 加;就显示 ';' 附近有语法错误。 去掉就显示 如果此语句是公用表表达式或 xmlnamespaces 子句，那么前一个语句必须以分号结尾。

既然你搞不定这个cte，那就不要用cte了，直接把每个cte的结果插入一个临时表，在过程结尾删除掉这些就行了，这样绝对不会报错。过程名称就没必要打马赛克了[/quote] 就这个sql在我本地的机器上跑没问题 SQLserver 2014 但是服务器上是2005的。我不确定是我改写的问题还是版本的问题

[听雨停了]

引用 6 楼 q476604254 的回复:

[quote=引用 5 楼 qq_37170555 的回复:] [quote=引用 4 楼 q476604254 的回复:] 现在出现了一个新问题，，我本地机器上跑没问题。但是过程写到服务器上就报错了 加;就显示 ';' 附近有语法错误。 去掉就显示 如果此语句是公用表表达式或 xmlnamespaces 子句，那么前一个语句必须以分号结尾。

既然你搞不定这个cte，那就不要用cte了，直接把每个cte的结果插入一个临时表，在过程结尾删除掉这些就行了，这样绝对不会报错。过程名称就没必要打马赛克了[/quote] 就这个sql在我本地的机器上跑没问题 SQLserver 2014 但是服务器上是2005的。我不确定是我改写的问题还是版本的问题[/quote] 2005还不支持cte，还是用临时表吧，稳稳的。不是你改写的问题，是版本不支持的问题。你改成临时表就没问题了。

[q476604254]

我该用#2 楼的子查询的方式了。。。。 应该是可以了，写进去了没报错。等待他们写数据进来了在测试看过程有问题没。