求SQL得改写

caoyang0299 2011-09-27 03:08:46
select count(p.fsalesmanid) as cou,
P.FSALESMANID,
p.fsellprojectid,
to_char(p.fprepurchaseauditdate, 'yyyymmdd') fprepurchaseauditdate,
pm.fname_l2 as fname
from T_SHE_Purchase p, T_TEN_MarketingUnitMember m, T_PM_User pm
where pm.fid = m.fmemberid
and p.fsalesmanid = m.fmemberid
and m.fisduty = 0
and m.fmemberid != '4wzzw+w+RTmp6JoE25cW/xO33n8='
group by p.fsalesmanid,
to_char(p.fprepurchaseauditdate, 'yyyymmdd'),
p.fsellprojectid,
pm.fname_l2
order by count(p.fsalesmanid) desc
如何COUNT值为空的显示为0，
...全文
81 19 打赏 收藏 转发到动态 举报
写回复
用AI写文章
19 条回复
切换为时间正序
请发表友善的回复…
发表回复
oO寒枫Oo 2011-09-27
  • 打赏
  • 举报
 回复
把group by 中的p.fsalesmanid去掉
oO寒枫Oo 2011-09-27
  • 打赏
  • 举报
 回复 


create or replace view kv_fdc_monthmember as

select count(p.fsalesmanid) as cou,

  P.FSALESMANID,

  p.fsellprojectid,

  to_char(p.fprepurchaseauditdate, 'yyyymmdd') fprepurchaseauditdate,

  pm.fname_l2 as fname

  from T_SHE_Purchase p, T_TEN_MarketingUnitMember m, T_PM_User pm

 where pm.fid = m.fmemberid

  and p.fsalesmanid = m.fmemberid

  and m.fisduty = 0

  and m.fmemberid != '4wzzw+w+RTmp6JoE25cW/xO33n8='

 group by   to_char(p.fprepurchaseauditdate, 'yyyymmdd'),

  p.fsellprojectid,

  pm.fname_l2

  order by count(p.fsalesmanid) desc
caoyang0299 2011-09-27
  • 打赏
  • 举报
 回复
不是左也不是右，是full，我在外面在加个条件试试
-晴天 2011-09-27
  • 打赏
  • 举报
 回复
如果是左连接,对右表记录进行计数,那不是用count,而是用每条记录转为1后求和.
在MSSQL里是这样处理的
select 分组列,

sum(case when 右表列 is not null then 1 else 0 end) as 计数值

from 左表 left join 右表 on ...

group by 分组列
caoyang0299 2011-09-27
  • 打赏
  • 举报
 回复

