sql分组问题

horizon89 2011-06-01 10:01:29
-------------现有表结构和数据
CREATE TABLE `tests` (
`id` int(10) NOT NULL DEFAULT '0',
`acst` int(10) DEFAULT NULL,
`dates` datetime DEFAULT NULL,
`counts` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tests VALUES ('1', '1', '2011-06-01 00:01:04', '2');
INSERT INTO tests VALUES ('2', '1', '2011-06-01 00:01:18', '2');
INSERT INTO tests VALUES ('3', '1', '2011-06-01 00:01:34', '5');
INSERT INTO tests VALUES ('4', '0', '2011-06-01 00:01:37', '6');
INSERT INTO tests VALUES ('5', '0', '2011-06-01 00:01:38', '3');
INSERT INTO tests VALUES ('6', '1', '2011-06-01 00:01:46', '10');
INSERT INTO tests VALUES ('7', '1', '2011-06-01 00:02:00', '6');
INSERT INTO tests VALUES ('8', '1', '2011-06-01 00:02:05', '3');
INSERT INTO tests VALUES ('9', '1', '2011-06-01 00:02:21', '5');
INSERT INTO tests VALUES ('10', '1', '2011-06-01 00:02:28', '21');
INSERT INTO tests VALUES ('11', '1', '2011-06-01 00:02:43', '7');
INSERT INTO tests VALUES ('12', '0', '2011-06-01 00:02:48', '7');
INSERT INTO tests VALUES ('13', '1', '2011-06-01 21:31:45', '3');

想要得到结果(根据acst来分组区分)
id 总记录数 总量 开始时间 结束时间
1 3 9 '2011-06-01 00:01:04' '2011-06-01 00:01:34'
2 2 9 '2011-06-01 00:01:37' '2011-06-01 00:01:38'
3 6 52 '2011-06-01 00:01:46' '2011-06-01 00:02:43'
4 1 7 '2011-06-01 00:02:48'
5 1 3 '2011-06-01 21:31:45'

--请各位大哥大姐帮忙。。
...全文
139 10 打赏 收藏 举报
写回复
10 条回复
切换为时间正序
当前发帖距今超过3年,不再开放新的回复
发表回复
Mirror然 2011-06-02
整理上面的 估计是没表示清楚
  • 打赏
  • 举报
回复
Mirror然 2011-06-02

create view cte1 as
select a.*,Case acst when (select top 1 acst from tests where id<a.id order by id desc) then 0 else 1 end as T from
tests a

create view cte2 as
select a.*,(select SUM(T) from cte1 where id<=a.id) as Group_No from cte1 a


select Group_No as id,
count(acst) as 总记录数,
sum(counts) as 总量,
(select MIN(Dates) from cte2 where Group_No=a.Group_No) as 开始时间,
case when count(acst)=1 then '' else (select Max(Dates) from cte2 where Group_No=a.Group_No) end as 结束时间
from cte2 a group by Group_No

1 3 9 2011-06-01 00:01:04.000 2011-06-01 00:01:34.000
2 2 9 2011-06-01 00:01:37.000 2011-06-01 00:01:38.000
3 6 52 2011-06-01 00:01:46.000 2011-06-01 00:02:43.000
4 1 7 2011-06-01 00:02:48.000 1900-01-01 00:00:00.000
5 1 3 2011-06-01 21:31:45.000 1900-01-01 00:00:00.000
  • 打赏
  • 举报
回复
bihai 2011-06-02
;with cte1 as
(select a.*,Case acst when (select top 1 acst from tests where id<a.id order by id desc) then 0 else 1 end as T from
tests a),
cte2 as
(select a.*,(select SUM(T) from cte1 where id<=a.id) as Group_No from cte1 a)

select Group_No as id,
count(acst) as 总记录数,
sum(counts) as 总量,
(select MIN(Dates) from cte2 where Group_No=a.Group_No) as 开始时间,
case when count(acst)=1 then '' else (select Max(Dates) from cte2 where Group_No=a.Group_No) end as 结束时间
from cte2 a group by Group_No
  • 打赏
  • 举报
回复
horizon89 2011-06-02
分已经送出。。。谢谢大家。。。
  • 打赏
  • 举报
回复
horizon89 2011-06-02
感谢wwwwgou和FlySQL帮忙。。同时也谢谢各位参与。。。
  • 打赏
  • 举报
回复
FlySQL 2011-06-02
CREATE TABLE `tests` (
`id` int(10) NOT NULL DEFAULT '0',
`acst` int(10) DEFAULT NULL,
`dates` datetime DEFAULT NULL,
`counts` int(10) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO tests VALUES ('1', '1', '2011-06-01 00:01:04', '2');
INSERT INTO tests VALUES ('2', '1', '2011-06-01 00:01:18', '2');
INSERT INTO tests VALUES ('3', '1', '2011-06-01 00:01:34', '5');
INSERT INTO tests VALUES ('4', '0', '2011-06-01 00:01:37', '6');
INSERT INTO tests VALUES ('5', '0', '2011-06-01 00:01:38', '3');
INSERT INTO tests VALUES ('6', '1', '2011-06-01 00:01:46', '10');
INSERT INTO tests VALUES ('7', '1', '2011-06-01 00:02:00', '6');
INSERT INTO tests VALUES ('8', '1', '2011-06-01 00:02:05', '3');
INSERT INTO tests VALUES ('9', '1', '2011-06-01 00:02:21', '5');
INSERT INTO tests VALUES ('10', '1', '2011-06-01 00:02:28', '21');
INSERT INTO tests VALUES ('11', '1', '2011-06-01 00:02:43', '7');
INSERT INTO tests VALUES ('12', '0', '2011-06-01 00:02:48', '7');
INSERT INTO tests VALUES ('13', '1', '2011-06-01 21:31:45', '3');

set @rowNo = 0;
select
(@rowNo := @rowNo + 1) as id,
sum(counts) as `总记录数`,
min(dates) as `开始时间`,
case when max(dates)=min(dates) then NULL else max(dates) end as `结束时间`
from(
select *,
id-(select count(1) from tests where acst=t.acst and id<t.id) as groupid
from tests as t
) a
group by groupid,acst

/**
1 9 2011-06-01 00:01:04 2011-06-01 00:01:34
2 52 2011-06-01 00:01:46 2011-06-01 00:02:43
3 9 2011-06-01 00:01:37 2011-06-01 00:01:38
4 3 2011-06-01 21:31:45 NULL
5 7 2011-06-01 00:02:48 NULL
**/
  • 打赏
  • 举报
回复
horizon89 2011-06-02

[Err] 1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(ORDER BY groupid),
总记录数 = COUNT(*),
开始时间 = MIN(dates' at line 2

请问wwwwgou大哥,这个语句是不是不支持mysql。。
  • 打赏
  • 举报
回复
Shawn 2011-06-02
CREATE TABLE #temp (
id int NOT NULL DEFAULT '0',
acst int DEFAULT NULL,
dates datetime DEFAULT NULL,
counts int DEFAULT NULL,
PRIMARY KEY (id)
)

INSERT INTO #temp VALUES ('1', '1', '2011-06-01 00:01:04', '2');
INSERT INTO #temp VALUES ('2', '1', '2011-06-01 00:01:18', '2');
INSERT INTO #temp VALUES ('3', '1', '2011-06-01 00:01:34', '5');
INSERT INTO #temp VALUES ('4', '0', '2011-06-01 00:01:37', '6');
INSERT INTO #temp VALUES ('5', '0', '2011-06-01 00:01:38', '3');
INSERT INTO #temp VALUES ('6', '1', '2011-06-01 00:01:46', '10');
INSERT INTO #temp VALUES ('7', '1', '2011-06-01 00:02:00', '6');
INSERT INTO #temp VALUES ('8', '1', '2011-06-01 00:02:05', '3');
INSERT INTO #temp VALUES ('9', '1', '2011-06-01 00:02:21', '5');
INSERT INTO #temp VALUES ('10', '1', '2011-06-01 00:02:28', '21');
INSERT INTO #temp VALUES ('11', '1', '2011-06-01 00:02:43', '7');
INSERT INTO #temp VALUES ('12', '0', '2011-06-01 00:02:48', '7');
INSERT INTO #temp VALUES ('13', '1', '2011-06-01 21:31:45', '3');
go
--SQL:
SELECT
id = ROW_NUMBER() OVER(ORDER BY groupid),
总记录数 = COUNT(*),
开始时间 = MIN(dates),
结束时间 = CASE WHEN (MAX(dates) = MIN(dates)) THEN NULL ELSE MAX(dates) END
FROM
(SELECT groupid = id-ROW_NUMBER() OVER(PARTITION BY acst ORDER BY id),* FROM #temp) T
GROUP BY acst, groupid
ORDER BY MAX(id)
/*
id 总记录数 开始时间 结束时间
1 3 2011-06-01 00:01:04.000 2011-06-01 00:01:34.000
3 2 2011-06-01 00:01:37.000 2011-06-01 00:01:38.000
2 6 2011-06-01 00:01:46.000 2011-06-01 00:02:43.000
5 1 2011-06-01 00:02:48.000 NULL
4 1 2011-06-01 21:31:45.000 NULL
*/

  • 打赏
  • 举报
回复
horizon89 2011-06-01
请问大哥是怎么执行的了。。。贴上去有语法错误,还有那个总数能一起统计么!!
  • 打赏
  • 举报
回复
bihai 2011-06-01
范例供参考:
ID | State | Date

1 1 2010-11-01 08:08:08
2 1 2010-11-01 08:09:08
3 1 2010-11-01 08:10:08
4 1 2010-11-01 08:11:08
5 1 2010-11-01 08:12:08
6 1 2010-11-01 08:13:08
7 1 2010-11-01 08:14:08
8 0 2010-11-01 08:15:08
9 0 2010-11-01 08:16:08
10 0 2010-11-01 08:17:08
11 0 2010-11-01 08:18:08
12 0 2010-11-01 08:19:08
13 1 2010-11-01 08:20:08
14 1 2010-11-01 08:21:08
15 1 2010-11-01 08:22:08
16 1 2010-11-01 08:23:08

with cte as
(select a.*,Case state when (select top 1 state from tb where Date<a.Date order by Date desc) then 0 else 1 end as T from
tb a),
cte2 as
(select id,state,Date,(select SUM(T) from cte where Date<=a.Date) as Group_No from cte a)

select Group_No,State,
(select MIN(Date) from cte2 where Group_No=a.Group_No) as StartDate,
(select Max(Date) from cte2 where Group_No=a.Group_No) as EndDate
from cte2 a group by Group_No,State

Group_No State StartDate EndDate
----------- ----------- ----------------------- -----------------------
1 1 2010-11-01 08:08:08.000 2010-11-01 08:14:08.000
2 0 2010-11-01 08:15:08.000 2010-11-01 08:19:08.000
3 1 2010-11-01 08:20:08.000 2010-11-01 08:23:08.000

(3 行受影响)


  • 打赏
  • 举报
回复
发帖
疑难问题

2.2w+

社区成员

MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
加入社区
帖子事件
创建了帖子
2011-06-01 10:01
社区公告
暂无公告