sql 分组统计

[FenHui]

select username,count(*) as phoneMoney from pu_fyb group by username having count(*)>0 order by username

select username,count(*) as phonenum from f_extsys group by username having count(*)>0 order by username

select username, count(*) as phonepause from pu_fyb group by username,pause having count(*)>=0 and pause=1

select count(*)as phonenum,username from f_extsys where
phonenum not in(select eps400 from pu_fyb group by username,eps400)
and username!='00' group by username,phonenum order by username

求这4个语句怎么合并一个sql语句啊

[fcuandy]

我看这四条语句没有必条合成一条语句。

[csdyyr]

union all

[FenHui]

[Quote=引用 1 楼 fcuandy 的回复:]
我看这四条语句没有必条合成一条语句。
[/Quote]

有必要啊
我想把每个语句的输出结果生成一个表格啊

[水族杰纶]

--TRY
select username,count(*) as phoneMoney,(select count(*) as phonenum from f_extsys group by username having count(*)>0),
(select  count(*) as phonepause  from pu_fyb group by username,pause having count(*)>=0 and pause=1 ),
(select count(*)as phonenum,username from f_extsys where phonenum not in(select eps400 from pu_fyb group by username,eps400) and  username!='00' group by username,phonenum) 
 from pu_fyb group by username having count(*)>0 order by username 

[FenHui]

[Quote=引用 2 楼 csdyyr 的回复:]
SQL codeunion all
[/Quote]

不能用啊
老是提示 在关键字 'union' 附近有语法错误。

[FenHui]