select'No.'||DENSE_RANK()
OVER (PARTITION BY a.fname ORDER BY t1.rq) as rowno,a.fname fname1,a.cou cou_1,b.fname fname2,b.cou cou_2,c.fname fname3,c.cou cou_3,d.fname fname4,d.cou cou_4,e.fname fname5,e.cou cou_5,
f.fname fname6,f.cou cou_6,g.fname fname7,g.cou cou_7,h.fname fname8,h.cou cou_8,i.fname fname9,i.cou cou_9,j.fname fname10,j.cou cou_10,
k.fname fname11,k.cou cou_11,l.fname fname12,l.cou cou_12,m.fname fname13,m.cou cou_13,n.fname fname14,n.cou cou_14,o.fname fname15,o.cou cou_15,
p.fname fname16,p.cou cou_16,q.fname fname17,q.cou cou_17,r.fname fname_18,r.cou cou_18,s.fname fname19,s.cou cou_19,t.fname fname20,t.cou cou_20,
u.fname fname21,u.cou cou_21,v.fname fname22,v.cou cou_22,w.fname fname23,w.cou cou_23,x.fname fname24,x.cou cou_24,y.fname fname25,y.cou cou_25,
z.fname fname_26,z.cou cou_26,a1.fname fname27,a1.cou cou_27,a2.fname fname28,a2.cou cou_28,a3.fname fname29,a3.cou cou_29,a4.fname fname30,a4.cou cou_30,
a5.fname fname31,a5.cou cou_31 from
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'01' and (fsellprojectid='@pro' or ? is null)) a,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'02' and (fsellprojectid='@pro' or ? is null)) b,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'03' and (fsellprojectid='@pro' or ? is null)) c,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'04' and (fsellprojectid='@pro' or ? is null)) d,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'05' and (fsellprojectid='@pro' or ? is null)) e,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'06' and (fsellprojectid='@pro' or ? is null)) f,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'07' and (fsellprojectid='@pro' or ? is null)) g,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'08' and (fsellprojectid='@pro' or ? is null)) h,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'09' and (fsellprojectid='@pro' or ? is null)) i,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'10' and (fsellprojectid='@pro' or ? is null)) j,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'11' and (fsellprojectid='@pro' or ? is null)) k,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'12' and (fsellprojectid='@pro' or ? is null)) l,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'13' and (fsellprojectid='@pro' or ? is null)) m,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'14' and (fsellprojectid='@pro' or ? is null)) n,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'15' and (fsellprojectid='@pro' or ? is null)) o,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'16' and (fsellprojectid='@pro' or ? is null)) p,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'17' and (fsellprojectid='@pro' or ? is null)) q,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'18' and (fsellprojectid='@pro' or ? is null)) r,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'19' and (fsellprojectid='@pro' or ? is null)) s,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'20' and (fsellprojectid='@pro' or ? is null)) t,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'21' and (fsellprojectid='@pro' or ? is null)) u,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'22' and (fsellprojectid='@pro' or ? is null)) v,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'23' and (fsellprojectid='@pro' or ? is null)) w,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'24' and (fsellprojectid='@pro' or ? is null)) x,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'25' and (fsellprojectid='@pro' or ? is null)) y,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'26' and (fsellprojectid='@pro' or ? is null)) z,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'27' and (fsellprojectid='@pro' or ? is null)) a1,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'28' and (fsellprojectid='@pro' or ? is null)) a2,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'29' and (fsellprojectid='@pro' or ? is null)) a3,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'30' and (fsellprojectid='@pro' or ? is null)) a4,
(select rownum k,t.fname,t.cou from kv_fdc_monthmember t where t.fprepurchaseauditdate='@year'||'@month'||'31' and (fsellprojectid='@pro' or ? is null)) a5,
temp_rq t1
where t1.rq=b.k(+) and a.k(+)=t1.rq
and t1.rq=c.k(+) and t1.rq=d.k(+)
and t1.rq=e.k(+)
and t1.rq=f.k(+)
and t1.rq=g.k(+)
and t1.rq=h.k(+)
and t1.rq=i.k(+)
and t1.rq=j.k(+)
and t1.rq=k.k(+)
and t1.rq=l.k(+)
and t1.rq=m.k(+)
and t1.rq=n.k(+)
and t1.rq=o.k(+)
and t1.rq=p.k(+)
and t1.rq=q.k(+)
and t1.rq=r.k(+)
and t1.rq=s.k(+)
and t1.rq=t.k(+)
and t1.rq=u.k(+)
and t1.rq=v.k(+)
and t1.rq=w.k(+)
and t1.rq=x.k(+)
and t1.rq=y.k(+)
and t1.rq=z.k(+)
and t1.rq=a1.k(+)
and t1.rq=a2.k(+)
and t1.rq=a3.k(+)
and t1.rq=a4.k(+)
and t1.rq=a5.k(+) and t1.rq<='20'

order by t1.rq,rowno desc
这是前台的调用语句
caoyang0299 2011-09-27
  • 打赏
  • 举报
 回复
依然不灵
dawugui 2011-09-27
  • 打赏
  • 举报
 回复
[Quote=引用 10 楼 caoyang0299 的回复:]
还是不行
[/Quote]

