如果我获取所有用户数据我在列中获取单人记录（技能，平台，project_name，task_name） [英] If I Fetch All Users Data I Am getting single person records in columns (skills,platform,project_name,task_name)

问题描述

SELECT distinct   TM_User.FullName,TM_Profile.profile, TM_User.Report_To,
    
FROM TM_User

join TM_Profile on TM_Profile.ProfileID=TM_User.ProfileID

WHERE TM_User.Is_Active=1 and TM_User.TM_UserID=13

推荐答案

首先，阅读我的< a href =http://www.codeproject.com/Articles/705911/SQL-Wizardry-Part-Five-when-not-to-use-DISTINCT>文章 [ ^ ]何时开启不要使用'distinct'。然后，尝试向我们提供表格结构和一些示例数据，以便我们尽力帮助您。



您的代码（冗余）要求所有子查询中的用户ID为13。所以当然你只能获得一个用户的数据（我猜这是另一个答案的意思）。您应该发布您的代码，使其不包含明显会破坏它的代码。并且，不，我们不能在不知道表结构并且要测试一些样本数据的情况下重写它。理想情况下，你会给我们一些数据并告诉我们你希望的结果，所以我们可以理解这一切。



我认为你的另一个问题是你没有别名你的表，所以当你在子查询中说tm_user时，不清楚你是指本地的，还是来自主查询的外部的。您当然希望通过主查询中的用户ID过滤每个子查询，或者每个结果将包含所有用户的数据。

First, read my article[^] on when not to use 'distinct'. Then, try giving us the table structure and some sample data so we can try to help you.

Your code (redundantly) requires the user id to be 13 in ALL your subqueries. So of course you only get data for one user ( I guess that's what the other answer means ). You should post your code so that it doesn't contain code that will obviously break it. And, no, we can't rewrite this without knowing the table structure and having some sample data to test against. Ideally, you'll give us some data and tell us the result you are hoping for, so we can make sense of it all.

I think your other issue is you don't alias your tables, so when you say tm_user in a subquery, it's not clear if you mean the local one, or the external one from the main query. you certainly want to filter each subquery by the user id in the main query, or each result will contain the data of ALL your users.

SELECT distinct   TM_User.FullName,TM_Profile.profile, TM_User.Report_To,
    stuff(( SELECT distinct ', ' + Reference.lookupvalue FROM Reference
            join Reference_Skills on Reference.LookupID=Reference_Skills.SkillLookupKey
            join TM_UserAttributeValue on  Reference_Skills.SkillLookupValue=TM_UserAttributeValue.AttributeValue
            join TM_User on TM_UserAttributeValue.TM_UserID=TM_User.TM_UserID
            where Reference.lookupkey='Platform' and TM_User.TM_UserID  = u.TM_UserId
            FOR XML PATH('')
        ) ,1,2,'') as platfrom,
    stuff(( SELECT ', ' + Reference_Skills.SkillLookupValue FROM Reference_Skills
            join TM_UserAttributeValue on  Reference_Skills.SkillLookupValue=TM_UserAttributeValue.AttributeValue
            join TM_User on TM_UserAttributeValue.TM_UserID=TM_User.TM_UserID
            where AttributeKeyType ='Skill' and TM_User.TM_UserID = u.TM_UserId
            FOR XML PATH('')
        ) ,1,2,'') as skills,
    STUFF( ( SELECT distinct  ', ' + project_name FROM  project
            JOIN task ON project.project_id = task.project_id
            JOIN timesheet ON timesheet.task_id = task.task_id
            join TM_user on TM_user.TM_UserID=timesheet.user_id
            WHERE project.status = 1 and TM_user.TM_UserID = u.TM_UserId
            FOR XML PATH ('')
       ), 1, 2, '') as project,
    STUFF( ( SELECT distinct ', ' + task_name  FROM  task
            JOIN project ON project.project_id = task.project_id
            JOIN timesheet ON timesheet.task_id = task.task_id
             join TM_user on TM_user.TM_UserID=timesheet.user_id
            WHERE project.status = 1 and TM_user.TM_UserID = u.TM_UserId
            FOR XML PATH ('')
        ), 1, 2, '') as task_name

FROM TM_User u

join TM_Profile on TM_Profile.ProfileID=TM_User.ProfileID

WHERE TM_User.Is_Active=1

是我尝试过的，但真的不可能帮助，无需使用表和数据。

is what I'd try, but it's impossible to really help without the tables and data to work with.

删除此 TM_user.TM_UserID = 13

这篇关于如果我获取所有用户数据我在列中获取单人记录（技能，平台，project_name，task_name）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！