首页 新闻 赞助 找找看

sql查询报派生表要个别名

0
悬赏园豆:100 [已解决问题] 解决于 2019-06-06 16:22

找了很久答案了,不成功,没用,试着加了在个个地方加了别名,无效,难受
如下sql
select number from(select sum(number) from
(select buy_uid, sum(number) from order_cclcoin GROUP BY sale_uidas
union ALL
select sale_uid, sum,(number) from order_cclcoin GROUP BY buy_uid)#as a
group by buy_uid as a);

崩溃。。。。

路过的一个小测试的主页 路过的一个小测试 | 初学一级 | 园豆:3
提问于:2019-06-05 17:59
< >
分享
最佳答案
0

select buy_uid, sum(number) from order_cclcoin GROUP BY sale_uidas
我能理解你两个子查询里GROUP BY的字段和展示列的字段不是一个是写错了吗?
你看这样对不对
select number from(
SELECT buy_uid,SUM(number) AS [number] from(
SELECT buy_uid, sum(number) AS [number] from order_cclcoin GROUP BY buy_uid
union ALL
select sale_uid, sum(number) AS [number] from order_cclcoin GROUP BY sale_uid
) AS A
) AS A
group by buy_uid

收获园豆:80
CaiJuHao | 菜鸟二级 |园豆:352 | 2019-06-05 19:09

是的,起别名的时候忘记删了

路过的一个小测试 | 园豆:3 (初学一级) | 2019-06-06 09:07

多谢了兄弟,但是执行后报语法错误

路过的一个小测试 | 园豆:3 (初学一级) | 2019-06-06 09:14

哦忘了,select number from(
SELECT buy_uid,SUM(number) AS [number] from( --你在这里做了聚合,最外没有聚合
SELECT buy_uid, sum(number) AS [number] from order_cclcoin GROUP BY buy_uid
union ALL
select sale_uid, sum(number) AS [number] from order_cclcoin GROUP BY sale_uid
) AS A group by buy_uid --GROUP BY应该放这
) AS A
下面大哥应该是对的

CaiJuHao | 园豆:352 (菜鸟二级) | 2019-06-06 09:27

@CaiJuHao: 谢谢,但是还是不行 泪奔

路过的一个小测试 | 园豆:3 (初学一级) | 2019-06-06 09:52

= =你是什么库,后面这个我Sql Server这边执行了一下是可以的啊。。。。(不是的话。。。你那边as 别名的时候不要加中括号试试[] )

CaiJuHao | 园豆:352 (菜鸟二级) | 2019-06-06 10:01

@CaiJuHao: mysql....

路过的一个小测试 | 园豆:3 (初学一级) | 2019-06-06 11:14

@打我队友: MYSQL我也试试了下。。你把别名的 中括号去掉试试。应该是可以的才对。

CaiJuHao | 园豆:352 (菜鸟二级) | 2019-06-06 11:18

可以,解决了,多谢,多谢

路过的一个小测试 | 园豆:3 (初学一级) | 2019-06-06 16:21
其他回答(2)
0

认真一点,两个子查询的结果都字段都不一样
还有,sum结果都不别名一个,在对其求和,字段都找不到,怎么求和呢?

select number from
(select sum(number) from
(
select buy_uid as buy_uid, sum(number) as number from order_cclcoin GROUP BY sale_uidas
union ALL
select sale_uid as buy_uid, sum(number) as number from order_cclcoin GROUP BY buy_uid
) as a
group by buy_uid)
as a;

收获园豆:10
程序员修炼之旅 | 园豆:776 (小虾三级) | 2019-06-05 21:09

select number from
(select sum(number) from
(
select buy_uid as buy_uid, sum(number) as number from order_cclcoin GROUP BY sale_uid
union ALL
select sale_uid as buy_uid, sum(number) as number from order_cclcoin GROUP BY buy_uid
) as a
group by number)
as a;照你的这么执行后报字段表中的未知的number,我检查了一下没不出来问题。。。大兄弟

@打我队友:
select number from
(select sum(number) as number from
(
select buy_uid as buy_uid, sum(number) as number from order_cclcoin GROUP BY sale_uidas
union ALL
select sale_uid as buy_uid, sum(number) as number from order_cclcoin GROUP BY buy_uid
) as a
group by buy_uid)
as a;

哦,在最后外层的一个子查询少些了一个as number
现在这个也该是对的了

支持(0) 反对(0) 程序员修炼之旅 | 园豆:776 (小虾三级) | 2019-06-08 20:58
0

select 1 from(
select sumNumber from
(select sale_uidas, sum(number) as sumNumber from order_cclcoin GROUP BY sale_uidas
union ALL select buy_uid from order_cclcoin GROUP BY buy_uid)
);

收获园豆:10
小光 | 园豆:1766 (小虾三级) | 2019-06-06 10:03
清除回答草稿
   您需要登录以后才能回答,未注册用户请先注册