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的话会因为后面的的结果不同而达不到我要的效果。不知道该怎么写。
拜托各位帮帮忙
解决方案 »
- alter system kill session后面能否跟变量?
- 这个问题一定要解决啊。
- Create an instance of failure, perplexing problem? ? ?
- 请各位大神帮忙优化一下下面的一段SQL谢谢了
- 请问 为什么 从数据库里取出来的BLOB数据是[B类型的
- 如果加密oracle的自定义函数和触发器
- 这个语句怎么写
- 是否有关于Oracle异常处理中的RollBack的相关文档?谢谢
- imp的时候提示:插入的值对于列过大 ,一共有几百个表,一半出现这个提示!
- 一個簡單又不容易的SQL語句,請教高人
- 如何实现两个不同数据库之间数据的导入?
- 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