表A:
myid qty date
a0001 1 2010-4-21
a0001 2 2010-4-24
a0001 3 2010-5-22
a0001 2 2010-5-26
b0001 2 2010-4-20
b0001 2 2010-4-22
b0001 1 2010-5-23
b0001 2 2010-5-11
c0001 4 2010-4-28
c0001 6 2010-4-13
c0001 7 2010-5-29
c0001 7 2010-5-16
**** * *********根據MYID匯總QTY,條件是根據DATE每個月的形式匯總,取出MYID每個月QTY最大的2條,得出效果如下:
myid qty date
b0001 4 2010-4
c0001 10 2010-4
a0001 5 2010-5
c0001 14 2010-5
**** * *********
myid qty date
a0001 1 2010-4-21
a0001 2 2010-4-24
a0001 3 2010-5-22
a0001 2 2010-5-26
b0001 2 2010-4-20
b0001 2 2010-4-22
b0001 1 2010-5-23
b0001 2 2010-5-11
c0001 4 2010-4-28
c0001 6 2010-4-13
c0001 7 2010-5-29
c0001 7 2010-5-16
**** * *********根據MYID匯總QTY,條件是根據DATE每個月的形式匯總,取出MYID每個月QTY最大的2條,得出效果如下:
myid qty date
b0001 4 2010-4
c0001 10 2010-4
a0001 5 2010-5
c0001 14 2010-5
**** * *********
解决方案 »
- sql2005新建字段的时候,字段类型为 boole 类型,应该要选什么类型才行呢?
- 大家帮忙看看,sql server报错
- 这个SQL语句怎么写
- 求两个表中查询无记录的方法(在线)
- 这个查询如何写?
- sql2005的问题
- SQL 2000的代码规则与其他数据库的代码不兼容,怎么办呢?比如SQL 2000 与 MYSQL 的代码
- 时间计算
- 邹捷书中6.1.3节有句SQL语句有错误,请大家来看看!
- 供应商与客户的表结构
- 0x80004005 Microsoft JET Database Engine 创建文件失败
- ★★★ 请教各位:怎样在access中写trigger ??? 很急!!! ★★★
use dbx;
declare @t table(myid varchar(10),qty int,[date] datetime);
insert into @t
select 'a0001', 1 ,'2010-4-21' union all
select 'a0001',2, '2010-4-24' union all
select 'a0001',3, '2010-5-22' union all
select 'a0001',2, '2010-5-26'union all
select 'b0001',2, '2010-4-20'union all
select 'b0001',2, '2010-4-22'union all
select 'b0001',1, '2010-5-23'union all
select 'b0001',2, '2010-5-11'union all
select 'c0001',4, '2010-4-28'union all
select 'c0001',6, '2010-4-13'union all
select 'c0001',7, '2010-5-29'union all
select 'c0001',7, '2010-5-16';
--select * from @t;
SELECT myid,SUM(qty) as c,CONVERT(VARCHAR(7),[DATE],120) AS m FROM @t GROUP BY myid,CONVERT(VARCHAR(7),[DATE],120)
with cte as
(
select *,rn = row_number() over (partition by myid,convert(varchar(6),date,112) order by qty desc)
from a
)select myid,convert(varchar(7),date,120)date,sum(qty)qty
from cte
where rn < 3
group by myid,convert(varchar(7),date,120)date
select MYID,qty,date from
(select row_number() over(partition by MYID order by sum(qty) desc) as num,
MYID,sum(qty) as qty,convert(varchar(6),date,120) as date from tb
group by MYID,convert(varchar(6),date,120))aa where num<3
with cte as
(
select *,rn = row_number() over (partition by myid,convert(varchar(6),date,112) order by qty desc)
from a
)select myid,convert(varchar(7),date,120)date,sum(qty)qty
from cte
where rn < 3
group by myid,convert(varchar(7),date,120) --上边这里多了 date
-- Author :flystone
-- Date : 2011-02-11
-- Version: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (Intel X86)
-- Mar 29 2009 10:27:29
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Enterprise Edition on Windows NT 5.2 <X86> (Build 3790: Service Pack 2)
--
------------------------------------------------------------------------
--> Test Data: [ta]
If Object_id('[ta]') Is Not Null
Drop Table [ta]
GO
Create table [ta] ([myid] Varchar(5),[qty] Int,[date] Datetime)
Go
Insert Into [ta]
Select 'a0001',1,'2010-4-21' Union All
Select 'a0001',2,'2010-4-24' Union All
Select 'a0001',3,'2010-5-22' Union All
Select 'a0001',2,'2010-5-26' Union All
Select 'b0001',2,'2010-4-20' Union All
Select 'b0001',2,'2010-4-22' Union All
Select 'b0001',1,'2010-5-23' Union All
Select 'b0001',2,'2010-5-11' Union All
Select 'c0001',4,'2010-4-28' Union All
Select 'c0001',6,'2010-4-13' Union All
Select 'c0001',7,'2010-5-29' Union All
Select 'c0001',7,'2010-5-16'
Go
-->SQL Query:
;with cte
as
(
Select myid,SUM(QTY) AS SUMQTY,convert(char(7),[date],120) as d,
rid = ROW_NUMBER () over (PARTITION by convert(char(7),[date],120) order by sum([qty]) desc) From [ta]
group by [myid],convert(char(7),[date],120)
)
select * from cte where rid <3
-->Result:
/*
myid SUMQTY d rid
----- ----------- ------- --------------------
c0001 10 2010-04 1
b0001 4 2010-04 2
c0001 14 2010-05 1
a0001 5 2010-05 2
*/
SQL2005,with cte as (...) 就是创建cte视图。