求sql语句

[shylsz]

CREATE TABLE t1(bm varchar(5), bh1 varchar(5), bh2 varchar(5), rq datetime, num1 int,
num2 int) INSERT INTO t1
SELECT '1', '1', '1', '2007-01-01', 1, 1
UNION
SELECT '1', '1', '2', '2007-01-01', 2, 2
UNION
SELECT '1', '1', '1', '2007-01-02', 1, 1
UNION
SELECT '1', '1', '1', '2007-01-03', 1, 1
UNION
SELECT '1', '1', '2', '2007-01-03', 1, 1
UNION
SELECT '2', '1', '1', '2007-01-01', 1, 1
UNION
SELECT '2', '1', '1', '2007-01-02', 2, 2
UNION
SELECT '3', '1', '1', '2007-01-01', 1, 1

求如下结果的sql语句
bm bh1 ts num1 num2
1 1 3 6 3
2 1 2 3 3
3 1 1 1 1
ts是相同bm,bh1不同天数的合计，num1是相同bm,bh1的num1合计，num2是相同bm,bh1,rq的最大num2的相同bm,bh1的合计

[pt1314917]

select distinct a.bm,a.bh1,(select count(distinct rq) from tf where bm=a.bm  group by bm,bh1)ts,
(select sum(num1) from tf where bm=a.bm group by bm,bh1)num1,
c.num2
from tf a,
(select bm,sum(num2)[num2] from (select bm,max(num2)[num2] from tf group by bm,bh1,rq)b group by bm)c
where a.bm=c.bm 

[pt1314917]

楼主描述的有点混乱，只好自己猜想的写一下了。

[shylsz]

SELECT bm, bh1, SUM(num1) AS num1, COUNT(DISTINCT rq) AS ts,
(SELECT SUM(num2) AS num2
FROM (SELECT MAX(num2) AS num2
FROM t1
WHERE bm = t3.bm AND bh1 = t3.bh1
GROUP BY bm, bh1, rq) a) AS num2
FROM t1 t3
GROUP BY bm, bh1
这样可以实现了,但是类似num2这样计算的字段有10来个
,可以优化吗?
SELECT bm, bh1, SUM(num1) AS num1, COUNT(DISTINCT rq) AS ts,
(SELECT SUM(num2) AS num2
FROM (SELECT MAX(num2) AS num2
FROM t1
WHERE bm = t3.bm AND bh1 = t3.bh1
GROUP BY bm, bh1, rq) a) AS num2
,
(SELECT SUM(num3) AS num3
FROM (SELECT MAX(num3) AS num3
FROM t1
WHERE bm = t3.bm AND bh1 = t3.bh1
GROUP BY bm, bh1, rq) a) AS num3

,
(SELECT SUM(num4) AS num4
FROM (SELECT MAX(num4) AS num4
FROM t1
WHERE bm = t3.bm AND bh1 = t3.bh1
GROUP BY bm, bh1, rq) a) AS num4

FROM t1 t3
GROUP BY bm, bh1

[pt1314917]

