如果我先对packageno进行分组,得到结果: SYS140214137 12653627 4044 15 SYS140218039 12653627 4048 15 SYS140218035 12653627 4048 15 SYS140218042 12653627 4048 10 SYS140218042 12653627 4044 5能否得到1楼那样的结果? 12653627 4044 15 12653627 4048 40 12653627 4044 5 试试这个: select cust,lotno,COUNT(*) ( select *, ROW_NUMBER() over(order by getdate()) as rowid ROW_NUMBER() over(partition by lotno order by getdate()) rownum from test )t group by cust,lotno,rowid - rownum
修改一下: select cust,lotno,COUNT(*) ( select *, ROW_NUMBER() over(order by getdate()) as rowid, ROW_NUMBER() over(partition by lotno order by getdate()) rownum from test )t group by cust,lotno,rowid - rownum
能否解释下?谢谢!这个是运用了数据本身的特别,比如rowid就是按照自然的顺序来排列,1,2,3,3而rownum是按照lotno分组后再排列,那么如果lotno是连续,那么rowid - rownum 就是一样的, 而如果不连续,就可以通过group by rowid-rownum,把不连续的分开,进行count了
SYS140214137 12653627 4044 15
SYS140218039 12653627 4048 15
SYS140218035 12653627 4048 15
SYS140218042 12653627 4048 10
SYS140218042 12653627 4044 5能否得到1楼那样的结果?
12653627 4044 15
12653627 4048 40
12653627 4044 5
真的没这么简单,得到的结果:12653627 4044 30
12653627 4048 45是不是你要分组的是,连续的lotno
真的没这么简单,得到的结果:12653627 4044 30
12653627 4048 45是不是你要分组的是,连续的lotno
是的。
SYS140214137 12653627 4044 15
SYS140218039 12653627 4048 15
SYS140218035 12653627 4048 15
SYS140218042 12653627 4048 10
SYS140218042 12653627 4044 5能否得到1楼那样的结果?
12653627 4044 15
12653627 4048 40
12653627 4044 5
试试这个:
select cust,lotno,COUNT(*)
(
select *,
ROW_NUMBER() over(order by getdate()) as rowid
ROW_NUMBER() over(partition by lotno order by getdate()) rownum
from test
)t
group by cust,lotno,rowid - rownum
select cust,lotno,COUNT(*)
(
select *,
ROW_NUMBER() over(order by getdate()) as rowid,
ROW_NUMBER() over(partition by lotno order by getdate()) rownum
from test
)t
group by cust,lotno,rowid - rownum
能否解释下?谢谢!这个是运用了数据本身的特别,比如rowid就是按照自然的顺序来排列,1,2,3,3而rownum是按照lotno分组后再排列,那么如果lotno是连续,那么rowid - rownum 就是一样的,
而如果不连续,就可以通过group by rowid-rownum,把不连续的分开,进行count了