select
t.vcsalesenterprisecode,
t.vcsalesenterprisename,
t.vcsaledepartmentname,
t.vcsalesenterprisecorporate,
t.vcsalesenterpriseaddress,
t.vcsalesenterprisetel
from VIEW_FLOWBUSSINESS t
where substr(t.vcpurchasedepartmentcode,0,2)='51'
and substr(vcsaledepartmentcode,0,2)='33'
and to_char(t.dtAwardCertificate,'yyyy-mm-dd')>='2010-01-01'
and to_char(t.dtAwardCertificate,'yyyy-mm-dd hh24:mi:ss')<='2010-04-14 23:59:59'
and t.brevoke=0
group by t.vcsalesenterprisecode,t.vcsalesenterprisename,t.vcsaledepartmentname,
t.vcsalesenterprisecorporate,t.vcsalesenterpriseaddress,t.vcsalesenterprisetel
这样子查询会把vcsalesenterprisecode相同的都查出来。
我希望把查询的到vcsalesenterprisecode相同的结果只输出一条!!!!!!!!!!!!!!
如果用distinct的话会因为后面的的结果不同而达不到我要的效果。不知道该怎么写。
拜托各位帮帮忙
t.vcsalesenterprisecode,
t.vcsalesenterprisename,
t.vcsaledepartmentname,
t.vcsalesenterprisecorporate,
t.vcsalesenterpriseaddress,
t.vcsalesenterprisetel
from VIEW_FLOWBUSSINESS t
where substr(t.vcpurchasedepartmentcode,0,2)='51'
and substr(vcsaledepartmentcode,0,2)='33'
and to_char(t.dtAwardCertificate,'yyyy-mm-dd')>='2010-01-01'
and to_char(t.dtAwardCertificate,'yyyy-mm-dd hh24:mi:ss')<='2010-04-14 23:59:59'
and t.brevoke=0
group by t.vcsalesenterprisecode,t.vcsalesenterprisename,t.vcsaledepartmentname,
t.vcsalesenterprisecorporate,t.vcsalesenterpriseaddress,t.vcsalesenterprisetel
这样子查询会把vcsalesenterprisecode相同的都查出来。
我希望把查询的到vcsalesenterprisecode相同的结果只输出一条!!!!!!!!!!!!!!
如果用distinct的话会因为后面的的结果不同而达不到我要的效果。不知道该怎么写。
拜托各位帮帮忙
解决方案 »
- 如何按时间分组,并取最大值啊?
- oracle等待事件
- 帮忙写个select语句
- 连表查询后原表的排序打乱了,该怎么办呢?
- 用loadrunner8.0测试oracle9.0.2.7,性能结果值特别低,为何?
- oralce提高自查询效率,我写的要0.094秒,这个效率不行 请高手指教
- 问个求日期的表达式
- oci ,以及dmalloc問題請教 , 我由chinaunix,轉戰itpub,都沒人願意出手,只好請csdn高手幫忙
- 几近崩溃边缘,oraclehome92TNSlistener服务启动不了了
- oracle 10g安装时,无法确定主机ip地址?
- 如何实现两个不同数据库之间数据的导入?
- sql语句问题~~
t.vcsalesenterprisename,
t.vcsaledepartmentname,
t.vcsalesenterprisecorporate,
t.vcsalesenterpriseaddress,
t.vcsalesenterprisetel
FROM (SELECT t.vcsalesenterprisecode,
t.vcsalesenterprisename,
t.vcsaledepartmentname,
t.vcsalesenterprisecorporate,
t.vcsalesenterpriseaddress,
t.vcsalesenterprisetel,
row_number() over(PARTITION BY vcsalesenterprisecode ORDER BY vcsalesenterprisecode) rn
FROM VIEW_FLOWBUSSINESS t
WHERE substr(t.vcpurchasedepartmentcode, 0, 2) = '51' AND
substr(vcsaledepartmentcode, 0, 2) = '33' AND
to_char(t.dtAwardCertificate, 'yyyy-mm-dd') >= '2010-01-01' AND
to_char(t.dtAwardCertificate, 'yyyy-mm-dd hh24:mi:ss') <= '2010-04-14 23:59:59' AND
t.brevoke = 0)
WHERE rn = 1;
乃是神人 崇拜中。
但是我通过PLSQL运行。在最外层的位置提示出错 主要是t的问题 俺同事看了下 修改了下 注意红色的a
SELECT t.vcsalesenterprisecode,
t.vcsalesenterprisename,
t.vcsaledepartmentname,
t.vcsalesenterprisecorporate,
t.vcsalesenterpriseaddress,
t.vcsalesenterprisetel修改后如下。
与大家分享
SELECT a.vcsalesenterprisecode,
a.vcsalesenterprisename,
a.vcsaledepartmentname,
a.vcsalesenterprisecorporate,
a.vcsalesenterpriseaddress,
a.vcsalesenterprisetel
FROM (SELECT t.vcsalesenterprisecode,
t.vcsalesenterprisename,
t.vcsaledepartmentname,
t.vcsalesenterprisecorporate,
t.vcsalesenterpriseaddress,
t.vcsalesenterprisetel,
row_number() over(PARTITION BY vcsalesenterprisecode ORDER BY vcsalesenterprisecode) rn
FROM VIEW_FLOWBUSSINESS t
WHERE substr(t.vcpurchasedepartmentcode, 0, 2) = '51' AND
substr(vcsaledepartmentcode, 0, 2) = '33' AND
to_char(t.dtAwardCertificate, 'yyyy-mm-dd') >= '2010-01-01' AND
to_char(t.dtAwardCertificate, 'yyyy-mm-dd hh24:mi:ss') <= '2010-04-14 23:59:59' AND
t.brevoke = 0) a
WHERE rn = 1