首页 > 绿虎论坛 > 电脑 > 讨论/求助 (发帖)

求助一段SQL代码

李沐沐
@Ta 2022-11-24发布，2022-11-24修改 22586点击
有这样一个表，数据只添加了这些数据


--
-- 表的结构 `personnels_too`
--

CREATE TABLE `personnels_too` (
  `id` int(11) NOT NULL COMMENT 'ID',
  `name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '姓名',
  `relationship` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '关系',
  `Gender` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '性别',
  `nation` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '民族',
  `uid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '身份证号码',
  `phone` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机号码',
  `Culture` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '文化程度',
  `marriage` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '婚姻状况',
  `family` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '家庭户主',
  `family_uid` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '户主身份证',
  `life` int(11) DEFAULT NULL COMMENT '生存状况（）',
  `Village` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '村组',
  `remarks` text COLLATE utf8_unicode_ci COMMENT '备注',
  `WorkingAddress` text COLLATE utf8_unicode_ci,
  `addtime` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `uptime` varchar(255) COLLATE utf8_unicode_ci NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='常驻人员名单';

--
-- 转存表中的数据 `personnels_too`
--

INSERT INTO `personnels_too` (`id`, `name`, `relationship`, `Gender`, `nation`, `uid`, `phone`, `Culture`, `marriage`, `family`, `family_uid`, `life`, `Village`, `remarks`, `WorkingAddress`, `addtime`, `uptime`) VALUES
(1, '张三', '户主', '男', '汉', '101100202202052252', '18888888888', '高中', '已婚', '张三', '101100202202052252', 1, '田系组', NULL, NULL, '', ''),
(2, '李娥', '配偶', '女', '汉', '101100202102052243', '18888888888', '高中', '已婚', '张三', '101100202202052252', 1, '田系组', NULL, NULL, '', ''),
(3, '王五', '户主', '男', '汉', '101100201102052243', '18888888888', '高中', '已婚', '王五', '101100201102052243', 1, '田系组', NULL, NULL, '', ''),
(4, '丽丽', '女', '女 ', '汉', '101100201702052243', '18888888888', '高中', '已婚', '王五', '101100201102052243', 1, '田系组', NULL, NULL, '', ''),
(5, '李爱', '女儿', '女', '汉', '101100202204052243', '18888888888', '高中', '未婚', '张三', '101100202202052252', 1, '田系组', NULL, NULL, '', '');

--
-- 转储表的索引
--

--
-- 表的索引 `personnels_too`
--
ALTER TABLE `personnels_too`
  ADD PRIMARY KEY (`id`),
  ADD UNIQUE KEY `id` (`id`);

--
-- 在导出的表使用AUTO_INCREMENT
--

--
-- 使用表AUTO_INCREMENT `personnels_too`
--
ALTER TABLE `personnels_too`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT COMMENT 'ID', AUTO_INCREMENT=6;
COMMIT;

`


上面的人员数据是乱插入的，输出的是这样
户1.png（32.04 KB）
然后，我想实现如下效果：
户2.png（23.33 KB）

用的sql是
SELECT * FROM (SELECT `name`,`family_uid` FROM `personnels_too` WHERE `uid`=`family_uid`) as dd RIGHT JOIN (SELECT `name`,`family_uid` FROM `personnels_too`) as vv ON dd.`family_uid`=vv.`family_uid`


数据少的时候，速度还可以，但到2000多人以上，速度就直线下降，各位有什么优化方式吗，

具体就是让后插入的人员，要在前端和户主排在一起，并且户主显示在该组的最上面
隐藏样式 查看源码
回复列表(17|隐藏机器人聊天)
  • 1
    李沐沐
    @Ta / 2022-11-24 / 样 / 源
    能不能把审核去掉啊，管理
  • 2
    罐子
    @Ta / 2022-11-24 / 样 / 源

    @李沐沐，你需要呼叫老虎，但是吧，就是管理发的帖子也要审核。
    一加8Pro

  • 3
    罐子
    @Ta / 2022-11-24 / 样 / 源

    @李沐沐，可以试试读写分离。
    一加8Pro

  • 4
    罐子
    @Ta / 2022-11-24 / 样 / 源

    @李沐沐，具体请教老虎大大。
    一加8Pro

  • 5
    无名啊
    @Ta / 2022-11-24 / 样 / 源

    @李沐沐，不能写成这样吗？

    SQLite 代码

    看需要加索引

    WITH
  data(id, name, relationship, Gender, nation, uid, phone, Culture, marriage, family, family_uid, life, Village, remarks, WorkingAddress, addtime, uptime) AS (
    VALUES
(1, '张三', '户主', '男', '汉', '101100202202052252', '18888888888', '高中', '已婚', '张三', '101100202202052252', 1, '田系组', NULL, NULL, '', ''),
(2, '李娥', '配偶', '女', '汉', '101100202102052243', '18888888888', '高中', '已婚', '张三', '101100202202052252', 1, '田系组', NULL, NULL, '', ''),
(3, '王五', '户主', '男', '汉', '101100201102052243', '18888888888', '高中', '已婚', '王五', '101100201102052243', 1, '田系组', NULL, NULL, '', ''),
(4, '丽丽', '女儿', '女', '汉', '101100201702052243', '18888888888', '高中', '已婚', '王五', '101100201102052243', 1, '田系组', NULL, NULL, '', ''),
(5, '李爱', '女儿', '女', '汉', '101100202204052243', '18888888888', '高中', '未婚', '张三', '101100202202052252', 1, '田系组', NULL, NULL, '', '')
  )

SELECT family_uid, id, name, relationship, Gender, nation, uid, phone, Culture, marriage
FROM data
ORDER BY family_uid, family_uid != uid, id;
-- ORDER BY family_uid, relationship != '户主', id;

    结果

    family_uid id name relationship Gender nation uid phone Culture marriage
    101100201102052243 3 王五 户主 男 汉 101100201102052243 18888888888 高中 已婚
    101100201102052243 4 丽丽 女儿 女 汉 101100201702052243 18888888888 高中 已婚
    101100202202052252 1 张三 户主 男 汉 101100202202052252 18888888888 高中 已婚
    101100202202052252 2 李娥 配偶 女 汉 101100202102052243 18888888888 高中 已婚
    101100202202052252 5 李爱 女儿 女 汉 101100202204052243 18888888888 高中 未婚
  • 6
    残缘
    @Ta / 2022-11-24 / 样 / 源
    感觉怪怪的啊， 你直接order by family_uid不行吗
  • 7
    李沐沐
    @Ta / 2022-11-24 / 样 / 源
    @残缘，不行啊，试了，效果不行，户主会和子排序错乱。
  • 8
    李沐沐
    @Ta / 2022-11-24 / 样 / 源
    @老虎会游泳
  • 9
    残缘
    @Ta / 2022-11-24 / 样 / 源
    @李沐沐，不会乱啊，order两个字段啊
  • 10
    无名啊
    @Ta / 2022-11-24 / 样 / 源

    @李沐沐，5 楼的尝试了吗？效果咋样？

    SELECT family_uid, id, name, relationship, Gender, nation, uid, phone, Culture, marriage
FROM personnels_too
ORDER BY family_uid, family_uid != uid, id;
  • 11
    李沐沐
    @Ta / 2022-11-26 / 样 / 源
    @无名啊，还没，明天试试
  • 12
    李沐沐
    @Ta / 2022-12-07 / 样 / 源
    @无名啊，可以是可以，可是ID会很乱，3000多的ID都会跑到前面来,你放后面的ID不起作用
  • 13
    无名啊
    @Ta / 2022-12-07 / 样 / 源

    @李沐沐，你还希望，户主ID较小的整个家庭排前面，是嘛？若是：

    SQL 代码

    若速度太慢，可加索引。看具体需要（加个 KEY(uid) 应该可以大幅加速。但总共就几千行，原来的速度应该也不会太慢）

    SELECT a.family_uid, a.id, a.name, a.relationship, a.Gender, a.nation, a.uid, a.phone, a.Culture, a.marriage
FROM personnels_too a
JOIN personnels_too b ON b.uid = a.family_uid
ORDER BY b.id, a.family_uid != a.uid, a.id;

    结果

    family_uid id name relationship Gender nation uid phone Culture marriage
    101100202202052252 1 张三 户主 男 汉 101100202202052252 18888888888 高中 已婚
    101100202202052252 2 李娥 配偶 女 汉 101100202102052243 18888888888 高中 已婚
    101100202202052252 5 李爱 女儿 女 汉 101100202204052243 18888888888 高中 未婚
    101100201102052243 3 王五 户主 男 汉 101100201102052243 18888888888 高中 已婚
    101100201102052243 4 丽丽 女儿 女 汉 101100201702052243 18888888888 高中 已婚
  • 14
    李沐沐
    @Ta / 2022-12-07 / 样 / 源
    @无名啊，看来需要加索引，共 3546 行, 查询花费 2.2645 秒。
  • 15
    李沐沐
    @Ta / 2022-12-07 / 样 / 源
    @李沐沐，索引加到哪几个字段
  • 16
    无名啊
    @Ta / 2022-12-07 / 样 / 源

    @李沐沐，加个 KEY(uid) 试试？

    另外，如果你能将 family_uid 的实际意义，从 身份证号 换成 户主id，就不用加 KEY(uid) 了

  • 17
    李沐沐
    @Ta / 2022-12-07 / 样 / 源
    @无名啊，加上索引，速度起飞，谢谢
添加新回复
回复需要登录。