Select max(sum(item1)) From T1 group by convert(varchar(7),time,120)
select top 100 percent * from (select convert(varchar(7),d1,120) , sum(item1) item1 from t1 group by convert(varchar(7),d1,120)) t union all select top 100 percnt * from (select top 1 convert(varchar(7),d1,120) , sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc) t
create table t1(Item1 int, D1 datetime) insert into t1 values(1 , '2009-1-1') insert into t1 values(2 , '2009-1-2') insert into t1 values(3 , '2009-1-3') insert into t1 values(4 , '2009-2-1') insert into t1 values(1 , '2009-2-2') goselect top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 descdrop table t1 /* d1 item1 ------- ----------- 2009-01 6(所影响的行数为 1 行) */
create table t1(Item1 int, D1 datetime) insert into t1 values(1 , '2009-1-1') insert into t1 values(2 , '2009-1-2') insert into t1 values(3 , '2009-1-3') insert into t1 values(4 , '2009-2-1') insert into t1 values(1 , '2009-2-2') go --1只查最大月份 select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc /* d1 item1 ------- ----------- 2009-01 6(所影响的行数为 1 行) */--2,先查各月份,然后再加上最大月份的记录 select top 100 percent * from (select convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120)) t union all select top 100 percent * from (select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc) t /* d1 item1 ------- ----------- 2009-01 6 2009-02 5 2009-01 6(所影响的行数为 3 行) */drop table t1
create table t1(Item1 int, D1 datetime) insert into t1 values(1 , '2009-1-1') insert into t1 values(2 , '2009-1-2') insert into t1 values(3 , '2009-1-3') insert into t1 values(4 , '2009-2-1') insert into t1 values(1 , '2009-2-2') go --1只查最大月份 select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc /* d1 item1 ------- ----------- 2009-01 6(所影响的行数为 1 行) */--2只查最小月份 select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 /* d1 item1 ------- ----------- 2009-02 5(所影响的行数为 1 行) */ --3先查各月份,然后再加上最大最小月份的记录 select top 100 percent * from (select convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120)) t union all select top 100 percent * from (select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc) t union all select top 100 percent * from (select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1) t /* d1 item1 ------- ----------- 2009-01 6 2009-02 5 2009-01 6 2009-02 5(所影响的行数为 4 行) */drop table t1
比如表里面有这些数据:
Item1 D1
1 2009-1-1
2 2009-1-2
3 2009-1-3
4 2009-2-1
1 2009-2-2
然后我想获得的结果是6!就是两个月份的Item1的总和的最大的一个!
Select max(sum(item1)) From T1
group by convert(varchar(7),time,120)
union all
select top 100 percnt * from (select top 1 convert(varchar(7),d1,120) , sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc) t
insert into t1 values(1 , '2009-1-1')
insert into t1 values(2 , '2009-1-2')
insert into t1 values(3 , '2009-1-3')
insert into t1 values(4 , '2009-2-1')
insert into t1 values(1 , '2009-2-2')
goselect top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 descdrop table t1 /*
d1 item1
------- -----------
2009-01 6(所影响的行数为 1 行)
*/
insert into t1 values(1 , '2009-1-1')
insert into t1 values(2 , '2009-1-2')
insert into t1 values(3 , '2009-1-3')
insert into t1 values(4 , '2009-2-1')
insert into t1 values(1 , '2009-2-2')
go
--1只查最大月份
select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc
/*
d1 item1
------- -----------
2009-01 6(所影响的行数为 1 行)
*/--2,先查各月份,然后再加上最大月份的记录
select top 100 percent * from (select convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120)) t
union all
select top 100 percent * from (select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc) t
/*
d1 item1
------- -----------
2009-01 6
2009-02 5
2009-01 6(所影响的行数为 3 行)
*/drop table t1
create table t1(Item1 int, D1 datetime)
insert into t1 values(1 , '2009-1-1')
insert into t1 values(2 , '2009-1-2')
insert into t1 values(3 , '2009-1-3')
insert into t1 values(4 , '2009-2-1')
insert into t1 values(1 , '2009-2-2')
go
--1只查最大月份
select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc
/*
d1 item1
------- -----------
2009-01 6(所影响的行数为 1 行)
*/--2只查最小月份
select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1
/*
d1 item1
------- -----------
2009-02 5(所影响的行数为 1 行)
*/
--3先查各月份,然后再加上最大最小月份的记录
select top 100 percent * from (select convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120)) t
union all
select top 100 percent * from (select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1 desc) t
union all
select top 100 percent * from (select top 1 convert(varchar(7),d1,120) d1, sum(item1) item1 from t1 group by convert(varchar(7),d1,120) order by item1) t
/*
d1 item1
------- -----------
2009-01 6
2009-02 5
2009-01 6
2009-02 5(所影响的行数为 4 行)
*/drop table t1