Ms sql update table column基于某些条件 [英] Ms sql updating table column base on certain condition

问题描述

我有3张桌子



表用户= [id，性别，国籍，名字，姓氏];

表性别= [id，name]一对多用表用户

表国籍[id，name] - 一对多用表用户。



想要执行查询，例如

选择id，性别，形式用户，其中id = 5;



在性别列中应显示性别姓名而非性别ID;



性别= 1，姓名

国籍= 1，姓名。



我尝试了什么：



i确实尝试过像



SELECT upi.id，upi.fullname，upi.gender，

（SELECT name from Gender WHERE g.id = upi.gender）as gndr

来自用户为upi，性别为g





它给出的结果如



id，fullname，gender_id，gndr

1，abc，1，Male

1，abc，1，Male

1， ABC ，1，男性

1，abc，1，男

1，abc，1，男





感谢期待....

I've 3 tables

Table User = [id, gender, nationality, firstname, lastname ];
Table Gender = [id, name] one to many with table User
Table nationality [ id, name] - One to many with table User.

want to execute query like
"Select id, gender, form User where id = 5";

in gender column it should display gender name instead of gender's id;

gender = 1, name
nationality = 1 , name.

What I have tried:

i do have tried query like

SELECT upi.id , upi.fullname, upi.gender,
(SELECT name From Gender WHERE g.id = upi.gender ) As gndr
FROM User as upi, gender as g


it gives result like

id,fullname,gender_id, gndr
1, abc ,1 ,Male
1, abc ,1 ,Male
1, abc ,1 ,Male
1, abc ,1 ,Male
1, abc ,1 ,Male


thanking in an anticipation....

推荐答案

听起来你需要 JOIN ：

SQL Joins | W3Schools [ ^ ]

Sounds like you need a JOIN:
SQL Joins | W3Schools[^]

SELECT
   U.id,
   U.firstname,
   U.lastname,
   G.name As gender,
   N.name As Nationality
FROM
    User As U
    INNER JOIN Gender As G
    ON G.id = U.gender
    INNER JOIN Nationality As N
    ON N.id = U.nationality
;

这篇关于Ms sql update table column基于某些条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！