SQL Server Count values that matches values with other columns

5q4ezhmt 于 2023-10-15 发布在其他

关注(0)|答案(1)|浏览(139)

SQL - Count number of columns that are having matching values then add count to the additional column.

Assuming I have a temp table with this value
| id | prod_name | attr1 | attr2 | attr3 | attr4 |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | test name | brand | bottle | size | 12x |

then I will need to compare it to product table having values of column by column:

id	prod_name	attr1	attr2	attr3	attr4
3	test name	brand	bottle	size	12x
4	some name	brand	satchet	size	1x
5	sample	brand	bottle	size	23x

expected result should be:

id	prod_name	attr1	attr2	attr3	attr4	CNT_Match_cols
3	test name	brand	bottle	size	12x	4
4	some name	brand	satchet	size	1x	2
5	sample	brand	bottle	size	23x	3

*CNT_Match_cols column is get of how many attr(X) columns that are having same values.

sql-server

来源：https://stackoverflow.com/questions/77171324/count-values-that-matches-values-with-other-columns

1条答案

按热度按时间

holgip5t1#

You can cross join your tables and then count the attribute matches:

select prod.id, prod.prod_name, prod.attr1, prod.attr2, prod.attr3, prod.attr4
,   case when temp.attr1 = prod.attr1 then 1 else 0 end
    + case when temp.attr2 = prod.attr2 then 1 else 0 end
    + case when temp.attr3 = prod.attr3 then 1 else 0 end
    + case when temp.attr4 = prod.attr4 then 1 else 0 end as matched
from (
    VALUES  (1, N'test name', N'brand', N'bottle', N'size', N'12x')
) temp (id,prod_name,attr1,attr2,attr3,attr4)
cross join (
    VALUES  (3, N'test name', N'brand', N'bottle', N'size', N'12x')
    ,   (4, N'some name', N'brand', N'satchet', N'size', N'1x')
    ,   (5, N'sample', N'brand', N'bottle', N'size', N'23x')
) prod (id,prod_name,attr1,attr2,attr3,attr4)

赞(0）回复(0）举报 2023-10-15

我来回答

SQL Server Count values that matches values with other columns

1条答案

相关问题

热门标签

最新问答