Steven Kirke's questions

有一行文字“1.制定行动计划，什么也不做。2.再次沉迷于‘狩猎’。3.完成一些任务。”

任务是让它成形

1. 制定行动计划，然后什么也不做。
2. 重饮“狩猎”。
3. 执行哪个线程任务。

const text = "1. Разработать план мероприятий и ни чего не делать. 2. Повторно напиться 'Охоты'. 3. Выполнить какую нить задачу."

function disassembleText(text) {
    const regexp = `/\d/g`;

    var dessembler = text.split(' ');

    for (var key of dessembler) {
        var test = key.replace(regexp, '\n');
        console.log(test);
    }
}

console.log(disassembleText(text))

我尝试使用正则表达式，但替换方法不起作用。或者我错误地转义了正则表达式。

我不明白为什么数据会被覆盖。在第一个示例中，我使用了三个记录，例如我找到了三个员工。

在游标中，我在另外 2 个表中进行查询，检查课程是否位于包含已完成课程的表中和包含活动课程的表中，并将其写入变量。如果员工既没有完成也没有参加课程，PRINT 中的记录会出于某种原因消失吗？

create table personTest ( 
    id bigint primary key ,
    name varchar(MAX) not null,
);

insert into personTest values (1, 'Anna');
insert into personTest values (2, 'Andrey');
insert into personTest values (3, 'Kate');

create table activeCource ( 
    id bigint primary key ,
    name varchar(MAX) not null,
    person_id bigint
);

insert into activeCource values (1, 'Cource ololo', 1);
insert into activeCource values (2, 'Cource trololo', 2);


create table passedCource ( 
    id bigint primary key ,
    name varchar(MAX) not null,
    person_id bigint
);

insert into passedCource values (1, 'Cource ololo', 1);
insert into passedCource values (2, 'Cource trololo', 3);

DECLARE  @current_person BIGINT
        ,@person_fullname VARCHAR(MAX)
        ,@course_result VARCHAR(20) = 'Данных нет'

DECLARE @cursor CURSOR

SET @cursor = CURSOR SCROLL FOR
    SELECT 
         cls.id
        ,cls.name
    FROM personTest AS cls

OPEN @cursor

FETCH NEXT FROM @cursor INTO @current_person, @person_fullname
WHILE @@FETCH_STATUS = 0 BEGIN

    SELECT @course_result = (
        SELECT 
            CASE 
                WHEN ISNULL(pls.id, 0) != 0 THEN 'Курс назначен'
                ELSE 'Данных нет'
            END AS is_exists
        FROM activeCource AS pls
        WHERE pls.person_id = @current_person
    ) --например у одного сотрудника (Kate) тут ни чего нет.

    SELECT @course_result = (
        SELECT 
            CASE 
                WHEN ISNULL(pls.id, 0) != 0 THEN 'Курс пройден'
                ELSE 'Данных нет'
            END AS is_exists
        FROM passedCource AS pls
        WHERE pls.person_id = @current_person
    ) 

    PRINT @person_fullname + ' ' + @course_result
    -- Почему то принт выводит только двух сотрудников, а не трех. Понять не могу из за чего. Любой цикл в том же Swift выводит все.
    FETCH NEXT FROM @cursor INTO @current_person, @person_fullname
END

CLOSE @cursor
DEALLOCATE @cursor

我无法理解任务的逻辑。当顾客在特定日期购买食物时，会有一张桌子（在示例中有几个）。并且有一个在同一时间段内访问过或未访问过的商店的列表。

第一个条件是显示在一段时间内访问过和未访问该商店的所有人员。第二个条件是显示在一段时间内访问过或未访问过的商店。

我无法连接这些数据。问题是如何连接第 1 列和第 3 列。也就是说，第 3 列应该显示未访问过或访问过的商店，同时链接人员，反之亦然，如果该人没有访问该商店。根据条款，禁止使用运营商。

NOT IN, IN, NOT EXISTS, EXISTS, UNION, EXCEPT, INTERSECT

我是这样实现的，但是不知道如何选择。

有三个表，Buers 与表关联，Visit、Stores 与 Hanging 关联，Visit 是有客户 ID 和商店 ID 的地方。

买家

create table person
( id bigint primary key ,
  name varchar(MAX) not null,
  age integer not null default 10,
  gender varchar(6) default 'female' not null ,
  address varchar(MAX)
  );

alter table person add constraint ch_gender check ( gender in ('female','male') );

