在SQL中串联主键 [英] Concate Primary Keys in SQL

问题描述

我想直接在 SQL 中连接多个表的主键.我在下面的查询中使用了三个主键，并将它们之间的连字符连接起来，但是 SQL 跳过了连字符并求和了主键并得出一个单一值.

  SELECT CID +'-'+ RID +'-'+ CGID作为[IdCombination] ... 

其中CID，RID和CGID是三个 SQL 表的主键.

它如何跳过查询中的 string 部分?

任何帮助将不胜感激.

已更新

例如:CID，RID和CGID的值分别为3，4，3.应该是3-4-3，但结果是10.

解决方案

发生了什么事?请记住， + 表示加法和字符串串联.碰巧-可以解释为数字(例如 -0 )，因此SQL Server倾向于将 + 解释为加法./p>

通常，当您执行这种类型的操作时，分隔符不能解释为数字，并且只会出现错误.我很高兴您在这种情况下没有收到错误.

一种方法是将值显式转换为字符串:

 将CAST(CID作为VARCHAR(255))+'-'+ CAST(RID +作为VARCHAR(255))'-'+ CAST(CGID作为VARCHAR(255))作为[IdCombination] 

在SQL Server 2012+中，您可以使用 CONCAT()更简单地执行此操作:

  SELECT CONCAT(CID，'-'，RID，'-'，'CGID)作为[IdCombination] 

CONCAT()知道所有内容都应该是字符串.

I want to concate Primary Keys of multiple tables in SQL directly. I used below query to concate three primary keys with a hyphen between them but the SQL skipped the hyphen and sum up the primary keys and result in a single value.

  SELECT CID + '-' + RID + '-'+  CGID As [IdCombination] ...    

where CID , RID and CGID are the Primary Keys of three SQL Tables.

How it skipped the string part in query ?

Any help would be highly appreciated.

Updated

For Example : The Values of CID , RID and CGID are 3 , 4, 3 respectively. It should be 3-4-3 but the result is 10.

解决方案

What is happening? Remember that + means both addition and string concatenation. It so happens that - can be interpreted as a number (like -0), so SQL Server prefers to interpret the + as addition.

Normally, when you do this type of operation, the separation character cannot be interpreted as a number, and you just get an error. I am amused that you don't get an error in this case.

One method is to explicitly cast the values as strings:

SELECT CAST(CID as VARCHAR(255)) + '-' + CAST(RID +  as VARCHAR(255)) '-'+  CAST(CGID  as VARCHAR(255)) As [IdCombination] 

In SQL Server 2012+, you can do this more simply using CONCAT():

SELECT CONCAT(CID, '-', RID, '-', 'CGID) As [IdCombination] 

CONCAT() knows that everything should be a string.

这篇关于在SQL中串联主键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！