这表结构设计的，呵呵```
楼主怎么会想要得到这种答案噢，太烦琐了。。。
我看看尽量优化下。 。

[pt1314917]

那些num3,num4是啥样的？

[pt1314917]

select distinct a.bm,a.bh1,b.ts,b.num1,c.num2
from tf a,
(select bm,sum(num1)num1,count(distinct rq)ts from tf group by bm,bh1)b,
(select bm,sum(num2)[num2],sum(num3)[num3],sum(num4)[num4] from (select bm,max(num2)num2,max(num3)num3,max(num4)num4 from tf group by bm,bh1,rq)b group by bm)c
where a.bm=c.bm and  b.bm=a.bm 
----------------------
如果num3,num4,...都跟num2是一样计算的话，可以像上面那样继续加在后面。

[hamburger]

select distinct a.bm,a.bh1,b.cnt ,c.num1, e.num2
from #t1 a
inner join
(select bm,bh1 ,count(distinct rq) as cnt from #t1 group by bm,bh1) b
on
a.bm = b.bm and b.bh1=b.bh1
inner join
(select bm,bh1 ,sum(num1) as num1 from #t1 group by bm,bh1) c
on
a.bm = c.bm and a.bh1=c.bh1
inner join
(select d.bm,d.bh1 ,sum(num2) as num2 from (
select bm,bh1 ,rq,max(num2) as num2 from #t1 group by bm,bh1,rq) d
group by bm,bh1) e
on
a.bm = e.bm and a.bh1=e.bh1


bm bh1 ts num1 num2
1 1 3 6 4
2 1 2 3 3
3 1 1 1 1

[shylsz]

结贴了

[子陌红尘]

CREATE   TABLE   t1(bm   varchar(5),   bh1   varchar(5),   bh2   varchar(5),   rq   datetime,   num1   int,   
num2   int)   INSERT   INTO   t1 
                    SELECT   '1 ',   '1 ',   '1 ',   '2007-01-01 ',   1,   1 
UNION 
SELECT   '1 ',   '1 ',   '2 ',   '2007-01-01 ',   2,   2 
UNION 
SELECT   '1 ',   '1 ',   '1 ',   '2007-01-02 ',   1,   1 
UNION 
SELECT   '1 ',   '1 ',   '1 ',   '2007-01-03 ',   1,   1 
UNION 
SELECT   '1 ',   '1 ',   '2 ',   '2007-01-03 ',   1,   1 
UNION 
SELECT   '2 ',   '1 ',   '1 ',   '2007-01-01 ',   1,   1 
UNION 
SELECT   '2 ',   '1 ',   '1 ',   '2007-01-02 ',   2,   2 
UNION 
SELECT   '3 ',   '1 ',   '1 ',   '2007-01-01 ',   1,   1 


select 
    bm,bh1,count(distinct rq) as ts,sum(num1) as num1,sum(num2) as num2 
from 
   t1 
group by 
    bm,bh1

/*
bm    bh1   ts          num1        num2        
----- ----- ----------- ----------- ----------- 
1     1     3           6           6
2     1     2           3           3
3     1     1           1           1
*/

drop table t1

[gahade]

select bm,
	bh1,
	datediff(day,min(rq),max(rq))+1 as ts,
	sum(num1) as num1
from t1
group by bm,bh1

num2没看懂

[子陌红尘]

CREATE TABLE t1(bm varchar(5), bh1 varchar(5), bh2 varchar(5), rq datetime, num1 int,num2 int) 

INSERT INTO t1 
        SELECT '1 ', '1 ', '1 ', '2007-01-01 ', 1, 1 
UNION 
SELECT '1 ', '1 ', '2 ', '2007-01-01 ', 2, 2 
UNION 
SELECT '1 ', '1 ', '1 ', '2007-01-02 ', 1, 1 
UNION 
SELECT '1 ', '1 ', '1 ', '2007-01-03 ', 1, 1 
UNION 
SELECT '1 ', '1 ', '2 ', '2007-01-03 ', 1, 1 
UNION 
SELECT '2 ', '1 ', '1 ', '2007-01-01 ', 1, 1 
UNION 
SELECT '2 ', '1 ', '1 ', '2007-01-02 ', 2, 2 
UNION 
SELECT '3 ', '1 ', '1 ', '2007-01-01 ', 1, 1 


select 
    bm,bh1,count(distinct rq) as ts,sum(num1) as num1,sum(num2) as num2 
from 
    t1 
group by 
    bm,bh1

/*
bm    bh1   ts          num1        num2    
----- ----- ----------- ----------- ----------- 
1     1     3           6           6
2     1     2           3           3
3     1     1           1           1
*/

drop table t1

[shylsz]

不好意思,num2错了
bm bh1 ts num1 num2
1 1 3 6 4
2 1 2 3 3
3 1 1 1 1

[shylsz]

比如bm:1,bh1:1来说num2是2+1+1=4
2007-01-01 2
2007-01-02 1
2007-01-03 1

[shylsz]

比如bm:1,bh1:1来说num2是2+1+1=4
2007-01-01 2
2007-01-02 1
2007-01-03 1