SQL Server How to search in columns in SQL

bxfogqkk 于 2023-03-11 发布在其他

关注(0)|答案(2)|浏览(124)

I have two table,the names table and the earnings table.
| Name_ID | Name_desc |
| ------------ | ------------ |
| 1 | mark |
| 2 | smith |
| 3 | becky |
| 4 | jimmy |

and the earnings table

ID_1	Earnings_1	ID_2	Earnings_2	ID_3	Earnings_3	ID_4	Earnings_4
1	500	2	5454	3	1247	4	7844

You can try it out here:

https://dbfiddle.uk/d3a3rfG0

Question: How to join Name_ID and ID_ to get the earnings. I need a funtion to check every ID column in the earnings table and if it match then get the earnings.

The goal is a table that looks like this:
| Name_ID | Name_desc | earnings |
| ------------ | ------------ | ------------ |
| 1 | mark | 500 |
| 2 | smith | 5454 |
| 3 | becky | 1247 |
| 4 | jimmy | 7844 |

sql-server

来源：https://stackoverflow.com/questions/75702983/how-to-search-in-columns-in-sql

2条答案

按热度按时间

qpgpyjmq1#

select n.Name_ID, n.Name_desc, 
       sum(coalesce(e1.Earnings_1, 0) +
           coalesce(e2.Earnings_2, 0) +
           coalesce(e3.Earnings_3, 0) +
           coalesce(e4.Earnings_4, 0)
       ) as earnings
from names n
left join earnings e1 on e1.id_1 = n.Name_ID
left join earnings e2 on e2.id_2 = n.Name_ID
left join earnings e3 on e3.id_3 = n.Name_ID
left join earnings e4 on e4.id_4 = n.Name_ID
group by n.Name_ID, n.Name_desc

DBFiddle

赞(0）回复(0）举报 2023-03-11

kwvwclae2#

SELECT *
FROM Earnings
select n.*,e.Earnings_1 Earning from Names n inner join Earnings e on n.Name_ID=e.ID_1

union
select n.*,e.Earnings_2 Earning from Names n inner join Earnings e on n.Name_ID=e.ID_2

union
select n.*,e.Earnings_3 Earning from Names n inner join Earnings e on n.Name_ID=e.ID_3

union
select n.*,e.Earnings_4 Earning from Names n inner join Earnings e on n.Name_ID=e.ID_4

赞(0）回复(0）举报 2023-03-11

我来回答

SQL Server How to search in columns in SQL

2条答案

DBFiddle

相关问题

热门标签

最新问答