select nvl(count(p.fsalesmanid),0) as cou,
P.FSALESMANID,
p.fsellprojectid,
to_char(p.fprepurchaseauditdate, 'yyyymmdd') fprepurchaseauditdate,
pm.fname_l2 as fname
from T_SHE_Purchase p left join T_TEN_MarketingUnitMember m
on
and p.fsalesmanid = m.fmemberid
and m.fisduty = 0
and m.fmemberid <> '4wzzw+w+RTmp6JoE25cW/xO33n8='
left join T_PM_User pm on pm.fid = m.fmemberid
group by p.fsalesmanid,
to_char(p.fprepurchaseauditdate, 'yyyymmdd'),
p.fsellprojectid,
pm.fname_l2
order by cou desc

你这个需要使用左连接才能查处所有的东西来,至于哪个表在前,哪个在后,就只有你自己知道了.
中国风 2011-09-27
  • 打赏
  • 举报
 回复 
用left join 

select count(p.fsalesmanid) as cou,

  P.FSALESMANID,

  p.fsellprojectid,

  to_char(p.fprepurchaseauditdate, 'yyyymmdd') fprepurchaseauditdate,

  pm.fname_l2 as fname

from T_SHE_Purchase p

	LEFT JOIN T_TEN_MarketingUnitMember m ON p.fsalesmanid = m.fmemberid

	LEFT JOIN T_PM_User pm ON pm.fid = m.fmemberid

 where  m.fisduty = 0

  and m.fmemberid != '4wzzw+w+RTmp6JoE25cW/xO33n8='

 group by p.fsalesmanid,

  to_char(p.fprepurchaseauditdate, 'yyyymmdd'),

  p.fsellprojectid,

  pm.fname_l2

  order by count(p.fsalesmanid) desc
caoyang0299 2011-09-27
  • 打赏
  • 举报
 回复
create or replace view kv_fdc_monthmember as
select count(p.fsalesmanid) as cou,
P.FSALESMANID,
p.fsellprojectid,
to_char(p.fprepurchaseauditdate, 'yyyymmdd') fprepurchaseauditdate,
pm.fname_l2 as fname
from T_SHE_Purchase p, T_TEN_MarketingUnitMember m, T_PM_User pm
where pm.fid = m.fmemberid
and p.fsalesmanid = m.fmemberid
and m.fisduty = 0
and m.fmemberid != '4wzzw+w+RTmp6JoE25cW/xO33n8='
group by p.fsalesmanid,
to_char(p.fprepurchaseauditdate, 'yyyymmdd'),
p.fsellprojectid,
pm.fname_l2
order by count(p.fsalesmanid) desc
我这是个视图，前台给这个试图传参，显示所有用户名，count为空显示灵
这么改过不灵
caoyang0299 2011-09-27
  • 打赏
  • 举报
 回复
还是不行
caoyang0299 2011-09-27
  • 打赏
  • 举报
 回复
呵呵，是，我再试下吧，这么改我好像试过不灵
dawugui 2011-09-27
  • 打赏
  • 举报
 回复 
select nvl(count(p.fsalesmanid),0) as cou,

  P.FSALESMANID,

  p.fsellprojectid,

  to_char(p.fprepurchaseauditdate, 'yyyymmdd') fprepurchaseauditdate,

  pm.fname_l2 as fname

  from T_SHE_Purchase p, T_TEN_MarketingUnitMember m, T_PM_User pm

 where pm.fid = m.fmemberid

  and p.fsalesmanid = m.fmemberid

  and m.fisduty = 0

  and m.fmemberid != '4wzzw+w+RTmp6JoE25cW/xO33n8='

 group by p.fsalesmanid,

  to_char(p.fprepurchaseauditdate, 'yyyymmdd'),

  p.fsellprojectid,

  pm.fname_l2

  order by count(p.fsalesmanid) desc
-晴天 2011-09-27
  • 打赏
  • 举报
 回复
再说,你是全连接后再进行统计的,连 0 都不会有.
-晴天 2011-09-27
  • 打赏
  • 举报
 回复
count 是不会出空值的,它是计数,没有的,就是计数为0.
suiyanpeng 2011-09-27
  • 打赏
  • 举报
 回复
............
--小F-- 2011-09-27
  • 打赏
  • 举报
 回复
