--设定表名为T SELECT 姓名,销售金额,销售时间,零售单号, 店名 FROM (select 姓名,销售时间,零售单号, 店名, ROW_NUMBER() OVER(PARTITION BY 零售单号 ORDER BY 销售金额 DESC) RN, SUM(销售金额) OVER(PARTITION BY 零售单号) 销售金额 FROM T) WHERE RN=1
ROW_NUMBER() OVER(PARTITION BY 零售单号 ORDER BY 销售金额 DESC) RN,这句话有什么作用不太懂、?、
select max(姓名)keep(dense_rank last order by 销售金额,rowid), sum(销售金额), max(销售时间)keep(dense_rank last order by 销售金额,rowid), 零售单号, max(店名)keep(dense_rank last order by 销售金额,rowid) from 表 group by 零售单号
因我这里说的表,是一个类似视图,以下是实际的语句,套用以上语法,提示“未明确定义列”,不知是哪里出了问题?select B.导购员,B.零售日期,B.单号,B.货位编码, B.货位名称,B.实收金额 from (select 导购员,零售日期,货位编码, 货位名称,实收金额, ROW_NUMBER() OVER(PARTITION BY 单号 ORDER BY 实收金额 DESC) RN, sum(实收金额) OVER(PARTITION BY 单号) 实收金额 FROM ( select TO_CHAR(ls_date, 'YYYY-MM-DD') as 零售日期,ls_number as 单号, ls_fs.ls_hw_code as 货位编码,ls_channel_person as 导购员,ls_fs.ls_hw_name as 货位名称, sum(ls_take_money) as 实收金额 from ls_fs where ls_number in (select ls_number from ls_fs WHERE (LS_DATE between to_date('2014-09-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2014-09-10 23:59:59','yyyy-mm-dd HH24:mi:ss')) having sum(ls_take_money)>=500 group by ls_number) group by TO_CHAR(ls_date, 'YYYY-MM-DD') ,ls_number, ls_hw_code,ls_channel_person,ls_hw_name order by ls_Number ) ) B where RN=1
你外层把单号也select出来了,里面的查询也要相应的加上 select B.导购员,B.零售日期,B.单号,B.货位编码, B.货位名称,B.实收金额 from (select 导购员,零售日期,单号,货位编码, 货位名称,实收金额, ROW_NUMBER() OVER(PARTITION BY 单号 ORDER BY 实收金额 DESC) RN, sum(实收金额) OVER(PARTITION BY 单号) 实收金额 FROM( select TO_CHAR(ls_date, 'YYYY-MM-DD') as 零售日期,ls_number as 单号, ls_fs.ls_hw_code as 货位编码,ls_channel_person as 导购员,ls_fs.ls_hw_name as 货位名称, sum(ls_take_money) as 实收金额 from ls_fs where ls_number in (select ls_number from ls_fs WHERE (LS_DATE between to_date('2014-09-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2014-09-10 23:59:59','yyyy-mm-dd HH24:mi:ss')) having sum(ls_take_money)>=500 group by ls_number) group by TO_CHAR(ls_date, 'YYYY-MM-DD') ,ls_number, ls_hw_code,ls_channel_person,ls_hw_name order by ls_Number ) )B where RN=1
你最里面的查询可以单独执行吗?嵌入到外层查询时,里层查询的order by 应该去掉
逻辑有点问题,套的层次太多,整理下,试试这个 select B.导购员,B.零售日期,B.单号,B.货位编码, B.货位名称,B.实收金额 from (select TO_CHAR(ls_date, 'YYYY-MM-DD') as 零售日期,ls_number as 单号, ls_hw_code as 货位编码,ls_channel_person as 导购员,ls_hw_name as 货位名称, ROW_NUMBER() OVER(PARTITION BY ls_number ORDER BY ls_take_money DESC) RN, sum(ls_take_money) OVER(PARTITION BY ls_number) 实收金额 FROM ls_fs WHERE (LS_DATE between to_date('2014-09-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2014-09-10 23:59:59','yyyy-mm-dd HH24:mi:ss')) ) B where RN=1 and 实收金额>=500 order by 单号
--设定表名为T
SELECT 姓名,销售金额,销售时间,零售单号, 店名 FROM
(select 姓名,销售时间,零售单号, 店名,
ROW_NUMBER() OVER(PARTITION BY 零售单号 ORDER BY 销售金额 DESC) RN,
SUM(销售金额) OVER(PARTITION BY 零售单号) 销售金额
FROM T)
WHERE RN=1
ROW_NUMBER() 其实就是一个分区然后标记功能,排序后第几就标记为几。
sum(销售金额),
max(销售时间)keep(dense_rank last order by 销售金额,rowid),
零售单号,
max(店名)keep(dense_rank last order by 销售金额,rowid)
from 表
group by 零售单号
我这里所说的表是经过分组函数查出来的,ROWID,在这里用不了。
我这里所说的表是经过分组函数查出来的,ROWID,在这里用不了。
换一个其他的字段,只要同一个单据中不重复就可以
dense_rank允许并列最大,因此需要order by的字段区分出每一条记录
或是
你试试我1楼的写法
这两种写法都可以,执行效率应该也没有太大差别
rank,dense_rank,row_number使用和区别
因我这里说的表,是一个类似视图,以下是实际的语句,套用以上语法,提示“未明确定义列”,不知是哪里出了问题?select B.导购员,B.零售日期,B.单号,B.货位编码, B.货位名称,B.实收金额 from
(select 导购员,零售日期,货位编码, 货位名称,实收金额, ROW_NUMBER() OVER(PARTITION BY 单号 ORDER BY 实收金额 DESC) RN,
sum(实收金额) OVER(PARTITION BY 单号) 实收金额 FROM
(
select TO_CHAR(ls_date, 'YYYY-MM-DD') as 零售日期,ls_number as 单号, ls_fs.ls_hw_code as 货位编码,ls_channel_person as 导购员,ls_fs.ls_hw_name as 货位名称,
sum(ls_take_money) as 实收金额
from ls_fs where ls_number in
(select ls_number
from ls_fs
WHERE (LS_DATE between to_date('2014-09-01 00:00:00','yyyy-mm-dd HH24:mi:ss')
and to_date('2014-09-10 23:59:59','yyyy-mm-dd HH24:mi:ss'))
having sum(ls_take_money)>=500
group by ls_number) group by TO_CHAR(ls_date, 'YYYY-MM-DD') ,ls_number, ls_hw_code,ls_channel_person,ls_hw_name
order by ls_Number
)
) B
where RN=1
select B.导购员,B.零售日期,B.单号,B.货位编码, B.货位名称,B.实收金额 from
(select 导购员,零售日期,单号,货位编码, 货位名称,实收金额,
ROW_NUMBER() OVER(PARTITION BY 单号 ORDER BY 实收金额 DESC) RN,
sum(实收金额) OVER(PARTITION BY 单号) 实收金额
FROM(
select TO_CHAR(ls_date, 'YYYY-MM-DD') as 零售日期,ls_number as 单号, ls_fs.ls_hw_code as 货位编码,ls_channel_person as 导购员,ls_fs.ls_hw_name as 货位名称,
sum(ls_take_money) as 实收金额
from ls_fs where ls_number in
(select ls_number
from ls_fs
WHERE (LS_DATE between to_date('2014-09-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2014-09-10 23:59:59','yyyy-mm-dd HH24:mi:ss'))
having sum(ls_take_money)>=500
group by ls_number)
group by TO_CHAR(ls_date, 'YYYY-MM-DD') ,ls_number, ls_hw_code,ls_channel_person,ls_hw_name
order by ls_Number
)
)B
where RN=1
select B.导购员,B.零售日期,B.单号,B.货位编码, B.货位名称,B.实收金额 from
(select TO_CHAR(ls_date, 'YYYY-MM-DD') as 零售日期,ls_number as 单号, ls_hw_code as 货位编码,ls_channel_person as 导购员,ls_hw_name as 货位名称,
ROW_NUMBER() OVER(PARTITION BY ls_number ORDER BY ls_take_money DESC) RN,
sum(ls_take_money) OVER(PARTITION BY ls_number) 实收金额
FROM ls_fs
WHERE (LS_DATE between to_date('2014-09-01 00:00:00','yyyy-mm-dd HH24:mi:ss') and to_date('2014-09-10 23:59:59','yyyy-mm-dd HH24:mi:ss'))
) B
where RN=1 and 实收金额>=500
order by 单号