insert into person values (1, 'Anna', 16, 'female', 'Moscow');
insert into person values (2, 'Andrey', 21, 'male', 'Moscow');
insert into person values (3, 'Kate', 33, 'female', 'Kazan');
insert into person values (4, 'Denis', 13, 'male', 'Kazan');
insert into person values (5, 'Elvira', 45, 'female', 'Kazan');

商店

create table pizzeria
(id bigint primary key ,
 name varchar(50) not null ,
 rating numeric not null default 0);

alter table pizzeria add constraint ch_rating check ( rating between 0 and 5);

insert into pizzeria values (1,'Pizza Hut', 4.6);
insert into pizzeria values (2,'Dominos', 4.3);
insert into pizzeria values (3,'DoDo Pizza', 3.2);
insert into pizzeria values (4,'Papa Johns', 4.9);
insert into pizzeria values (5,'Best Pizza', 2.3);
insert into pizzeria values (6,'DinoPizza', 4.2);

买家来访

create table person_visits
(id bigint primary key ,
 person_id bigint not null ,
 pizzeria_id bigint not null ,
 visit_date date not null default GETDATE(),
 constraint uk_person_visits unique (person_id, pizzeria_id, visit_date),
 constraint fk_person_visits_person_id foreign key  (person_id) references person(id),
 constraint fk_person_visits_pizzeria_id foreign key  (pizzeria_id) references pizzeria(id)
 );

insert into person_visits values (1, 1, 1, '2022-01-01');
insert into person_visits values (2, 2, 2, '2022-01-01');
insert into person_visits values (3, 2, 1, '2022-01-02');
insert into person_visits values (4, 3, 5, '2022-01-03');
insert into person_visits values (5, 3, 6, '2022-01-04');
insert into person_visits values (6, 4, 5, '2022-01-07');
insert into person_visits values (7, 4, 6, '2022-01-08');
insert into person_visits values (8, 5, 2, '2022-01-08');
insert into person_visits values (9, 5, 6, '2022-01-09');
insert into person_visits values (10, 6, 2, '2022-01-09');
insert into person_visits values (11, 6, 4, '2022-01-01');
insert into person_visits values (12, 7, 1, '2022-01-03');
insert into person_visits values (13, 7, 2, '2022-01-05');

第一个请求，我选择在特定日期访问商店的员工

DECLARE @DataMin DATE = '2022-01-01';
DECLARE @DataMax DATE = '2022-01-03';

CREATE TABLE ##ResultVisitPersons
(namePerson NVARCHAR(100), resultVisit BIT, visitDuringPeriod NVARCHAR(100), pizzeriaName NVARCHAR(100), pizzeriaID INT)

INSERT INTO ##ResultVisitPersons
SELECT
    visit.name,
    visit.resultVisit,
    visit.visitDuringPeriod,
    visit.pizzeriaName,
    visit.pizzeria_id
FROM (
  SELECT 
    person.name,
    CASE 
        WHEN person_visits.visit_date BETWEEN @DataMin AND @DataMax THEN 1
        ELSE 0
    END AS resultVisit,
    CASE 
        WHEN person_visits.visit_date BETWEEN @DataMin AND @DataMax THEN LEFT(CONVERT(VARCHAR, person_visits.visit_date, 104), 10)
        ELSE 'NULL'
    END AS visitDuringPeriod,
    person_visits.pizzeria_id,
    pizzeria.name as pizzeriaName
FROM person_visits
JOIN person ON person.id = person_visits.person_id
JOIN pizzeria ON pizzeria.id = person_visits.pizzeria_id
) visit

第二个请求是同一时间段内商店访问的样本。

CREATE TABLE ##ResultVisitPizzeria
(visitDuringPeriod BIT, result NVARCHAR(100), namePizzeria NVARCHAR(100), id INT)

INSERT INTO ##ResultVisitPizzeria
SELECT 
    CASE 
        WHEN person_visits.visit_date BETWEEN @DataMin AND @DataMax THEN 1
        ELSE 0
    END AS visitDuringPeriod,
        CASE 
        WHEN person_visits.visit_date BETWEEN @DataMin AND @DataMax THEN N'Ресторан посещали'
        ELSE N'Ресторан не посещали'
    END AS isVisitDuringPeriod,
    pizzeria.name,
    pizzeria.id
FROM person_visits
JOIN pizzeria ON pizzeria.id = person_visits.pizzeria_id