select t1.id, t1.a, t2.b from (select id, count(AcSt) a from t group by id having AcSt = 1) t1, (select id, count(AcSt) b from t group by id having AcSt=0) t2 where t1.id=t2.id order by t1.id;
我照着楼上的写了下[SQL] select t1.id,t1.a,t2.b from (select id, count(AcSt) a from tab_gpsorigdatahis_201012 group by id having AcSt=1 ) t1, (select id, count(AcSt) b from tab_gpsorigdatahis_201012 group by id having AcSt=0 ) t2 where t1.id=t2.id order by t1.id[Err] 1054 - Unknown column 'AcSt' in 'having clause'
SET @a:=0; SET @b:=0; SET @c:=0; SELECT hh,MAX(IF(acst=1,gs,0)) AS ac1,MAX(IF(acst=0,gs,0)) AS ac0 FROM ( SELECT a.*,a.序号-b.序号+1 AS gs,@c:=@c+1 AS pm1,CEILING((@c)/2) AS hh FROM ( SELECT *,@a:=@a+1 AS pm FROM qtty a WHERE NOT EXISTS(SELECT 1 FROM qtty WHERE 序号=a.序号+1 AND acst=a.AcSt)) a LEFT JOIN (SELECT *,@b:=@b+1 AS pm FROM qtty a WHERE NOT EXISTS(SELECT 1 FROM qtty WHERE 序号=a.序号-1 AND acst=a.AcSt)) b ON a.pm=b.pm) aa GROUP BY hh;
[SQL] select hh,max( if (AcSt=1 ,gs,0 ) )as ac1,max( if (AcSt=0 ,gs,0 )) as ac0 from( select a.*, a.id- b.id+1 as gs,@c:= @c+1 as pm1,ceiling((@c )/2 ) as hh from(select *,@a:=@a+1 as pm from tab_gpsorigdatahis_201012 where not exists (select 1 from tab_gpsorigdatahis_201012 where id= a.id + 1 and AcSt=a.AcSt ) ) a left join ( select * , @b:= @b + 1 as pm from tab_gpsorigdatahis_201012 where not exists ( select 1 from tab_gpsorigdatahis_201012 where id = a.id - 1 and AcSt = a.AcSt ) ) b on a.pm=b.pm ) aa group by hh:[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 ':' at line 18 这个错误要怎么解决? line 18是 where id = a.id - 1 好像没错啊!
from
(select id,
count(AcSt) a
from tab_gpsorigdatahis_201012
group by id
having AcSt=1 ) t1,
(select id,
count(AcSt) b
from tab_gpsorigdatahis_201012
group by id
having AcSt=0 ) t2
where t1.id=t2.id order by t1.id[Err] 1054 - Unknown column 'AcSt' in 'having clause'
+------+------+
| id | num |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
| 4 | 0 |
| 5 | 0 |
| 6 | 1 |
| 7 | 1 |
| 8 | 1 |
| 9 | 1 |
| 10 | 0 |
| 11 | 1 |
+------+------+mysql> set @b=0;
Query OK, 0 rows affected (0.00 sec)mysql> select id,num,cou from (select id,num,@b:=if(num=@a,@b+1,1) as cou ,@a:=n
um from ddd) aa;
+------+------+------+
| id | num | cou |
+------+------+------+
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 1 | 3 |
| 4 | 0 | 1 |
| 5 | 0 | 2 |
| 6 | 1 | 1 |
| 7 | 1 | 2 |
| 8 | 1 | 3 |
| 9 | 1 | 4 |
| 10 | 0 | 1 |
| 11 | 1 | 1 |
+------+------+------+只能做到这一步,请WWWWWA和狼头大哥帮忙了.
SET @b:=0;
SET @c:=0;
SELECT hh,MAX(IF(acst=1,gs,0)) AS ac1,MAX(IF(acst=0,gs,0)) AS ac0 FROM (
SELECT a.*,a.序号-b.序号+1 AS gs,@c:=@c+1 AS pm1,CEILING((@c)/2) AS hh
FROM (
SELECT *,@a:=@a+1 AS pm FROM qtty a WHERE NOT EXISTS(SELECT 1 FROM qtty WHERE 序号=a.序号+1 AND acst=a.AcSt)) a
LEFT JOIN
(SELECT *,@b:=@b+1 AS pm FROM qtty a WHERE NOT EXISTS(SELECT 1 FROM qtty WHERE 序号=a.序号-1 AND acst=a.AcSt)) b
ON a.pm=b.pm) aa GROUP BY hh;
select hh,max( if (AcSt=1 ,gs,0 ) )as ac1,max( if (AcSt=0 ,gs,0 )) as ac0
from(
select a.*, a.id- b.id+1 as gs,@c:= @c+1 as pm1,ceiling((@c )/2 ) as hh
from(select *,@a:=@a+1 as pm
from tab_gpsorigdatahis_201012
where not exists
(select 1 from tab_gpsorigdatahis_201012
where id= a.id + 1
and AcSt=a.AcSt ) ) a
left join
( select * , @b:= @b + 1 as pm
from tab_gpsorigdatahis_201012
where not exists
( select 1 from tab_gpsorigdatahis_201012
where id = a.id - 1
and AcSt = a.AcSt ) ) b
on a.pm=b.pm ) aa
group by hh:[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 ':' at line 18
这个错误要怎么解决? line 18是 where id = a.id - 1
好像没错啊!