时间戳

首页 > 解决方案 > 如何查询EF中的相邻行？

c# - 如何查询EF中的相邻行？

问题描述

我有一个显示时间线的应用程序，我希望日历事件显示在图表中间。因此，我还想在查询中返回前 10 行和后 10 行。例如，假设我有这张表：

-------------------------------------------------
| id | user_id | date    | account_balance | action
-------------------------------------------------
.................................................
| 98  |    1   | 6/6/20  | 1524            | null
| 99  |    2   | 6/6/20  | 32              | null
| 100 |    1   | 6/7/20  | 1524            | null
| 101 |    2   | 6/7/20  | 32              | null
| 102 |    1   | 6/8/20  | 1524            | null
| 103 |    2   | 6/9/20  | 32              | null
| 104 |    1   | 6/9/20  | 1524            | null
| 105 |    2   | 6/10/20 | 32              | null
| 106 |    1   | 6/10/20 | 1560            | deposit
| 107 |    2   | 6/11/20 | 32              | null
| 108 |    1   | 6/11/20 | 1560            | null
| 109 |    2   | 6/12/20 | 32              | null
| 110 |    1   | 6/12/20 | 1560            | null
| ...............................................

如何查询最新存款 ( 'WHERE action = deposit and user_id = 1') 并返回第 106 行以及 ...92,94,96,98,100,102,104 和 108,110,112,114... 有没有办法通过使用 EF 的单个查询来实现这一点？

标签： c#mysqldatabaseasp.net-coreentity-framework-core

解决方案

对于您的数据，只要只有一个 id

CREATE TABLE account (
  `id` INTEGER,
  `user_id` INTEGER,
  `date` DATETIME,
  `account_balance` INTEGER,
  `action` VARCHAR(7)
);

INSERT INTO account
  (`id`, `user_id`, `date`, `account_balance`, `action`)
VALUES
  ('98', '1', '6/6/20', '1524', 'null'),
  ('99', '2', '6/6/20', '32', 'null'),
  ('100', '1', '6/7/20', '1524', 'null'),
  ('101', '2', '6/7/20', '32', 'null'),
  ('102', '1', '6/8/20', '1524', 'null'),
  ('103', '2', '6/9/20', '32', 'null'),
  ('104', '1', '6/9/20', '1524', 'null'),
  ('105', '2', '6/10/20', '32', 'null'),
  ('106', '1', '6/10/20', '1560', 'deposit'),
  ('107', '2', '6/11/20', '32', 'null'),
  ('108', '1', '6/11/20', '1560', 'null'),
  ('109', '2', '6/12/20', '32', 'null'),
  ('110', '1', '6/12/20', '1560', 'null');

(SELECT * 
    FROM account a 
WHERE id <= (SELECT id FROM account WHERE user_id = 1 AND action = 'deposit' ORDER BY id LIMIT 1) 
AND user_id = 1
ORDER BY id DESC LIMIT 11)
UNION ALL
(SELECT * 
    FROM account a 
WHERE id > (SELECT id FROM account WHERE user_id = 1 AND action = 'deposit' ORDER BY id LIMIT 1) 
AND user_id = 1 ORDER BY id ASC LIMIT 10)
ORDER BY id;

编号 | 用户 ID | 日期 | 账户余额 | 行动
--: | ------: | :----------------- | --------------: | :------
 98 | 1 | 0006-06-20 00:00:00 | 第1524章 无效的   
100 | 1 | 0006-07-20 00:00:00 | 第1524章 无效的   
102 | 1 | 0006-08-20 00:00:00 | 第1524章 无效的   
104 | 1 | 0006-09-20 00:00:00 | 第1524章 无效的   
106 | 1 | 0006-10-20 00:00:00 | 1560 | 订金
108 | 1 | 0006-11-20 00:00:00 | 1560 | 无效的   
110 | 1 | 0006-12-20 00:00:00 | 1560 | 无效的

db<>在这里摆弄

使用一个 id 可以正常工作，但是使用多个 id 您可以使用存储过程

DELIMITER //
CREATE DEFINER=`root`@`localhost` PROCEDURE `get_account_data`(IN _user_id INt, IN _limit INT, IN _action TEXT)
BEGIN
    DECLARE finished INTEGER DEFAULT 0;
    DECLARE _sqltext LONGTEXT;
    DECLARE _selid INTEGER DEFAULT 0;
    DECLARE _counter INTEGER DEFAULT 0;
        -- declare cursor for row id
    DEClARE curid 
        CURSOR FOR 
            SELECT id FROM account WHERE user_id = _user_id AND action = _action;

    -- declare NOT FOUND handler
    DECLARE CONTINUE HANDLER 
        FOR NOT FOUND SET finished = 1;
        
        SET _sqltext = '';
    OPEN curid;

    getid: LOOP
        FETCH curid INTO _selid;
        IF finished = 1 THEN 
            LEAVE getid;
        END IF;
        -- build id list
        IF _counter > 0 THEN
            SET _sqltext = CONCAT(_sqltext, " UNION ALL ");
        ELSE
            SET _counter = 1;
         END IF;
            
        SET _sqltext = CONCAT(_sqltext,"(SELECT * FROM account a WHERE id <= ",_selid," AND user_id = ",_user_id," ORDER BY id DESC LIMIT ", _limit + 1,")");
        SET _sqltext = CONCAT(_sqltext, " UNION ALL ");
        SET _sqltext = CONCAT(_sqltext,"(SELECT * FROM account a WHERE id > ",_selid," AND user_id = ",_user_id," ORDER BY id ASC LIMIT ", _limit ,")");
    END LOOP getid;
    CLOSE curid;        
    SET _sqltext = CONCAT(_sqltext, " ORDER BY id;");
    SET @sql = _sqltext;
    PREPARE stmt1 FROM @sql;
    EXECUTE stmt1;
    DEALLOCaTE PREPARE stmt1;
END//
DELIMITER ;

当您现在调用它时，您将获得对该用户执行操作的所有行以及 5 行

CALL get_account_data(1,5,'deposit');

但请注意，应根据允许文本的白名单检查操作文本，以便防止sql 注入

推荐阅读