在sale_new_result表中需要获取province,city,dealercode(销售店号) ,companyname,province province2,companycity,count(dealercode) total (销售店总数)这些字段,但在 group by后只需按city,dealercode分组就可以了,但为了同时能查询出其它字段我也只好把其它不需要分组的字段也加到 group by后去,这样虽然不影响结果(在我这个例子中),但总觉这种方法不太好,求其它的解决方法,请大家指点指点,先谢了!
查询代码如下:
select province,city,dealercode ,companyname,province province2,companycity,count(dealercode) total from sale_new_result a
where countdate>=200906 and countdate<=200909
and
dealercode in(SELECT distinct dealercode FROM SALE_NEW_MASTER where province=substr(a.province,0,length(a.province)-1))
and substr(city,0,length(city)-1) not in (SELECT distinct city FROM SALE_NEW_MASTER where province=substr(a.province,0,length(a.province)-1))group by province, city,companyname,dealercode,companycity
查询代码如下:
select province,city,dealercode ,companyname,province province2,companycity,count(dealercode) total from sale_new_result a
where countdate>=200906 and countdate<=200909
and
dealercode in(SELECT distinct dealercode FROM SALE_NEW_MASTER where province=substr(a.province,0,length(a.province)-1))
and substr(city,0,length(city)-1) not in (SELECT distinct city FROM SALE_NEW_MASTER where province=substr(a.province,0,length(a.province)-1))group by province, city,companyname,dealercode,companycity
解决方案 »
- 谁来帮帮我 ORA-00936:missing expression 错误。
- 为什么在游标里用rownum,没有得到正确的数据
- 如何对单表建立及管理(如查询)LOG?
- 如何在oracle9i 中date只存储时间而不存日期
- 一个存储过程的问题?
- 程序包或函数f_getcblrsl处于无效状态
- oracle 的new_time函数自动实现了对夏令时的计算么?
- 在SQL2000里面创建一个数据库这么容易(几秒钟),为什么Oracle 要等几分钟的,是不是系统出了什么问题
- oracle: 如何将 数据库中字段长度缩小?
- 帮帮忙 linux下安装ORACLE!!
- 应用程序始终无法登陆
- 求一sql语句:
where
group by city,dealercode
和group by city,dealercode
结果是一样的?按city,dealercode分组时同一个分组内的其他字段值都相同的话
可以用max(province),max(companyname)...
不影响结果另外,in后面的子查询中distinct没有必要
这样试试
select max(province),city,dealercode ,max(companyname),max(province) province2,max(companycity),count(dealercode) total from sale_new_result a
where countdate>=200906 and countdate <=200909
and
exists(SELECT 1 FROM SALE_NEW_MASTER where a.province like province||'_' and dealercode=a.dealercode)
and not exists (SELECT 1 FROM SALE_NEW_MASTER where a.province like province||'_' and a.city like city||'_') group by city,dealercode
group by city,dealercode
from sale_new_result a
where countdate>=200906 and countdate <=200909 意思就这样了,基本COPY你的代码,只是换了一个分析函数,错误你自己找找,运气好直接通过