sql 分组统计

2008-12-25 02:39:19 163 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

ThingsBoard入门实战（七）：公共发布和 UI 细节修改公共发布和 UI 细节修改7.细节修改和公共发布一、公共发布二、UI优化下载运行修改三、下一步还可以做什么？更好的UI更丰富的系统模块 7.细节修改和公共发布 GET到新技能了，想给兄弟们演示，需要给他们每个人一个账号么？答案肯定是：不需要！ ThingsBoard 用公共发布解决了这个问题。一、公共发布首先我们来看一下路灯设备现在在谁名下？如下图，路灯都是customerA的，目前只有它和它的租户可以看到，其他人需要看就需要设为公有。如图操作：对于设备详情仪表盘，我们也需要设为公有阅读详情

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语句啊

...全文

QCPColorMap实现瀑布图 QCPColorMap实现真正的瀑布图阅读详情

FenHui

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

+ 关注 + 加入社区

切换为时间正序

FenHui 2008-12-26

打赏
举报

[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条的数据啊

FenHui 2008-12-26

打赏
举报

[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就是这个的结果啊

babygehui 2008-12-25

打赏
举报

[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
这样都处理下试试

hery2002 2008-12-25

打赏
举报

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

FenHui 2008-12-25

打赏
举报

[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' 附近有语法错误。

babygehui 2008-12-25

打赏
举报

前边加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 2008-12-25

打赏
举报

各位大哥大姐帮个忙啊

FenHui 2008-12-25

打赏
举报

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

这要怎么实现啊

czx33859066 2008-12-25

打赏
举报



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

babygehui 2008-12-25

打赏
举报

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

水族杰纶 2008-12-25

打赏
举报

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

csdyyr 2008-12-25

打赏
举报

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

songxianping 2008-12-25

打赏
举报

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 ,查詢的字段必需相同, 如上面的語句,查詢的字段都是兩個

FenHui 2008-12-25

打赏
举报

[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 引入子查询时，在选择列表中只能指定一个表达式。不能返回多个字段啊

FenHui 2008-12-25

打赏
举报

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

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

水族杰纶 2008-12-25

打赏
举报

--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 2008-12-25

打赏
举报

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

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

csdyyr 2008-12-25

打赏
举报

union all

fcuandy 2008-12-25

打赏
举报

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

sql 分组统计

相关推荐