在Sqlite查询中声明局部变量 [英] Declare local variable in Sqlite query

问题描述

您好，

有没有办法在Sqlite中做到这样的事情？

我想声明一个变量并在其后面添加列。

但是Sqlite dows不允许你使用变量。

Hello,
Is there any way to do it something like this in Sqlite?
I want to declar a variable and append columns to it.
But Sqlite dows not allows you to use variables.

Declare @var AS VARCHAR(MAX)
SET @var = ''
SELECT   
@var =@var +  Column1 + '-' + CAST(Column2 AS VARCHAR(50))   +CHAR(10) 	
FROM TableName
SELECT @var

我搜索了它但无法找到解决方案..任何机构都遇到过这个请求帮助。

I searched for it but not able to find solution.. any body have come across this please help.

推荐答案

SqLite不会让你这样做 - 而且你不是第一个问的人。如果您先搜索过Google，那么您会发现很多问题和答案： Google [ ^ ]有几个workrounds（我很确定它们都是相同的）： http://comments.gmane.org/gmane.comp.db.sqlite.general/62041 [ ^ ]



基本上，它是一个轻量级数据库 - 它没有拥有SQL服务器或MySql的所有功能。

你可能会更好地完成这个复杂的工作在你的应用程序中的东西，而不是试图强迫SqLite做一些它不是为它设计的东西。

SqLite doesn't let you do that - and you aren't the first to ask. If you had googled first, you would have found a lot of questions and responses on this: Google[^] There are a couple of "workrounds" (which I'm pretty sure are all the same one): http://comments.gmane.org/gmane.comp.db.sqlite.general/62041[^]

Basically, it's a Lightweight database - it doesn't have all the features of SQL server or MySql.
You would probably be better off doing the complex stuff in your application, rather than trying to force SqLite to do something it is not designed for.

有解决方法

它可以在内存临时表中完成

为变量创建内存临时表

BEGIN

PRAGMA temp_store = 2;

CREATE TEMP TABLE _Variables（Name TEXT PRIMARY KEY，RealValue REAL，IntegerValue INTEGER，BlobValue BLOB，TextValue TEXT）;



声明变量

INSERT INTO [_Variables]（[Name]）VALUES（'VariableName'）;



分配变量（选择正确的存储类）

UPDATE [_Variables] SET [IntegerValue] = ... WHERE Name ='VariableName ';



获取变量值（在表达式中使用）

（SELECT coalesce（RealValue，IntegerValue，BlobValue） ，TextValue）FROM _Variables WHERE Name ='VariableName'LIMIT 1）



DROP TABLE _Variables;

END;





参考： [ ^ ]

过程（存储与否）不是SQLite功能的一部分。因此变量声明不起作用。





请注意，如果在SQL Server中运行查询，它将无法正常工作你期待...我想你基本上希望你的连接发生在你的每一行。 for each行不是sql概念：SQL语言被设计为for all行。你需要游标（或其他语言结构）为每一行做的事情......

Procedures (stored or not) are not part of SQLite capabilities. So variable declaration don't work.


Note that if you run your query in SQL Server it won't work either the way you expect... I suppose you basically want your concatenation to happen " for each " row of your table. The "for each" row is not a sql notion : SQL language has been designed to think as "for all" rows. You need cursors (or other language constructs) to do "for each row" stuff...

这篇关于在Sqlite查询中声明局部变量的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！