需要帮助的MySQL问题 [英] Need help in mysql problem

问题描述

大家好

如何显示会员的姓名表格
我的问题:
我有一个表名userDetails
userDetails的列是
1.pk_id
2.名字
3.姓氏
等

我想查找用户的名字，而不是pk_id
我已经尝试过

Hi all

How to show the member''s name form table
My Problem:
I have a table name userDetails
Columns of userDetails are
1.pk_id
2.First_name
3.Last_name
etc.

i want to find the user''s first name for more than on pk_id
I have tried

SELECT FIRST_NAME FROM USERDETAILS WHERE PK_ID = (SELECT GROUP_CONCAT(USER_ID) FROM PROJECTS WHERE PROJECT_ID = 2 GROUP BY PROJECT_ID);

这仅显示第一个pk_id的名字，但是我想显示子查询将返回的所有userid的名字.
意思是:
如果子查询将返回2,3,4,5而不是我想显示pk_id = 2，pk_id = 3，pk_id = 4，pk_id = 5
的名字 如何编写查询，以使其返回子查询选择的所有用户名的第一名

请帮助

感谢

This is showing only first name of first pk_id, but i want to show all the first names of the userids that subquery will return.
means:
If the subquery will return 2,3,4,5 than i want to show the first_name of pk_id=2,pk_id=3,pk_id=4,pk_id=5
How to write a query so that it will return all first names of the userids selected by the subquery

Please Help

Thanks

推荐答案

您列出的语法正确.我进行了测试(在Microsoft SQL中，但在这种情况下应该没有区别)，结果是返回了两行.您只查看返回的第一行吗?否则，由于查询正确，您的数据肯定有问题.

继续前进，检查以确保您正在查看的每一行(而不仅仅是第一行).接下来，检查以确保表中同时具有PK_ID = 2和PK_ID = 3(也许缺少3行?).最后，查看数据库本身.可能有一个损坏的索引正在返回错误数据(不太可能但有可能).

更新:
根据更新后的信息，您需要更改查询以使用IN语句而不是equals语句，并且需要像以下这样删除GROUP_CONCAT语句:

The syntax you have listed is correct. I ran a test (in Microsoft SQL but there should not be a difference in this case) and the results were that two rows were returned. Are you only looking at the first row returned? Otherwise, there must be something wrong with your data because the query is right.

Moving forward, check to be sure you are looking at every row (not just the first). Next, check to be sure you have both an PK_ID=2 and PK_ID=3 in your table (maybe the 3 row is missing?). Finally, look at your database itself. There might be a corrupted index that is returning bad data (unlikely but possible).

Update:
Based upon your updated information, you need to change your query to use the IN statement instead of the equals statement and you need to take off the GROUP_CONCAT statement like so:

SELECT FIRST_NAME FROM USERDETAILS WHERE PK_ID IN (SELECT USER_ID FROM PROJECTS WHERE PROJECT_ID=2);

您需要注意的一件事是子查询返回一列数据(USER_ID列). SQL会将其转换为类似于您的第一个查询的内容(本质上).如果返回多行，它将把每行的值与PK_ID进行比较以查看是否匹配.如果是这样，由于匹配，它将包括主查询中的行.

The one thing you need to watch out for is that the sub-query returns one column of data (the USER_ID column). That will be converted by SQL into something similar to your first query (in essence). If you return multiple rows, it will compare the value for each row against the PK_ID to see if it matches. If it does, it will include the row from the main query because of the match.

这篇关于需要帮助的MySQL问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！