如何在sql 2008中创建数组

本文介绍了如何在sql 2008中创建数组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 我做了一个小数组，它的工作正常没问题 但是我把这个数组放在查询或子查询中它不起作用 给我没有错误只给我空值 DECLARE @ EmployeeList nvarchar （max）= ' '; （ SELECT @ EmployeeList = @ EmployeeList + ' ，' + CAST（AccountsDefinition。[ID] AS nvarchar （max）） FROM AccountsDefinition，Directexpenses WHERE AccountsDefinition。[HaveFather] = Directexpenses。[DEXID]） SELECT @ EmployeeList ， 135 ， 136 ， 137 ， 138 ， 139 ， 140 ， 141 ， 142 ， 143 ， 144 ， 145 ， 146 ， 147 ， 148 ， 149 ， 150 ， 151 ， 152 ， 153 ， 154 ， 155 ， 156 ， 157 子查询 SELECT SUM （ISNULL（AzenSarfKhazna。[KhaznaTotal]， 0 ）） AS Expr4 FROM AzenSarfKhazna WHERE AzenSarfKhazna。[OPID] = ' 1' 和 AzenSar fKhazna。[ Type ] = ' 其他' AND AzenSarfKhazna。[TypeID] IN （ SELECT @ EmployeeList ） 我尝试过： 请帮助我在这个错误中我有很长一段时间寻找解决方案解决方案 它不起作用，因为当你创建你的数组时你没有创建一个数组。 SQL中没有任何这样的概念。你所做的是创建一个单独的文本字符串。 现在，你的子查询在其WHERE子句中所做的是将TypeID值与此字符串进行比较。除非你的TypeID值是一个完全匹配，135,136,137,138,139,140,​​141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157的字符串，否则这不起作用。 你不创建一个字符串，或者你是什么重新调用数组。您可以将这些查询组合到一个查询中： SELECT SUM（ISNULL（AzenSarfKhazna。[KhaznaTotal]，0））AS Expr4 来自AzenSarfKhazna 在哪里AzenSarfKhazna。[OPID] ='1'和AzenSarfKhazna。[类型] ='其他'和AzenSarfKhazna。[TypeID] IN（SELECT AccountsDefinition。[ID] FROM AccountsDefinition，Directexpenses WHERE AccountsDefinition。[HaveFather] = Directexpenses。[DEXID]） 您的'Array'不是数组或集合。这是一个nvarchar。你知道，这件事： DECLARE @EmployeeList nvarchar（max）=''; 不要使用'Array'这个词。它们在sql中不存在。你需要一个表，表变量，临时表或公用表表达式（CTE）。 psst。不要使用表或临时表。你不需要它们。 表变量： 声明 @ EmployeeList as table （id int 不 null ） 插入 进入 @ EmployeeList 选择 AccountsDefinition。[ID] FROM AccountsDefinition，Directexpenses WHERE AccountsDefinition。[HaveFather] = Directexpenses。[DEXID] CTE： EmployeeList as （ 选择 AccountsDefinition。[ID] as id FROM AccountsDefinition，Directexpenses WHERE AccountsDefinition。[HaveFather] = Directexpenses。[DEXID] ） 然后只需使用连接 SELECT SUM（ISNULL（AzenSarfKhazna。[KhaznaTotal]， 0 ）） AS Expr4 FROM AzenSarfKhazna INNER JOIN @ EmployeeList AzenSarfKhazna.TypeID = EmployeeList.id WHERE AzenSarfKhazna。[OPID] = ' 1' AND AzenSarfKhazna。[类型] = ' 其他' 或者，正确方法是在同一查询中使用该表： SELECT SUM（ISNULL （a1。[KhaznaTotal]， 0 ）） AS Expr4 FROM AzenSarfKhazna as a1 INNER JOIN AzenSarfKhazna as a2 on a1.TypeID = a2.ID WHERE AzenSarfKhazna。[OPID] = ' 1 ' AND a1。[类型] = ' 其他' AND a2。[HaveFather] = Directexpenses。 [DEXID] 希望有所帮助 Andy i have make a small array and it's work fine no problembut win i put this array in query or sub query it's not workgive me no error just give me null valueDECLARE @EmployeeList nvarchar(max)= '';(SELECT @EmployeeList = @EmployeeList + ',' + CAST(AccountsDefinition.[ID] AS nvarchar(max)) FROM AccountsDefinition,Directexpenses WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID])SELECT @EmployeeList,135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157the sub querySELECT SUM(ISNULL(AzenSarfKhazna.[KhaznaTotal],0)) AS Expr4 FROM AzenSarfKhazna WHERE AzenSarfKhazna.[OPID] = '1' AND AzenSarfKhazna.[Type] = 'Other' AND AzenSarfKhazna.[TypeID] IN (SELECT @EmployeeList)What I have tried:help me please in this error i have bean a long time search for solution 解决方案 It doesn't work because when you created "your array" you didn't create an array. There isn't any such concept in SQL. What you did was create a single string of text.Now, what your subquery is doing in its WHERE clause is comparing a "TypeID" value to this string. Unless your TypeID value is a string that matches ",135,136,137,138,139,140,141,142,143,144,145,146,147,148,149,150,151,152,153,154,155,156,157" exactly, this is not going to work.You don't create a string, or what you're calling an "array". You can combine these queries into a single query:SELECT SUM(ISNULL(AzenSarfKhazna.[KhaznaTotal],0)) AS Expr4FROM AzenSarfKhaznaWHERE AzenSarfKhazna.[OPID] = '1' AND AzenSarfKhazna.[Type] = 'Other' AND AzenSarfKhazna.[TypeID] IN (SELECT AccountsDefinition.[ID] FROM AccountsDefinition,Directexpenses WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID])Your 'Array' isn't an array or set. It is a nvarchar. You know, this thing: DECLARE @EmployeeList nvarchar(max)= '';Don't use the word 'Array'. They don't exist in sql. You need a table, table variable, temp table or common table expression (CTE).psst. Don't use table or temp table. You don't need them.Table Variable:declare @EmployeeList as table(id int not null)insert into @EmployeeList select AccountsDefinition.[ID]FROM AccountsDefinition,Directexpenses WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID]CTE:with EmployeeList as ( select AccountsDefinition.[ID] as idFROM AccountsDefinition,Directexpenses WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID])then just use a joinSELECT SUM(ISNULL(AzenSarfKhazna.[KhaznaTotal],0)) AS Expr4 FROM AzenSarfKhazna INNER JOIN @EmployeeList on AzenSarfKhazna.TypeID = EmployeeList.idWHERE AzenSarfKhazna.[OPID] = '1' AND AzenSarfKhazna.[Type] = 'Other' Or, the correct way is to just use the table in the same query:SELECT SUM(ISNULL(a1.[KhaznaTotal],0)) AS Expr4 FROM AzenSarfKhazna as a1INNER JOIN AzenSarfKhazna as a2 on a1.TypeID = a2.IDWHERE AzenSarfKhazna.[OPID] = '1' AND a1.[Type] = 'Other' AND a2.[HaveFather] = Directexpenses.[DEXID]Hope that helpsAndy 这篇关于如何在sql 2008中创建数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！