将命令从SQL Server转换为SQLITE

Question

我有这个命令，我想在flutter sqlite中使用它，但它显示了错误，因为sqlite中没有DECLARE。

DECLARE @totalTable TABLE 
(
id INTEGER IDENTITY(1, 1), 
account_id INTEGER, 
title TEXT, 
account_number TEXT, 
client_name TEXT, 
total REAL
)
INSERT INTO @totalTable (account_id, title, account_number) 
SELECT id, title, account_number 
FROM Accounts 
DECLARE @counterVariable INTEGER, @startLoop INTEGER 
SET @counterVariable = (SELECT COUNT(id) FROM @totalTable) 
SET @startLoop = 1 
WHILE (@startLoop <= @counterVariable) 
BEGIN 
UPDATE @totalTable 
SET total = (SELECT SUM(total) FROM Bills 
WHERE account_id = (SELECT account_id 
FROM @totalTable 
WHERE id = @startLoop)), 
client_name = (SELECT client_name FROM Clients 
WHERE id = (SELECT MIN(client_id) 
FROM Bills 
WHERE account_id = (SELECT MIN(account_id) 
FROM @totalTable 
WHERE id = @startLoop))) 
WHERE id = @startLoop 
SET @startLoop = @startLoop + 1 
END 
SELECT account_id, title, client_name, account_number, total 
FROM @totalTable

有没有办法重写这个命令以便在sqlite中使用？

Answer 1

在询问朋友后，我发现了这个用于相同结果的简短命令

SELECT        Accounts.id, Accounts.account_number, Clients.client_name, Accounts.title, SUM(Bills.total) AS total
FROM            Accounts INNER JOIN
Bills ON Accounts.id = Bills.account_id LEFT OUTER JOIN
Clients ON Bills.client_id = Clients.id
GROUP BY Accounts.id, Accounts.account_number, Clients.client_name, Accounts.title