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 aJOIN
:
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屋!