[Quote=引用 4 楼 wufeng4552 的回复:]
SQL code--TRY
select username,count(*) as phoneMoney,(select count(*) as phonenum from f_extsys group by username having count(*)>0),
(select count(*) as phonepause from pu_fyb group by username,pause having count(*)>=0 and pause=1 ),
(select count(*)as phonenum,username from f_extsys where phonenum not in(select eps400 from pu_fyb group by username,eps400) and username!='00' group by user…
[/Quote]

当没有用 EXISTS 引入子查询时，在选择列表中只能指定一个表达式。不能返回多个字段啊

[songxianping]

select username,count(*) as phoneMoney from pu_fyb group by username having count(*)>0 order by username
union all
select username,count(*) as phonenum from f_extsys group by username having count(*)>0 order by username
union all
select username, count(*) as phonepause from pu_fyb group by username,pause having count(*)>=0 and pause=1
union all
select count(*)as phonenum,username from f_extsys where
phonenum not in(select eps400 from pu_fyb group by username,eps400)
and username!='00' group by username,phonenum order by username


注意使用union all ,查詢的字段必需相同, 如上面的語句,查詢的字段都是兩個

[csdyyr]

select username,count(*) as phoneMoney from pu_fyb group by username having count(*)>0 
union all
select username,count(*) as phonenum from f_extsys group by username having count(*)>0 
union all
select username, count(*) as phonepause  from pu_fyb group by username,pause having count(*)>=0 and pause=1
union all
select username,count(*) as phonenum from f_extsys where
phonenum not in(select eps400 from pu_fyb group by username,eps400)
and  username!='00' group by username,phonenum 
order by username

[水族杰纶]

select username,count(*) as phoneMoney from pu_fyb group by username having count(*)>0 
union all
select username,count(*) as phonenum from f_extsys group by username having count(*)>0 
union all
select username, count(*) as phonepause  from pu_fyb group by username,pause having count(*)>=0 and pause=1 
union all
select username, count(*)as phonenum from f_extsys where phonenum not in(select eps400 from pu_fyb group by username,eps400) and  username!='00' group by username,phonenum

[babygehui]

select username,sum(phoneMoney) as phoneMoney,sum(phonenum) as phonenum,sum(phonepause) as phonepause,sum(phonenum1) as phonenum1 from(
select username,count(*) as phoneMoney,0 as phonenum,0 as phonepause,0 as phonenum1 from pu_fyb group by username having count(*)>0 order by username
union all
select username,0 as phoneMoney,count(*) as phonenum,0 as phonepause,0 as phonenum1 from f_extsys group by username having count(*)>0 order by username
union all
select username, 0 as phoneMoney,0 as phonenum,count(*) as phonepause ,0 as phonenum1 from pu_fyb group by username,pause having count(*)>=0 and pause=1

select username,0 as phoneMoney,0 as phonenum,0 as phonepause,count(*)as phonenum1 from f_extsys where
phonenum not in(select eps400 from pu_fyb group by username,eps400)
and username!='00' group by username,phonenum order by username )a
group by username

[czx33859066]

select username,count(*) as phoneMoney from pu_fyb group by username having count(*)>0 
union all
select username,count(*) as phonenum from f_extsys group by username having count(*)>0 
union all
select username, count(*) as phonepause  from pu_fyb group by username,pause having count(*)>=0 and pause=1
union all
select username,count(*) as phonenum from f_extsys where
phonenum not in(select eps400 from pu_fyb group by username,eps400)
and  username!='00' group by username,phonenum 
order by username

[FenHui]

username phonemoney
0110 27
0110 27
02 9
02 10
02 1
02 1
0201 1
0201 2
0201 1
如果用union all 结果是这样的

我想要的效果是
username phonemoney phonenum phonepause
0110 27 27 9
02 9 10 1

这要怎么实现啊

[FenHui]

各位大哥 大姐 帮个忙啊

[babygehui]

前边加sum 呀
select username,sum(phoneMoney) as phoneMoney,sum(phonenum) as phonenum,sum(phonepause) as phonepause,sum(phonenum1) as phonenum1 from(
select username,count(*) as phoneMoney,0 as phonenum,0 as phonepause,0 as phonenum1 from pu_fyb group by username having count(*)>0 order by username
union all
select username,0 as phoneMoney,count(*) as phonenum,0 as phonepause,0 as phonenum1 from f_extsys group by username having count(*)>0 order by username
union all
select username, 0 as phoneMoney,0 as phonenum,count(*) as phonepause ,0 as phonenum1 from pu_fyb group by username,pause having count(*)>=0 and pause=1

select username,0 as phoneMoney,0 as phonenum,0 as phonepause,count(*)as phonenum1 from f_extsys where
phonenum not in(select eps400 from pu_fyb group by username,eps400)
and username!='00' group by username,phonenum order by username )a
group by username

[FenHui]

[Quote=引用 14 楼 babygehui 的回复:]
前边加sum 呀
select username,sum(phoneMoney) as phoneMoney,sum(phonenum) as phonenum,sum(phonepause) as phonepause,sum(phonenum1) as phonenum1 from(
select username,count(*) as phoneMoney,0 as phonenum,0 as phonepause,0 as phonenum1 from pu_fyb group by username having count(*)>0 order by username
union all
select username,0 as phoneMoney,count(*) as phonenum,0 as phonepause,0 as phone…
[/Quote]

我在测试这个的时候有好多错误
select username,count(*) as phoneMoney,0 as phonenum,0 as phonepause,0 as phonenum1
from pu_fyb group by username having count(*)>0 order by username
union all
select username,0 as phoneMoney,count(*) as phonenum,0 as phonepause,0 as phonenum1
from f_extsys group by username having count(*)>0 order by username
这样写 也是在关键字 'union' 附近有语法错误。

[hery2002]

username phonemoney phonenum phonepause
0110 27 27 9
02 9 10 1
---------------
这个9怎么来的?
看上去有点像行列转换哦

[babygehui]

[Quote=引用 15 楼 FenHui 的回复:]
引用 14 楼 babygehui 的回复:
前边加sum 呀
select username,sum(phoneMoney) as phoneMoney,sum(phonenum) as phonenum,sum(phonepause) as phonepause,sum(phonenum1) as phonenum1  from(
select username,count(*) as phoneMoney,0 as phonenum,0 as phonepause,0 as phonenum1  from pu_fyb group by username having count(*)>0 order by username
union all
select username,0 as phoneMoney,count(*) as phonenum,…
[/Quote]
因为order by和union 的错误，你把order by 和union处理一下，类似于
select * from(select username,count(*) as phoneMoney,0 as phonenum,0 as phonepause,0 as phonenum1 from pu_fyb group by username having count(*)>0 order by username) a
union all
select * from(select username,0 as phoneMoney,count(*) as phonenum,0 as phonepause,0 as phonenum1 from f_extsys group by username having count(*)>0 order by username)b
这样都处理下试试

[FenHui]

[Quote=引用 16 楼 hery2002 的回复:]
username phonemoney phonenum phonepause
0110 27 27 9
02 9 10 1
---------------
这个9怎么来的?
看上去有点像行列转换哦
[/Quote]

select username,count(*) as phonenum from f_extsys group by username having count(*)>0 order by username

这个9就是这个的结果啊

[FenHui]

[Quote=引用 17 楼 babygehui 的回复:]
引用 15 楼 FenHui 的回复:
引用 14 楼 babygehui 的回复:
前边加sum 呀
select username,sum(phoneMoney) as phoneMoney,sum(phonenum) as phonenum,sum(phonepause) as phonepause,sum(phonenum1) as phonenum1 from(
select username,count(*) as phoneMoney,0 as phonenum,0 as phonepause,0 as phonenum1 from pu_fyb group by username having count(*)>0 order by username
union all
select username,0 as …
[/Quote]

这样的话
除非同时指定了 TOP，否则 ORDER BY 子句在视图、内嵌函数、派生表和子查询中无效。
如果我加了top
2条union的结果却是
第一条追加第2条的数据啊