klor's questions

我在 MySQL 数据库中有两个表。

Table1
a_id
1
3
4
5
6


Table2
b_id
1
2
3

我想从 Table1 a_id 2 中找出缺失的行，并从 Table2 b_id 4、5、6 中找出缺失的行，这些行应该显示在 deleted_from_a、deleted_from_b 列中，值为 1。

Expected output:
+----+----+--------------+--------------+
|a_id|b_id|deleted_from_a|deleted_from_b|
|   1|   1|             0|             0|
|    |   2|             1|             0|
|   3|   3|             0|             0|
|   4|    |             0|             1|
|   5|    |             0|             1|
|   6|    |             0|             1|
+----+----+--------------+--------------+

如何查询输出结果？

编辑1：

更新了表 1，与表 2 相比具有附加值。

编辑2：

更新了规范，一开始并不完美。

我有一个查询，其中列出了这些用户，即学生：

SELECT  *
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE 
ra.roleid = 5  /* is student */

我有一个带有用户 ID 的表，他们至少登录过一次：

SELECT * FROM mdl_user_lastaccess   

我需要列出所有学生用户，并有一个新列，显示用户是否登录。已登录：值 1，未登录：值 0。

我想出了这个查询，但登录列始终为 1。缺少 0 值：

SELECT  *, (SELECT IF ((SELECT count(*) as c FROM mdl_user as u, mdl_user_lastaccess as ula WHERE u.id = ula.userid), 1, 0)) as loggedin
FROM mdl_user as u
JOIN mdl_role_assignments as ra ON u.id = ra.userid

WHERE 
ra.roleid = 5   

知道如何解决吗？

错误的解决方案：

select u.*,
       case
       when ul.userid is null then 0
       else 1
       end loggedin
  from mdl_user u
  join mdl_role_assignments ra
    on u.id = ra.userid
  left join (
     select ula.userid
       from mdl_user_lastaccess ula
      group by ula.userid
) ul
on u.id = ul.userid
WHERE 
ra.roleid = 5

ORDER BY loggedin DESC

结果 455 行，其中包含重复项。

好的解决方案：

SELECT DISTINCT u.*, IF(ul.userid IS NULL, 0, 1) AS loggedin, ul.courseid
FROM mdl_user u
JOIN mdl_role_assignments ra ON u.id = ra.userid
LEFT JOIN (
    SELECT ula.userid, ula.courseid
    FROM mdl_user_lastaccess ula
    GROUP BY ula.userid
) ul ON u.id = ul.userid
WHERE ra.roleid = 5
ORDER BY loggedin DESC

我不得不使用 DISTINCT 来忽略重复项。

最终的好解决方案：

SELECT  u.*, IF(la.userid IS NULL, 0, 1) AS loggedin, la.courseid
  FROM mdl_user                 AS u
  JOIN mdl_role_assignments     AS ra ON ra.userid = u.id
  LEFT JOIN mdl_user_lastaccess AS la ON la.userid = u.id
 WHERE ra.roleid = 5           -- is student
 GROUP BY u.id                 -- to collapse all sessions for certain user
 ORDER BY loggedin DESC

path我有一个存储函数，它应该用列中的名称替换列中的类别 ID name。然后将结果字符串存储到名为 的新列path_long中。

我使用 Debian 8，MySQL v5.5。

例子

我有一个名为path“/426/427/428”的列。我想用类别名称替换类别 ID 号。结果类似于“/Computers/Other accessories/Laser printers”。

我有这个存储功能：

CREATE DEFINER=`root`@`%` FUNCTION `decode_path`(
    `path_input` MEDIUMTEXT
)
RETURNS mediumtext CHARSET latin1
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''

BEGIN

