在 SQLite 中声明变量并使用它

小开

尝试使用绑定值。您不能像在 T-SQL 中那样使用变量，但是您可以使用“参数”。我希望下面的链接是有用的

小开

最佳答案

SQLite 不支持本机变量语法，但是可以使用内存中的临时表实现同样的功能。

我已经在大型项目中使用了下面的方法，效果非常好。

    /* Create in-memory temp table for variables */
BEGIN;


PRAGMA temp_store = 2; /* 2 means use in-memory */
CREATE TEMP TABLE _Variables(Name TEXT PRIMARY KEY, RealValue REAL, IntegerValue INTEGER, BlobValue BLOB, TextValue TEXT);


/* Declaring a variable */
INSERT INTO _Variables (Name) VALUES ('VariableName');


/* Assigning a variable (pick the right storage class) */
UPDATE _Variables SET IntegerValue = ... WHERE Name = 'VariableName';


/* Getting variable value (use within expression) */
... (SELECT coalesce(RealValue, IntegerValue, BlobValue, TextValue) FROM _Variables WHERE Name = 'VariableName' LIMIT 1) ...


DROP TABLE _Variables;
END;

小开

Herman 的解决方案是可行的，但是可以简化，因为 Sqlite 允许在任何字段上存储任何值类型。

下面是一个更简单的版本，它使用一个声明为 TEXT的 Value字段来存储任何值:

CREATE TEMP TABLE IF NOT EXISTS Variables (Name TEXT PRIMARY KEY, Value TEXT);


INSERT OR REPLACE INTO Variables VALUES ('VarStr', 'Val1');
INSERT OR REPLACE INTO Variables VALUES ('VarInt', 123);
INSERT OR REPLACE INTO Variables VALUES ('VarBlob', x'12345678');


SELECT Value
FROM Variables
WHERE Name = 'VarStr'
UNION ALL
SELECT Value
FROM Variables
WHERE Name = 'VarInt'
UNION ALL
SELECT Value
FROM Variables
WHERE Name = 'VarBlob';

小开

赫尔曼的解决方案对我有效，但是 ...让我有点搞不清楚状况。我把根据他的回答做的样带也包括进去了。我的答案中的附加特性包括外键支持、自动递增键以及使用 last_insert_rowid()函数获取事务中最后一个自动生成的键。

当我遇到一个需要三个外键的事务时，我需要这个信息，但是我只能用 last_insert_rowid()获得最后一个。

PRAGMA foreign_keys = ON;   -- sqlite foreign key support is off by default
PRAGMA temp_store = 2;      -- store temp table in memory, not on disk


CREATE TABLE Foo(
Thing1 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL
);


CREATE TABLE Bar(
Thing2 INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
FOREIGN KEY(Thing2) REFERENCES Foo(Thing1)
);


BEGIN TRANSACTION;


CREATE TEMP TABLE _Variables(Key TEXT, Value INTEGER);


INSERT INTO Foo(Thing1)
VALUES(2);


INSERT INTO _Variables(Key, Value)
VALUES('FooThing', last_insert_rowid());


INSERT INTO Bar(Thing2)
VALUES((SELECT Value FROM _Variables WHERE Key = 'FooThing'));


DROP TABLE _Variables;


END TRANSACTION;

小开

对于只读变量(即一次性设置并在查询中的任何位置使用的常量值) ，请使用公共表表达式(Common Table Expression，CTE)。

WITH const AS (SELECT 'name' AS name, 10 AS more)
SELECT table.cost, (table.cost + const.more) AS newCost
FROM table, const
WHERE table.name = const.name

SQLite WITH 子句

小开

我找到了一个将变量赋值给 COLUMN 或 TABLE 的解决方案:

conn = sqlite3.connect('database.db')
cursor=conn.cursor()
z="Cash_payers"   # bring results from Table 1 , Column: Customers and COLUMN
# which are pays cash
sorgu_y= Customers #Column name
query1="SELECT  * FROM  Table_1 WHERE " +sorgu_y+ " LIKE ? "
print (query1)
query=(query1)
cursor.execute(query,(z,))

Don't forget input one space between the WHERE and double quotes 在双引号和 LIKE 之间

小开

创建用于 SQLiteSELECT (和其他一些)语句的“ VARIABLE”

CREATE TEMP TABLE IF NOT EXISTS variable AS SELECT '2002' AS _year; --creating the "variable" named "_year" with value "2002"
UPDATE variable SET _year = '2021'; --changing the variable named "_year" assigning "new" value "2021"
SELECT _year FROM variable; --viewing the variable
SELECT 'TEST', (SELECT _year FROM variable) AS _year; --using the variable
SELECT taxyr FROM owndat WHERE taxyr = (SELECT _year FROM variable); --another example of using the variable
SELECT DISTINCT taxyr FROM owndat WHERE taxyr IN ('2022',(SELECT _year FROM variable)); --another example of using the variable
DROP TABLE IF EXISTS variable; --releasing the "variable" if needed to be released

小开

在你的例子中使用来自 denverCR 的例子:

WITH tblCTE AS (SELECT "Joe" AS namevar)
SELECT * FROM table, tblCTE
WHERE name = namevar

As a beginner I found other answers too difficult to understand, hope this works

小开

在阅读了所有的答案之后，我更喜欢这样的东西:

select *
from table, (select 'name' as name) const
where table.name = const.name