有人能帮我吗?我想从我的表中查询,目的是显示哪些员工的销售额超过100件。,我已经完成了查询并成功了
但当我想显示每个员工的最后销售时间(销售超过100件商品)时,问题就出现了
我有类似的TBL_SALES表
no employee_id name pieces sl_time
---|-------------|-------------|--------|----------
1 | 1 | bungdito | 60 | 2012-03-29 22:20
2 | 1 | bungdito | 40 | 2012-03-05 18:00
3 | 1 | bungdito | 50 | 2012-02-18 08:00
4 | 2 | addheat | 120 | 2012-02-12 09:30
5 | 3 | angga | 20 | 2012-01-18 10:45
6 | 4 | dimas | 50 | 2012-01-01 08:30
当我使用像这样的查询时
select * from
(
select EMPLOYEE_ID, NAME, sum(PIECES) PIECES from
(
select EMPLOYEE_ID, NAME, PIECES
from DB_SCHEMA.TBL_SALES
)
group by EMPLOYEE_ID, NAME
) where PIECES > 100
我有这个正确的结果
employee_id name pieces
-------------|-------------|--------
1 | bungdito | 150
2 | addheat | 120
以上结果正确,
但是我需要的是完成每个员工的最后交易时间
我已经尝试进行查询,但仍然不正确
select * from
(
select EMPLOYEE_ID, NAME, sum(PIECES) PIECES, SL_TIME
(
select EMPLOYEE_ID, NAME, PIECES,(select max(WAKTU) SL_TIME from DB_USER.TR_PELANGGARAN where TB_SALES.EMPLOYEE_ID = EMPLOYEE_ID from TB_SALES) SL_TIME
from DB_SCHEMA.TBL_SALES
)
group by EMPLOYEE_ID, NAME, SL_TIME
) where PIECES > 100
使用该查询,我仍然有错误的结果(请参阅bundito和addheat之间的sl_time相同),如:
employee_id name pieces sl_time
-------------|-------------|--------|----------
1 | bungdito | 150 | 2012-03-29 22:20
2 | addheat | 120 | 2012-03-29 22:20
我需要的是下面这样的:
employee_id name pieces sl_time
-------------|-------------|--------|----------
1 | bungdito | 150 | 2012-03-29 22:20
2 | addheat | 120 | 2012-02-12 09:30
试试这个:
select EMPLOYEE_ID, NAME, sum(PIECES) PIECES, MAX(SL_TIME)
from DB_SCHEMA.TBL_SALES
group by EMPLOYEE_ID, NAME
having sum(PIECES) > 100
select *
from
(
select EMPLOYEE_ID, NAME, sl_time
, sum(PIECES) over(partition by EMPLOYEE_ID) SUM_PIECES
, max(sl_time) over(partition by EMPLOYEE_ID) last_sl_time
from
DB_SCHEMA.TBL_SALES
)
where SUM_PIECES > 100
and last_sl_time = sl_time
;