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'

--请各位大哥大姐帮忙。。
...全文
148 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
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 行受影响)


SQL中分组问题
关于SQl中分组中在分组的问题 举例: 写子查询可以先把符合的选出来，然后用选出来的数据与原表做内连接，有几个字段写几个on，这样就可以把符合的数据筛选出来，group by后面可以加多个字段进行分组，...
SQL分组查询数据
我们在生活中会碰到不少分组的情况，比如常见的根据性别来分组，男的一组，女的一组，所以在SQL中也可以对数据进行分组，这样可以更加便利的操作数据 1.Group By 语句 Group By 从字面意义上理解就是根据“By”...
SQL的分组查询
SQL的分组查询 一、在SQL中Group By从字面的意思上理解就是根据“By”指定的规则对数据进行分组，所谓的分组就是将一个‘数据集’划分成若干个‘小区域’，然后针对若干个‘小区域’进行数据处理。在此语法中group ...
sql重复数据查询,sql分组数据查询,sql分组数据的总条数,sql分组后数据的条数（条数大于1的显示）, 删除多余重复数据
sql重复数据查询,sql分组数据查询,sql分组数据的总条数,sql分组后数据的条数（条数大于1的显示）, 删除多余重复数据
SQL 分组条件深入剖析
追根溯源，上面 SQL 反映出来的问题，它缺少单纯的分组动作，不能显式的表达每个小分组，进而对每个小分组的更细致操作就无法直观表达。1、 按设备分组，找到每个设备下Ready的个数c1；2、 按设备分组，找到每个设备...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章