像Skype一样在查询表中查询 [英] Skype like query in a lookup table

问题描述

大家好，
目前，我正在努力尝试创建一个查询.
它基于以下表结构:

Hi all,
currently I am struggling with a query that I try to create.
It is based on the following table structure:

CREATE TABLE zzzTestQuery1(
	[id] [int] IDENTITY(1,1) NOT NULL,
	[mId] [int] NOT NULL,
	[uId] [int] NOT NULL)

mId是messageId，而uId是userId
该表用作查找表，以便将n个用户分配给一条消息.

现在，我尝试获取分配给特定用户组的所有消息.
意味着我需要获取分配给(示例)用户1、3、5的所有消息，而实际上仅分配给这三个用户.没有分配额外用户的邮件，也没有分配少于指定用户的邮件.
(我希望这个解释很清楚，很遗憾，我不是母语人士，所以请问是否不清楚).

我必须查找的userId是动态的.

我可以使用C#生成消息列表，但是我需要获取大量记录才能处理它们，这在某种程度上让人感到不舒服.

我该如何满足这一要求.我考虑过平整层次结构，以便消息获得一个新的(也许是经过计算的)列，其中包含用逗号分隔的分配给消息的userId(已排序)的列表...

任何帮助，感激不尽，
祝你有美好的一天
Andy

mId is a messageId while uId is the userId
This table functiones as a lookup table in order to assign n users to a message.

I now try to get all messages that are assigned to a specific user group.
Means that I need to get all messages that are assigned to (exmpl.) user 1, 3, 5 and only axactly these three users. No messages that have an additional user assigned and no messages that have less than the specified users assigned.
(I hope that this explanation is clear enough, unfortunately I am not a native speaker, so ask if something is unclear).

The userId''s I have to look up are dynamic.

I can produce the list of messages using C#, but I need to grab a lot of records in order to process them, which feels somehow uncomfortable.

How could I approach this requirement. I thought about flattening the hirarchy so that a message gets a new (maybe computed) column that contains a comma separated list of userId''s (sorted) that are assigned to a message...

Any help is kindly appreciated,
have a great day
Andy

推荐答案

我该做什么:
我有一个表，其中包含用于唯一ID的字段，用于GUID值的varchar字段(50)和用于用户ID的字段
我有一个复选框，可以在其中选择用户.单击提交按钮时，我会做:
-创建一个新的Guid()
-扫描所有选中的复选框.
每个复选框和GUID都存储在表中.
之后，我将调用Guid作为输入参数的存储过程. 在该存储过程中，我可以

What I Do:
I have a table with a field for an unique ID, a varchar field(50) for a GUID value and a field for the user id
I have a checkboxlist in which I can select users. When clicking a submit button I do:
- create a New Guid()
- scan for all checked checkboxes.
Each checkbox and the guid are stored in table.
After that I call an stored procedure that has the Guid as input parameter
In that stored procedure I can

select m.Message
from tblMessage as m
inner join zzzTestQuery1 as z on z.mId = m.mid
where z.uid in (select uid from myTable where guidField = @guid)

通过使用guid，您可以唯一地引用来自所选用户的消息.

玩得开心

By using the guid you have one unique reference to the messages form the selected users.

Have fun

我假设您的意思是让同一个对话的用户获得?由于您使用的是实体框架，因此您应该可以这样做:

I assume what you mean is get the users that are in the same conversation? Since you are using the Entity Framework you should be able to do it like this:

var usersInChat = (from m in myDataContext.Messages
                                 group m.Users in ChatUsers
                                 where m.mID = messageID
                                 select ChatUsers.Key); 

假设您已经正确设置了数据库，那么这应该可以工作.

P.S:我现在不在我的电脑上，所以我不能仔细检查，但是我想了解的一点是使用分组:P

http://msdn.microsoft.com/en-us/library/bb896250.aspx [ ^ ]

Assuming that you have set up you database correctly this should work.

P.S: Im not by my PC now so I cant double check, but the point I am trying to get across is use grouping :P

http://msdn.microsoft.com/en-us/library/bb896250.aspx[^]

这篇关于像Skype一样在查询表中查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！