搞了半天 居然是ORACLE的
suiyanpeng 2011-09-27
  • 打赏
  • 举报
 回复
select isnull(count(p.fsalesmanid),0) as cou,
P.FSALESMANID,
p.fsellprojectid,
to_char(p.fprepurchaseauditdate, 'yyyymmdd') fprepurchaseauditdate,
pm.fname_l2 as fname
from T_SHE_Purchase p, T_TEN_MarketingUnitMember m, T_PM_User pm
where pm.fid = m.fmemberid
and p.fsalesmanid = m.fmemberid
and m.fisduty = 0
and m.fmemberid != '4wzzw+w+RTmp6JoE25cW/xO33n8='
group by p.fsalesmanid,
to_char(p.fprepurchaseauditdate, 'yyyymmdd'),
p.fsellprojectid,
pm.fname_l2
order by count(p.fsalesmanid) desc
--小F-- 2011-09-27
  • 打赏
  • 举报
 回复 
select isnull(count(p.fsalesmanid),0) as cou,

  P.FSALESMANID,

  p.fsellprojectid,

  to_char(p.fprepurchaseauditdate, 'yyyymmdd') fprepurchaseauditdate,

  pm.fname_l2 as fname

  from T_SHE_Purchase p, T_TEN_MarketingUnitMember m left outer  join T_PM_User pm

on pm.fid = m.fmemberid

  and p.fsalesmanid = m.fmemberid

  and m.fisduty = 0

  and m.fmemberid != '4wzzw+w+RTmp6JoE25cW/xO33n8='

 group by p.fsalesmanid,

  to_char(p.fprepurchaseauditdate, 'yyyymmdd'),

  p.fsellprojectid,

  pm.fname_l2

  order by count(p.fsalesmanid) desc
AcHerat 元老 2011-09-27
  • 打赏
  • 举报
 回复
count(p.fsalesmanid) as cou

改为

sum(case when p.fsalesmanid is not null then 1 else 0 end) as cou
仿网易新闻代码
android 网易新闻 滑动 从网上下载，自己改写了一部分。
Mybatis插件开发(拦截SQL并改写SQL)
文章目录拦截器接口介绍SQL拦截改写定义拦截器接口 Interceptor添加拦截器 拦截器接口介绍 Mybatis 允许在以映射SQL语句执行过程中的某一点进行拦截调用。默认情况下，Mybatis允许使用插件来拦截的接口和方法有以下几个 Executor(update、query、flushStatements、commit、rollback、getTransaction、clos、isClosed) ParameterHandler(getParameterObject、setParameters)
2019小白SQL入门（零基础60分钟含SQL语法问答精彩）
零基础60分钟含SQL语法问答精彩
Sharding-JDBC 源码分析 —— SQL 改写
1. 概述 前置阅读：《SQL 解析（三）之查询SQL》 本文分享SQL 改写的源码实现。主要涉及两方面： SQL 改写：改写 SQL，解决分库分表后，查询结果需要聚合，需要对 SQL 进行调整，例如分页 SQL 生成：生成分表分库的执行 SQL SQLRewriteEngine，SQL重写引擎，实现 SQL 改写、生成功能。从 Sharding-JDBC 1.5.0 版本，SQL 改写进行了调整和大量优化。 1.4.x及之前版本，SQL改写是在SQL路由之前完成的，在1.5.x中调整为SQL路
Sharding-JDBC 源码之 SQL 改写
Sharding-JDBC 系列 第一篇 Sharding-JDBC 源码之启动流程分析 第二篇 Sharding-JDBC 源码之 SQL 解析 第三篇 Sharding-JDBC 源码之 SQL 路由 第三篇 Sharding-JDBC 源码之 SQL 改写 在完成 SQL 路由后，我们就会拿到实际要查询的数据源名称及实际表节点。这时候就需要对原逻辑 SQL 进行改写，将逻辑表名替换为真实表名，这一步 Sharding-JDBC 是由改写引擎 SQLRewriteEngine 来完成的。 目录
MS-SQL Server

34,594

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章