CREATE TEMPORARY TABLE IF NOT EXISTS 
t1
ENGINE=MyISAM
AS (
    SELECT
         n AS nr
         , SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM @path_input)), '/', tmp.n), '/', -1) AS catid
         , (
                SELECT name FROM category
                WHERE category.id = catid
            ) AS name
         , (
                SELECT path FROM category
                WHERE category.id = catid
            ) AS path
    FROM
      (SELECT @rownum := @rownum + 1 AS n, category.id, category.name, category.path
            FROM category
            CROSS JOIN (SELECT @rownum := 0) r
        ) AS tmp 
    GROUP BY catid
    ORDER BY
       n
);

INSERT INTO t2
SELECT group_concat(name SEPARATOR '/') as path_long FROM t1;

RETURN (SELECT path_long FROM t2 limit 1);

END

这是测试 DDL：

CREATE TABLE `category` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `name` VARCHAR(100) NOT NULL,
    `path` VARCHAR(100) NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
AUTO_INCREMENT=429
;

还有测试数据：

INSERT INTO `category` (`id`, `name`, `path`) VALUES (1, 'A', '/1');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (2, 'B', '/1/2');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (3, 'C', '/1/2/3');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (4, 'D', '/4');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (5, 'E', '/4/5');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (6, 'F', '/4/5/6');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (7, 'G', '/7');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (8, 'H', '/7/8');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (9, 'I', '/7/8/9');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (426, 'Computers', '/426');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (427, 'Other accessories', '/426/427');
INSERT INTO `category` (`id`, `name`, `path`) VALUES (428, 'Laser printers', '/426/427/428');

不幸的是我不能改变设计。它在软件中给出。不管模式是否糟糕，数据库是否糟糕，这就是我所拥有的。框架使用这个模式，数据库是MySQL。我必须在这个系统上做查询，我必须得到想要的结果。

使用查询：

SELECT decode_path(category.path) as decoded FROM category

问题

查询结果如下：

decoded 
A
A
A
A
A
A
A
A
A
A
A
A

源列看起来像这样（显示未解码的路径）：

path
/426/427/428
/1/2/3
/4/5/6
/7/8/9

期望的结果列应该是这样的（显示解码路径）：

path_long
/Computers/Other accessories/Laser printers
/A/B/C
/D/E/F
/G/H/I

基本上它应该使用类别名称将具有类别 ID 的路径解码为可读路径格式。

如何修复存储的函数以使其工作？

使用 MySQL，按分隔符拆分列并用另一列中的值替换数字。

我有一个名为“路径”的列，其路径类似于“/426/427/428”。我想用类别名称替换类别编号。结果将类似于“/计算机/其他附件/激光打印机”。

这个 mysql 查询似乎是一个好的开始，但不知道如何从 'categories' 表、'path' 列中获取列值。

select distinct
  SUBSTRING_INDEX(SUBSTRING_INDEX((SELECT TRIM(LEADING '/' FROM '/426/427/428')), '/', numbers.n), '/', -1) name
from
  (select @rownum := @rownum + 1 as n
        from categories
        cross join (select @rownum := 0) r
    ) numbers 
where n > 0
order by
   n

现在，此查询将字符串正确拆分为：

426
427
428

接下来会是这样的结果：

id    name
426   Computers
427   Other accessories
428   Laser printers

最后应该将“名称”列合并到

'/Computers/Other accessories/Laser printers' 

细绳。

提前感谢您的帮助！

以下函数创建查询时出错：

CREATE FUNCTION `_increase_num` () RETURNS int(11)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    RETURN IF(@counter, @counter:=@counter+1, @counter:=1);
END

1. 知道如何解决吗？
2. 我可以避免使用 ; 在上面的查询中？开发框架不允许一次调用多个查询，并将此标识为多个查询。

EDIT1：函数的目的是使每一行都是唯一的，就像一个自动增加的主键。但是出于某些开发原因，我不能使用自动递增的主键。

EDIT2：错误消息很常见，查询失败。您的查询有误。

EDIT3：我这样使用函数：SELECT _increase_num() as rownr, content FROM mytable

工作解决方案：

CREATE FUNCTION _increase_num() 
returns int 
    return IF(@counter, @counter:=@counter+1, @counter:=1)