MIN我知道不會在這里作業的原因是借方添加到余額中，或者TOP 1無法處理像紅色框中一樣為負的余額，我得到的結果是（-156152.33）而不是（443847.47），我怎樣才能得到每個 GROUP 的（底部）余額akawnt。
SELECT AccountTitle as akawnt, [Year], DepartmentName as depar, Debit, Credit
, Balance as balanse
FROM YearlyBudget
WHERE DepartmentName = DepartmentName
AND MotherTitle = MotherTitle
AND AccountTitle = AccountTitle
GROUP BY AccountTitle, [Year], DepartmentName, Debit, Credit, Balance
| 阿昆特 | 出發 | 借方 | 信用 | 平衡 |
|---|---|---|---|---|
| 帳號 1 | 部門1 | 411971.35 | 411971.35 | |
| 帳號 1 | 部門1 | 41666.31 | 370305.04 | |
| 帳號 1 | 部門1 | 46763.47 | 323541.57 | |
| 帳號 1 | 部門1 | 116549.53 | 206992.04 | |
| 帳號 1 | 部門1 | 60668.01 | 146324.03 | |
| 帳號 1 | 部門1 | 113003.92 | 33320.11 | |
| 帳號 1 | 部門1 | 63651.79 | -30331.68 | |
| 帳號 1 | 部門1 | 68005.37 | -98337.05 | |
| 帳號 1 | 部門1 | 57815.28 | -156152.33 | |
| 帳號 1 | 部門1 | 600000.00 | 443847.67 | |
| 賬戶2 | 部門2 | 2351500.00 | 2351500.00 | |
| 賬戶2 | 部門2 | 1178500.00 | 1173000.00 | |
| 賬戶2 | 部門2 | 2000.00 | 1171000.00 | |
| 賬戶2 | 部門2 | 1159500.00 | 1159500.00 | 2330500.00 |
假設在同一個表中有另一個帳戶和部門是 GROUP BY
SELECT akawnt, MIN(balanse)
FROM (
SELECT AccountTitle as akawnt, [Year], DepartmentName as depar
, Debit, Credit, Balance as balanse
FROM YearlyBudget
WHERE DepartmentName = DepartmentName
AND MotherTitle = MotherTitle
AND AccountTitle = AccountTitle
GROUP BY AccountTitle, [Year], DepartmentName, Debit, Credit, Balance
) src
GROUP BY akawnt
實際輸出：
| 阿昆特 | 平衡 |
|---|---|
| 帳號 1 | -156152.33 |
| 賬戶2 | 1171000.00 |
期望的輸出：
| 阿昆特 | 平衡 |
|---|---|
| 帳號 1 | 443847.67 |
| 賬戶2 | 2330500.00 |
當您添加到借記卡時，MIN 也不適用。我想獲得最新的余額。
uj5u.com熱心網友回復：
您可以使用該row_number函式來選擇單行，但您需要一列來對其進行排序 - 我添加了一個標識列來演示，但我希望您有一個可以使用的真實日期時間列。
declare @YearlyBudget table (id int identity(1,1), akawnt varchar(12), DepartmentName varchar(12), Debit money, Credit money, Balance money);
insert into @YearlyBudget (akawnt, DepartmentName, Debit, Credit, Balance)
values
('Account1', 'Department1', 411971.35, null, 411971.35),
('Account1', 'Department1', null, 41666.31, 370305.04),
('Account1', 'Department1', null, 46763.47, 323541.57),
('Account1', 'Department1', null, 116549.53, 206992.04),
('Account1', 'Department1', null, 60668.01, 146324.03),
('Account1', 'Department1', null, 113003.92, 33320.11),
('Account1', 'Department1', null, 63651.79, -30331.68),
('Account1', 'Department1', null, 68005.37, -98337.05),
('Account1', 'Department1', null, 57815.28, -156152.33),
('Account1', 'Department1', 600000.00, null, 443847.67),
('Account2', 'Department2', 2351500.00, null, 2351500.00),
('Account2', 'Department2', null, 1178500.00, 1173000.00),
('Account2', 'Department2', null, 2000.00, 1171000.00),
('Account2', 'Department2', 1159500.00, 1159500.00, 2330500.00);
WITH cte AS (
SELECT akawnt, Balance
-- You need a way to order your rows, I have used an id as an example but I suspect you have a date column to use here
, ROW_NUMBER() OVER (PARTITION BY akawnt ORDER BY id) rn
FROM @YearlyBudget
)
SELECT akawnt, Balance
FROM cte
WHERE rn = 1;
注意：如果您將示例資料添加為 DDL DML（如我在此處所示），則回答起來會容易得多。并且您確保您有一個作業最少的可重現示例（您的示例中存在語法錯誤。
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/359851.html
標籤：sql sql-server 查询语句