如何编写SQL QUERY? [英] How to write the SQL QUERY?

问题描述

CREATE FUNCTION dbo.usrlst(@recid int)

DECLARE @Userlist varchar(1000)

RETURNS @userlst TABLE (@eventid int, @userlst nvarchar(1000)

AS

BEGIN

INSERT INTO @userlst

SELECT @recid, (SELECT @Userlist = COALESCE(@Userlist + '; ', '') + tbl_Users.Lname + N', ' + tbl_Users.Fname

FROM tbl_Event_Assignments INNER JOIN

tbl_Users ON tbl_Event_Assignments.Assigned_TO = tbl_Users.id

WHERE (tbl_Event_Assignments.Event_ID = @recID)) as userlst)

RETURN

END

上面是我为表定义的函数设置的脚本.我要完成的工作是我需要一个可以与表关联的字段.表A是我的事件列表.它包含有关事件的详细信息.表B是动作列表，以及这些动作所分配给的人员.他们希望一个显示器可以查看事件"列表中的数据以及该事件分配给的人员的列表.他们希望列表在一个字段中，以便数据易于阅读.

我愿意接受任何建议.

Above is a script I have setup for a table defined function. what I am trying to accomplish is I need a field that I can relate to a table. Table A is my event list. It holds specifics about the events. table B is a list of actions and the people those actions were assigned to. They want one display to see data from the Event list and a list of the people the event was assigned to. They want the list to be in one field so the data is easly readable.

I am open to any suggestions.

推荐答案

我相信，您要完成的任务的逻辑已包含在此 ^ ].除此之外:
1.您应该在BEGIN和END语句内使用DECLARE变量.
2.在RETURNS表定义中添加右括号. 3.不要在RETURNS表定义的列名中使用@.
4.另外，请考虑使用与此处名称不同的变量，表，函数，列名，以避免混淆并使函数更具可读性.

I believe the logic of what you are trying to accomplish is covered on this page[^]. Aside from that:
1. You should DECLARE variables inside the BEGIN and END statements.
2. Add closing parenthesis to the RETURNS table definition.
3. Don''t use @ in the column names for the RETURNS table definition.
4. Also, consider using variable, table, function, column names that are not as similar as the ones here to avoid confusion and make the function more readable.

这篇关于如何编写SQL QUERY?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！