英文:
SQL statement select column from same table in one query with different values
问题
goodsinfo表格中的seller ID对应于mainrefer表格中的ID，您可以使用以下查询获取所需的结果：
SELECT m.Codename AS "Seller name", g.Codename AS "goods name"
FROM goodsinfo AS gi
JOIN mainrefer AS m ON gi.seller_id = m.ID
JOIN mainrefer AS g ON gi.goods_id = g.ID;
这将返回您所希望的结果：
| Seller name | goods name |
|---|---|
| Richard | Mobile phones |
| Sam | IT cables |
请注意，此查询使用了两次mainrefer表格的自连接，一次用于卖家名称，一次用于商品名称，然后将它们合并在一起以获得所需的结果。
英文:
I have 2 tables; the first table has good and seller ID which this ID found in the second table.
goodsinfo:
| seller ID | goods ID |
|---|---|
| 1 | 5 |
| 3 | 7 |
Second Table mainrefer:
| ID | Codename |
|---|---|
| 1 | Richard |
| 3 | Sam |
| 5 | Mobile phones |
| 7 | IT cables |
I want to get a result like this:
| Seller name | goods name |
|---|---|
| Richard | Mobile phones |
| Sam | IT Cables |
I tried many queries with my self but cannot reach how to get diff value from 1 column at 1 query
答案1
得分: 2
你可以使用不同的别名两次连接相同的表格。
select seller.Codename as sellerName,
goods.Codename as goodsName
from goodsinfo g
join mainrefer seller on g.sellerID = seller.ID
join mainrefer goods on g.goodsID = goods.ID
英文:
You can join the same table twice with different alias names
select seller.Codename as sellerName,
goods.Codename as goodsName
from goodsinfo g
join mainrefer seller on g.sellerID = seller.ID
join mainrefer goods on g.goodsID = goods.ID
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论