有一个数据库表tb:id,name1,name2
1,a,0
2,b,0
3,c,0
4,d,0
5,0,e
6,0,f
7,0,g
...
我想查询的结果name1=0的和name2=0的相隔开,例如结果为
1,a,0
5,0,e
2,b,0
6,0,f
3,c,0
7,0,g
...
id是自动增加的key,请问这个可以实现吗?
1,a,0
2,b,0
3,c,0
4,d,0
5,0,e
6,0,f
7,0,g
...
我想查询的结果name1=0的和name2=0的相隔开,例如结果为
1,a,0
5,0,e
2,b,0
6,0,f
3,c,0
7,0,g
...
id是自动增加的key,请问这个可以实现吗?
建立个临时表,然后定义2个变量m、n(一个为name1=0的每次取完一条记录的最小id值,一个为name2=0的每次取完一条记录的最小id值),然后用变量结合条件(name1=0或name2=0取得最小ID记录值,且id值要大于上次m、n的),然后循环搜索,把每次对应的记录插入临时表,直到记录梭梭所有记录为止,然后把临时表的结果显示。
declare s_name2 varchar(4);
declare cur_2 cursor for select name1,name2 from tb;
declare exit handler for not found close cur_2;
set @ji = 1;
set @ou = 2;
open cur_2;
repreat
fetch cur_2 into s_name1,s_name2;
if s_name2 = 0
insert table_111 then
values(@ji,s_name1,s_name2); set @ji = @ji + 2
else
insert table_111
values(@ou,s_name1,s_name2); set @ou = @ou +2 ; end ifuntil 0 end repeat;
close cur_2;
end;
呼,敲打了半天。。table_111的表结构自己定义
最后
select * from table_111 order by id
就是你要的结果了。。 应该是行吧。楼下的帮忙指错下
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
| 1 | a | 0 |
| 2 | b | 0 |
| 3 | c | 0 |
| 4 | d | 0 |
| 5 | 0 | e |
| 6 | 0 | f |
| 7 | 0 | g |
+----+-------+-------+
7 rows in set (0.00 sec)mysql> select id,name1,name2
-> from t_yunfeifan a
-> order by if(name2='0',
-> (select count(*) from t_yunfeifan where id<=a.id and name2='0') ,
-> (select count(*) from t_yunfeifan where id<=a.id and name1='0')
-> ) ,id;
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
| 1 | a | 0 |
| 5 | 0 | e |
| 2 | b | 0 |
| 6 | 0 | f |
| 3 | c | 0 |
| 7 | 0 | g |
| 4 | d | 0 |
+----+-------+-------+
7 rows in set (0.00 sec)mysql>
id1 = id2=-1;
sql1 ="select * from (select * from tb where name2=0) where id>"id1;
sql3 ="select * from (select * from tb where name1 = 0) where id>"id2;while(rs1.next()&&rs2.next())
{
id1 = rs.getValue(0);//我瞎写的,不过就是表达这个意思了。获取第一列的值,具体代码就自己写喽~id2 = rs.getValue(0);tb3.insert(rs1...);//sql1的结果
tb3.insert(rs2...);//sql2的结果
}
mysql> select * from t_yunfeifan
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
| 1 | a | 0 |
| 2 | b | 0 |
| 3 | c | 0 |
| 4 | d | 0 |
| 5 | 0 | e |
| 6 | 0 | f |
| 7 | 0 | g |
| 8 | h | 0 |
+----+-------+-------+
8 rows in set (0.00 sec)再用你的查询语句
mysql> select id,name1,name2 from t5 a order by if(name2='0',(select count(*) fr
om t5 where id<=a.id and name2='0'),(select count(*) from t5 where id<=a.id and
name1='0')),id;结果:
+----+-------+-------+
| id | name1 | name2 |
+----+-------+-------+
| 1 | a | 0 |
| 5 | 0 | e |
| 2 | b | 0 |
| 6 | 0 | f |
| 3 | c | 0 |
| 7 | 0 | g |
| 4 | d | 0 |
| 8 | h | 0 |
+----+-------+-------+
8 rows in set (0.00 sec)已经不是LZ想要的结果了。