表Table里有这样几条记录: NUMBER BIG SMALL WEEK_DAY
1000---10---20---6
1000---15---18---7
1000---20---25---6
1000---16---18---7
1001---20---16---6
1001---32---19---7
1001---36---19---6
1001---28---17---7按number,weekday分组后得到如下
number sum(big) sum(small) weekday
1000-----30---------45-------6
1000-----31---------36-------7
1001-----56---------35-------6
1001-----60---------36-------7现在要统计如下
如果6,7都存在则将6,7合并为2即如下:number sum(big) sum(small) weekday
1000-----30---------36------2
1000-----0----------9--------6
1000-----1--------- 0-------7
1001-----56---------35------2
1001-----4--------- 1-------7请问sql语句如何实现?
1000---10---20---6
1000---15---18---7
1000---20---25---6
1000---16---18---7
1001---20---16---6
1001---32---19---7
1001---36---19---6
1001---28---17---7按number,weekday分组后得到如下
number sum(big) sum(small) weekday
1000-----30---------45-------6
1000-----31---------36-------7
1001-----56---------35-------6
1001-----60---------36-------7现在要统计如下
如果6,7都存在则将6,7合并为2即如下:number sum(big) sum(small) weekday
1000-----30---------36------2
1000-----0----------9--------6
1000-----1--------- 0-------7
1001-----56---------35------2
1001-----4--------- 1-------7请问sql语句如何实现?
select number,sum(big) big,sum(small) small,weekday
from tb
group by number,weekdayselect number,min(big),min(small),2
from
temp
group by number
having count(*)>1
union all
select *
from (
select A.number,case when A.big-B.big<0 then 0 else A.big-B.big end as big,
case when A.small-B.small<0 then 0 else A.small-B.small end as small,
A.weekday
from temp A,temp B
where A.number=B.number and (a.weekday=B.weekday+1 or a.weekday=B.weekday-1)
)T
where (big=0 and small>0) or (big>0 and small>0) or (big>0 and small=0)
建议你列出你的表结构,并提供测试数据以及基于这些测试数据的所对应正确结果。
参考一下这个贴子的提问方式http://topic.csdn.net/u/20091130/20/8343ee6a-417c-4c2d-9415-fa46604a00cf.html
1. 你的 create table xxx .. 语句
2. 你的 insert into xxx ... 语句
3. 结果是什么样,(并给以简单的算法描述)
4. 你用的数据库名称和版本(经常有人在MS SQL server版问 MySQL)
这样想帮你的人可以直接搭建和你相同的环境,并在给出方案前进行测试,避免文字描述理解上的误差。
如果6,7都存在则将6,7合并为2即如下:number sum(big) sum(small) weekday
1000-----30---------36------2
1000-----0----------9--------6
1000-----1--------- 0-------7
1001-----56---------35------2
1001-----4--------- 1-------7简要说明一下结果是怎样得出的
建表语句如下:
CREATE TABLE `test` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`number` VARCHAR(4),
`big` INT(11) DEFAULT NULL,
`small` INT(11) DEFAULT NULL,
`week_day` INT(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)测试数据:INSERT INTO test (number, big, small, week_day) VALUES(1000,10,20,6)
INSERT INTO test (number, big, small, week_day) VALUES(1000,15,18,7)
INSERT INTO test (number, big, small, week_day) VALUES(1000,20,25,6)
INSERT INTO test (number, big, small, week_day) VALUES(1000,16,18,7)INSERT INTO test (number, big, small, week_day) VALUES(1001,20,16,6)
INSERT INTO test (number, big, small, week_day) VALUES(1001,32,19,7)
INSERT INTO test (number, big, small, week_day) VALUES(1001,36,19,6)
INSERT INTO test (number, big, small, week_day) VALUES(1001,28,17,7)问题描述
week_day代表星期几,然后按number和weekday进行分组
sql语句如下:
SELECT number,week_day, SUM(big) AS big ,SUM(small) AS small FROM test GROUP BY number,week_day
结果如下:
number sum(big) sum(small) weekday
1000-----30---------45-------6
1000-----31---------36-------7
1001-----56---------35-------6
1001-----60---------36-------7
然后拿上面的分组结果在进行统计,如果同一个number在weekday=6或7中都存在,则将共有部分合并,并将weekday指定为2。
比如拿number=1000来说 weekday有两条分别为6和7,则将6和7共有部分合并,即应得到如下结果
number sum(big) sum(small) weekday
1000-----30---------36------2
1000-----0----------9-------6
1000-----1--------- 0-------7最后想统计出类似如下的结果:
number sum(big) sum(small) weekday
1000-----30---------36------2
1000-----0----------9-------6
1000-----1--------- 0-------7
1001-----56---------35------2
1001-----4--------- 1-------7数据库为mysql不知道这样是否描述清楚,谢谢大家的指教!
+----+--------+------+-------+----------+
| id | number | big | small | week_day |
+----+--------+------+-------+----------+
| 1 | 1000 | 10 | 20 | 6 |
| 2 | 1000 | 15 | 18 | 7 |
| 3 | 1000 | 20 | 25 | 6 |
| 4 | 1000 | 16 | 18 | 7 |
| 5 | 1001 | 20 | 16 | 6 |
| 6 | 1001 | 32 | 19 | 7 |
| 7 | 1001 | 36 | 19 | 6 |
| 8 | 1001 | 28 | 17 | 7 |
+----+--------+------+-------+----------+
8 rows in set (0.05 sec)mysql> SELECT number,week_day, SUM(big) AS big ,SUM(small) AS small FROM test GR
OUP BY number,week_day;
+--------+----------+------+-------+
| number | week_day | big | small |
+--------+----------+------+-------+
| 1000 | 6 | 30 | 45 |
| 1000 | 7 | 31 | 36 |
| 1001 | 6 | 56 | 35 |
| 1001 | 7 | 60 | 36 |
+--------+----------+------+-------+
4 rows in set (0.09 sec)mysql> select number,2 as week_day,min(big) as big,min(small) as small
-> from (
-> SELECT number,week_day, SUM(big) AS big ,SUM(small) AS small
-> FROM test
-> where week_day=6 or week_day=7
-> GROUP BY number,week_day
-> ) t
-> group by number
-> having count(*)>1
->
-> union all
->
-> select t1.number,t1.week_day,
-> if(t1.week_day=6 or t1.week_day=7,t1.big-t2.big,t1.big) as big ,
-> if(t1.week_day=6 or t1.week_day=7,t1.small-t2.small,t1.small) as small
-> from (
-> SELECT number,week_day, SUM(big) AS big ,SUM(small) AS small
-> FROM test
-> GROUP BY number,week_day
-> ) t1 inner join (select number,2 as week_day,min(big) as big,min(small) as small
-> from (
-> SELECT number,week_day, SUM(big) AS big ,SUM(small) AS small
-> FROM test
-> where week_day=6 or week_day=7
-> GROUP BY number,week_day
-> ) t
-> group by number
-> having count(*)>1
-> ) t2 on t1.number=t2.number
-> order by number,week_day;
+--------+----------+------+-------+
| number | week_day | big | small |
+--------+----------+------+-------+
| 1000 | 2 | 30 | 36 |
| 1000 | 6 | 0 | 9 |
| 1000 | 7 | 1 | 0 |
| 1001 | 2 | 56 | 35 |
| 1001 | 6 | 0 | 0 |
| 1001 | 7 | 4 | 1 |
+--------+----------+------+-------+
6 rows in set (0.00 sec)mysql>