mysql-查询举例

m0_73234807

已于 2024-03-20 15:39:35 修改

阅读量90

点赞数

分类专栏： sql查询文章标签： sql 数据库 mysql

于 2023-10-26 20:21:42 首次发布

本文链接：https://blog.csdn.net/m0_73234807/article/details/134063997

版权

sql查询专栏收录该内容

2 篇文章 0 订阅

订阅专栏

本文介绍了MySQL中如何进行迭代查询父节点，包括在MySQL 8.0及5.7版本下的不同做法。此外，还讨论了数据去重的策略以及如何执行除运算。这些技巧对于优化SQL查询效率和处理数据库数据具有重要作用。

摘要由CSDN通过智能技术生成

迭代查父节点

Mysql 8.0支持以下语句

drop table if exists t_org;
CREATE TABLE t_org (
	id VARCHAR(35) NULL,
	parentid VARCHAR(35) NULL,
	name VARCHAR(200) NULL
);

INSERT INTO t_org (id, parentid, name) VALUES('0', null, '南京');
INSERT INTO t_org (id, parentid, name) VALUES('11', '0', '海南');
INSERT INTO t_org (id, parentid, name) VALUES('12', '0', '深圳');
INSERT INTO t_org (id, parentid, name) VALUES('13', '0', '汕头');

INSERT INTO t_org (id, parentid, name) VALUES('123', '11','海南');
INSERT INTO t_org (id, parentid, name) VALUES('124', '12','深圳');
INSERT INTO t_org (id, parentid, name) VALUES('125', '13','汕头');

INSERT INTO t_org (id, parentid, name) VALUES( '1234','123','海司');
INSERT INTO t_org (id, parentid, name) VALUES( '1235','124','深店');
INSERT INTO t_org (id, parentid, name) VALUES( '1236','125','汕店');

WITH RECURSIVE tree_cte as
 (
SELECT o.id,
	   o.name,
	   o.parentid,
	   0 AS lv
  FROM t_org o
 WHERE o.id = '1235'
UNION ALL
SELECT o.id,
	   o.name,
	   o.parentid,
	   lv + 1
  FROM t_org o
  JOIN tree_cte tcte
    ON o.id = tcte.parentid
 )
 SELECT id,name,lv,parentid from tree_cte ORDER BY lv ;

Mysql 5.7及以下支持下面语句

drop table if exists t_org;
CREATE TABLE t_org (
	id VARCHAR(35) NULL,
	parentid VARCHAR(35) NULL,
	name VARCHAR(200) NULL
);

INSERT INTO t_org (id, parentid, name) VALUES('0', null, '南京');
INSERT INTO t_org (id, parentid, name) VALUES('11', '0', '海南');
INSERT INTO t_org (id, parentid, name) VALUES('12', '0', '深圳');
INSERT INTO t_org (id, parentid, name) VALUES('13', '0', '汕头');

INSERT INTO t_org (id, parentid, name) VALUES('123', '11','海南');
INSERT INTO t_org (id, parentid, name) VALUES('124', '12','深圳');
INSERT INTO t_org (id, parentid, name) VALUES('125', '13','汕头');

INSERT INTO t_org (id, parentid, name) VALUES( '1234','123','海司');
INSERT INTO t_org (id, parentid, name) VALUES( '1235','124','深店');
INSERT INTO t_org (id, parentid, name) VALUES( '1236','125','汕店');

SELECT t2.id,
       t2.name,
       t1.sort,
       t2.parentid
FROM 
    (SELECT @r AS _id,         
            (SELECT @r:=ttt.parentid FROM t_org ttt WHERE ttt.id = _id) AS parent_id, 
            @s:=@s + 1 AS sort
	    FROM (SELECT @r:='1235', @s:=0) temp, 
             t_org tt
	 ) t1
 JOIN t_org t2 ON t1._id = t2.id;

去重

-- 笛卡尔积

select a.org_id,a.id a_id,b.id b_id

 from t_user a  left join t_user b on a.org_id = b.org_id

 order by a.org_id;

-- 取机构的最小用户id

select a.org_id,a.id from t_user a where not exists(select 1 from t_user b where a.org_id=b.org_id and a.id > b.id);

-- 取 除了机构的最小用户id 的其他数据

select a.org_id,a.id from t_user a where exists(select 1 from t_user b where a.org_id=b.org_id and a.id > b.id);

-- 取机构的最大用户id

select a.org_id,a.id from t_user a where not exists(select 1 from t_user b where a.org_id=b.org_id and a.id < b.id);

-- 取 除了 机构的最大用户id  的其他数据

select a.org_id,a.id from t_user a where exists(select 1 from t_user b where a.org_id=b.org_id and a.id < b.id);

除运算

-- 除运算
create table t1(var1 varchar(10),var2 varchar(10));
create table t2(var2 varchar(10));

insert into t1 select 'a','1';
insert into t1 select 'a','2';
insert into t1 select 'a','3';
insert into t1 select 'b','1';
insert into t1 select 'b','2';

insert into t2 select '1';
insert into t2 select '2';
insert into t2 select '3';

select aaa.* from t1 aaa where aaa.var1 not in(
select aa.var1 from 
(select a.var1,b.var2 from t1 a,t2 b) aa
left join t1 bb on aa.var1=bb.var1
and aa.var2=bb.var2
where bb.var1 is null
)