talbe 如下:Id a b name
--------------------------------------
1 | 12 | 13 | 张三 |
2 | 14 | 15 | 张三 |
3 | 16 | 17 | 张三 |
4 | 18 | 19 | 张三 |
5 | 20 | 21 | 张三 |
6 | 21 | 22 | 张三 |
------------------------------------怎么用mysql sql 语句实现 结果:Id a b name
--------------------------------------
1 | 12 | 22 | 张三 |
--------------------------------------
--------------------------------------
1 | 12 | 13 | 张三 |
2 | 14 | 15 | 张三 |
3 | 16 | 17 | 张三 |
4 | 18 | 19 | 张三 |
5 | 20 | 21 | 张三 |
6 | 21 | 22 | 张三 |
------------------------------------怎么用mysql sql 语句实现 结果:Id a b name
--------------------------------------
1 | 12 | 22 | 张三 |
--------------------------------------
from talbe
group by name
ACMAIN_CHM老大min(a)都打错了,打成mid(a)了。呵呵
--------------------------------------
1 | 12 | 13 | 张三 |
2 | 14 | 15 | 张三 |
3 | 16 | 17 | 张三 |
4 | 18 | 19 | 张三 |
5 | 20 | 21 | 张三 |
6 | 21 | 22 | 张三 |
7 | 61 | 62 | 张三 |
8 | 63 | 64 | 张三 |
9 | 82 | 83 | 张三 |
10 | 84 | 85 | 张三 |
------------------------------------ 怎么用mysql sql 语句实现 结果: Id a b name
--------------------------------------
1 | 12 | 22 | 张三 |
7 | 61 | 64 | 张三 |
9 | 82 | 85 | 张三 | 结果 id字段 可以不要
--------------------------------------
可以用一句SQL解决,但效率上不会好如果你的数据多的话。
不如用程序或者存储过程来解决。
-> from (
-> select id,a,name
-> from talbe t
-> where not exists (select 1 from talbe where t.a-1 between a and b)
-> ) a,
-> (
-> select id,b
-> from talbe t
-> where not exists (select 1 from talbe where t.b+1 between a and b)
-> ) b
-> where a.id<b.id
-> group by a.id,a.a,a.name;
+------+------+------+------+
| id | a | b | name |
+------+------+------+------+
| 1 | 12 | 22 | 张三 |
| 7 | 61 | 64 | 张三 |
| 9 | 82 | 85 | 张三 |
+------+------+------+------+
3 rows in set (0.00 sec)但不建议这种做法。效率比较差。
-> (select min(b) from talbe t1
-> where id>t.id
-> and not exists (select 1 from talbe where t1.b+1 between a and b)
-> ) as b,
-> name
-> from talbe t
-> where not exists (select 1 from talbe where t.a-1 between a and b);
+------+------+------+------+
| id | a | b | name |
+------+------+------+------+
| 1 | 12 | 22 | 张三 |
| 7 | 61 | 64 | 张三 |
| 9 | 82 | 85 | 张三 |
+------+------+------+------+
3 rows in set (0.00 sec)mysql>
(select min(b) from talbe t1
where id>t.id
and not exists (select 1 from talbe where t1.b+1 between a and b)
) as b,
name
from talbe t
where not exists (select 1 from talbe where t.a-1 between a and b);建议你先自己试着解释一下这个过程,否则别人也根本不知道你目前已经懂了什么,到什么层次,到底是哪儿不懂。写出你自己的理解,然后让别人来看你的理解是否正确。 否则别人解释过简单你还是理解不了,解释得过细,可能又是根本不必要。
select a.* from (
Select id,a,name from tta
union all
Select id,b,name from tta
order by a) a
left join
(
Select id,a,name from tta
union all
Select id,b,name from tta
order by a) b
on a.a=b.a-1 where b.id is nullselect a.* from (
Select id,a,name from tta
union all
Select id,b,name from tta
order by a) a
left join
(
Select id,a,name from tta
union all
Select id,b,name from tta
order by a) b
on a.a=b.a-1 where b.id is null将上述内容插入有自增字段的临时表, 自增字段ID1SELECT * FROM 查询1 a inner join 查询1 b on a.id1=b.id1
select a.* from (
Select id,a,name from tta
union all
Select id,b,name from tta
order by a) a
left join
(
Select id,a,name from tta
union all
Select id,b,name from tta
order by a) b
on a.a=b.a-1 where b.id is null
查询2
select a.* from (
Select id,a,name from tta
union all
Select id,b,name from tta
order by a) a
left join
(
Select id,a,name from tta
union all
Select id,b,name from tta
order by a) b
on a.a=b.a+1 where b.id is null 将上述内容插入有自增字段的临时表, 自增字段ID1 SELECT * FROM 查询1 a inner join 查询2 b on a.id1=b.id1
from talbe t
where not exists
(select * from talbe where t.a-1 between a and b);
--这样就可以了
from talbe t
where not exists (select 1 from talbe where t.a-1 between a and b);这一句是否能够理解?
where id>t.id
and not exists (select 1 from talbe where t1.b+1 between a and b)
) as b,这不明白
where id>t.id这个明不明白?建议你能多少自己解释一下,否则,真的不知道你到底哪里不明白。 not exists 这个你是否理解?select 1 from talbe where t1.b+1 between a and b
这一句可不可解释一下?
where id>t.id这个明不明白?建议你能多少自己解释一下,否则,真的不知道你到底哪里不明白。 not exists 这个你是否理解?select 1 from talbe where t1.b+1 between a and b
这一句可不可解释一下?
where id>t.id 解释下这个
where id>10
这个是否理解?
where id>t.idt.id 是什么?
where id>t.id 就明白了 不要意思啊 让你分心了
from talbe t
where not exists (select 1 from talbe where t.a-1 between a and b);t 是这个表。运行一下这个SQL语句。贴出结果。
| id | a | name |
+----+------+------+
| 1 | 12 | 张三 |
| 6 | 61 | 张三 |
| 8 | 82 | 张三 |
+----+------+------+
t.id 等于什么?
t.id 等于 1
where id>t.id则select min(b) from talbe t1
where id>1
from talbe t
where not exists (select 1 from talbe where t.a-1 between a and b);
的结果是+----+------+------+
| id | a | name |
+----+------+------+
| 1 | 12 | 张三 |
| 6 | 61 | 张三 |
| 8 | 82 | 张三 |
+----+------+------+下面这句来说, 添加了红色的部分 。红色这部分,你可以理解为它是针对上面每行都执行一遍。
select id,a,
(select min(b) from talbe t1
where id>t.id
and not exists (select 1 from talbe where t1.b+1 between a and b)
) as b,
name
from talbe t
where not exists (select 1 from talbe where t.a-1 between a and b);第一行 | 1 | 12 | 张三 | 执行一次,此时 t.id=1
第二行| 6 | 61 | 张三 | 时t.id=6 (select min(b) from talbe t1
where id>t.id
and not exists (select 1 from talbe where t1.b+1 between a and b)
) as b,
则变成
select min(b) from talbe t1
where id>1
and not exists (select 1 from talbe where t1.b+1 between a and b)
和
select min(b) from talbe t1
where id>6
and not exists (select 1 from talbe where t1.b+1 between a and b)
这里,你同样可以理解 (select 1 from talbe where t1.b+1 between a and b) 针对 t1的每行都进算一次。