如何使用另一个表中的多个列替换表中的一列
16lz
2021-01-22
I have a table that has two different references to an address table. I would like to replace each reference id with the actual columns in a view. I envision something like what follows, but that does not work. I am using MySQL 5.5. What is the correct grammar?
我有一个表有两个不同的地址表引用。我想用视图中的实际列替换每个引用ID。我想象下面的内容,但这不起作用。我正在使用MySQL 5.5。什么是正确的语法?
CREATE VIEW Company AS
select id, Name AccountName, JoinDate, AccountStatus, CompanyName,
( select street1 MailStreet1, street2 MailStreet2, city MailCity, state MailState, county MailCounty, country MailCountry, postalcode MailPostalCode from Addresses where id = MailAddress limit 1 ) ,
( select street1 BillingStreet1, street2 BillingStreet2, city BillingsCity, state BillingState, county BillingCounty, country BillingCountry, postalcode BillingPostalCode from Addresses where id = BillingAddress limit 1 )
from Customer;
1 个解决方案
#1
2
select
c.id, c.Name as AccountName, c.JoinDate, c.AccountStatus, c.CompanyName,
ma.street1 as MailStreet1, ma.street2 as MailStreet2, ma.city as MailCity, ma state as MailState,
ma.county as MailCounty, ma.country as MailCountry, ma.postalcode MailPostalCode,
ba.street1 as MailStreet1, ba.street2 as MailStreet2, ba.city as MailCity, ba state as MailState,
ba.county as MailCounty, ba.country as MailCountry, ba.postalcode MailPostalCode,
from
Customer c
inner join Address ma on ma.id = c.MailAddress
inner join Address ba on ba.id = c.BillingAddress
You can use LEFT JOIN instead of INNER JOIN if these address references may be empty.
如果这些地址引用可能为空,则可以使用LEFT JOIN而不是INNER JOIN。
更多相关文章
- MySQL视图-(视图创建,修改,删除,查看,更新数据)
- 如何在C中删除多个闪存地址?
- ng- repeat显示的行等于no属性,甚至不显示html视图上的数据
- 桌面视图中的SlikNav多级菜单?
- 在单选按钮上选中/取消选中,加载/隐藏部分视图
- 如何在当前视图中始终保持水平底部滚动条
- 切换元素类并使用旧类在视图中添加新元素
- 在用户将'n'粘贴复制到文本字段后,如何更新视图模型?
- 在项目之间共享ASP.NET MVC部分视图