英文:
Marking users as not active if they don't have required records
问题
我有一个简单的表格模型。每个用户都必须完成一些必要的“操作”。
用户
-ID
-姓名
-是否激活
操作
-ID
-名称
用户操作
-用户ID
-操作ID
我想找出所有没有在用户操作表中具有所有操作记录的用户。如果他们在用户操作中没有所有操作记录，我想将是否激活标记为false。
可能会有2万个用户，所以这个处理应该是高效的。
寻找在不使用游标的情况下完成此操作的最佳方法。
英文:
I have a simple table model. There are some required "Actions" that every user has to have completed.
User
-Id
-Name
-IsActive
Actions
-Id
-Name
UserActions
-UserID
-ActionID
I want to find all Users who don't have all the Action records in the UserAcitons table. If they don't have all actions records in UserActions, I want to mark IsActive as false.
There could be 20K users so this should be effecient to process.
Looking for the best way to do this without having a cursor.
答案1
得分: 2
假设 UserActions 表中的行是唯一的，您可以计算这两个表中的操作并进行比较：
update u
set isactive = (case when num_actions > total_actions then 1 else 0 end)
from users u left join
(select ua.userid, count(*) as num_actions
from useractions ua
group by ua.userid
) ua
on ua.userid = u.id cross join
(select count(*) as total_actions
from actions a
) a;
SQL Server 不支持布尔值，因此这里使用 0 表示假，1 表示真。
英文:
Assuming rows in UserActions are unique, you can count the actions in the two tables and compare them:
update u
set isactive = (case when num_actions > total_actions then 1 else 0 end)
from users u left join
(select ua.userid, count(*) as num_actions
from useractions ua
group by ua.userid
) ua
on ua.userid = u.id cross join
(select count(*) as total_actions
from actions a
) a;
SQL Server doesn't support booleans, so this uses 0 for false and 1 for true.
答案2
得分: 1
你忘记告诉我们一些细节，例如是否存在重复的操作，是否ids是唯一的等等。
对于非常简单的情况，我成功创建了以下示例：
create table [User]
(
Id int not null primary key,
Name varchar(50) not null,
IsActive bit not null
)
create table [Actions]
(
Id int not null primary key,
Name varchar(50) not null
)
GO
create table [UserActions]
(
UserId int not null,
ActionId int not null,
foreign key (UserId) REFERENCES [User](Id),
foreign key (ActionId) REFERENCES [Actions](Id)
)
GO
insert into [User] values
(1, 'Alice', 1),(2, 'Bob', 1),(3, 'Caroline', 1)
insert into [Actions] values
(1, 'eat'),(2,'drink'),(3,'sleep')
insert into [UserActions] values
(1,1),(1,2),(1,3),
(2,1),(2,2),
(3,1),(3,2),(3,1)
GO
update us
set us.IsActive = 0
from [User] us
join
(
select ua.UserId, COUNT(distinct ua.ActionId) as ActionCount
from [UserActions] ua
group by ua.UserId
) as uac on uac.UserId = us.Id
where uac.ActionCount < (select count(*) from [Actions])
select * from [User] us
提供以下结果：
Id Name IsActive
----------- ---------- --------
1 Alice 1
2 Bob 0
3 Caroline 0
英文:
You forgot to tell us some details, for example if there are duplicated actions, if ids are UNIQUE, etc.
for very simple scenario I managed to create the below example:
create table [User]
(
Id int not null primary key,
Name varchar(50) not null,
IsActive bit not null
)
create table [Actions]
(
Id int not null primary key,
Name varchar(50) not null
)
GO
create table [UserActions]
(
UserId int not null,
ActionId int not null,
foreign key (UserId) REFERENCES [User](Id),
foreign key (ActionId) REFERENCES [Actions](Id)
)
GO
insert into [User] values
(1, 'Alice', 1),(2, 'Bob', 1),(3, 'Caroline', 1)
insert into [Actions] values
(1, 'eat'),(2,'drink'),(3,'sleep')
insert into [UserActions] values
(1,1),(1,2),(1,3),
(2,1),(2,2),
(3,1),(3,2),(3,1)
GO
update us
set us.IsActive = 0
from [User] us
join
(
select ua.UserId, COUNT(distinct ua.ActionId) as ActionCount
from [UserActions] ua
group by ua.UserId
) as uac on uac.UserId = us.Id
where uac.ActionCount < (select count(*) from [Actions])
select * from [User] us
Provides de results below
> Id Name IsActive
> ----------- ---------- --------
> 1 Alice 1
> 2 Bob 0
> 3 Caroline 0
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论