查询不是正确的输出方式 [英] query is not filltering right way output

问题描述

你好，

Hello,

DECLARE @AssignedGroupIDs AS NVARCHAR(4000)

    SELECT
            @AssignedGroupIDs=  COALESCE(CONVERT(NVARCHAR, '1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480') + ', ', '') + Convert(NVARCHAR,tbGroup.GroupID )
        FROM
            tbGroup,tbAssignedGroups
        WHERE
         tbGroup.GroupID = tbAssignedGroups.GroupID
            AND tbAssignedGroups.UserID = 2
            AND tbGroup.IsDeleted = 0

        select @AssignedGroupIDs

我的输出：



1470,1471,1472,1474,1475，，1480





实际产出必须是：

1470,1471,1472,1474,1475,1477,1478,1480




为什么输出没有得到正确的ids.1477,1478这个ids跳过为什么

i不要kw请怎样才能得到正确的ids









Regrads

Sadhana.R.Belge

My output:

1470, 1471, 1472, 1474, 1475, , 1480


actual output must be:
1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480


why output is not getting right ids.1477,1478 this ids skip why
i dont kw please how can i get right ids




Regrads
Sadhana.R.Belge

推荐答案

我可以在这段代码中看到几个问题，但我只是处理t他提出了问题：



首先， NVARCHAR（4000）自SQL 2000以来一直不是标准（也许是2005年）。请改用 NVARCHAR（MAX）。它是可变长度的，因此对于未知长度的字符串更有效。



如果没有（4000）或（MAX），NVARCHAR的默认值为（30）。因此 CONVERT（NVARCHAR，'1470,1471,1472,1474,1475,1477,1478,1480'） = '1470,1471,1472， 1474,1475，'



设置NVARCHAR的大小来解决问题，但它已经是一个字符串所以你不需要甚至需要转换它。





另外：Coalesce用于忽略空值。你的代码排除了空值的可能性，所以这是毫无意义的



这就是你的代码应该按照它所写的方式做的事情：

I can see several issues in this code, but I'll just deal with the question at hand:

For a start, NVARCHAR(4000) has not been the standard since SQL 2000 (maybe 2005). Use NVARCHAR(MAX) instead. It is variable length so is more efficient for strings of unknown length.

Without (4000) or (MAX) the default for NVARCHAR is (30). Hence CONVERT(NVARCHAR, '1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480') = '1470, 1471, 1472, 1474, 1475, '

set the size of the NVARCHAR to fix the issue, but it is already a string so you don't even need to convert it.


Also: Coalesce is used to ignore null values. Your code excludes the possibility of nulls so this is pointless

This is what your code should look like to do what it is written to do:

DECLARE @AssignedGroupIDs AS NVARCHAR(4000)
 
    SELECT
            @AssignedGroupIDs=  '1470, 1471, 1472, 1474, 1475, 1477, 1478, 1480, ' + Convert(NVARCHAR,tbGroup.GroupID )
        FROM
            tbGroup,tbAssignedGroups
        WHERE
         tbGroup.GroupID = tbAssignedGroups.GroupID
            AND tbAssignedGroups.UserID = 2
            AND tbGroup.IsDeleted = 0
 
        select @AssignedGroupIDs

也许这不是你想要的，但这就是你写的。



如果您需要使用COALESCE的帮助，请告知我，我会帮助您整理所需的东西。



希望有所帮助^ _ ^

Andy

Perhaps this is not what you intended, but it's what you wrote.

If you need help using COALESCE then let mw know and I'll help you put together what you need.

Hope that helps ^_^
Andy

这篇关于查询不是正确的输出方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！