如何在sql 2008中创建数组 [英] How to make array in 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 ）

我尝试过：



请帮助我在这个错误中我有很长一段时间寻找解决方案

解决方案

< blockquote>它不起作用，因为当你创建你的数组时你没有创建一个数组。 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 problem
but win i put this array in query or sub query it's not work
give me no error just give me null value

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

the sub query

SELECT 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 Expr4
FROM AzenSarfKhazna
WHERE 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 id
FROM AccountsDefinition,Directexpenses 
WHERE AccountsDefinition.[HaveFather] = Directexpenses.[DEXID]
)

then just use a join

SELECT 
SUM(ISNULL(AzenSarfKhazna.[KhaznaTotal],0)) AS Expr4 
FROM AzenSarfKhazna 
INNER JOIN @EmployeeList on AzenSarfKhazna.TypeID = EmployeeList.id
WHERE 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 a1
INNER JOIN AzenSarfKhazna as a2 on a1.TypeID = a2.ID
WHERE AzenSarfKhazna.[OPID] = '1' 
AND a1.[Type] = 'Other' 
AND a2.[HaveFather] = Directexpenses.[DEXID]

Hope that helps
Andy

这篇关于如何在sql 2008中创建数组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！