我有一张桌子
CREATE TABLE #Table1
(
[RowID] INT NOT NULL PRIMARY KEY IDENTITY(1, 1)
, [Column1] BIGINT NOT NULL
, [Column2] CHAR(10) NOT NULL
, [Column3] CHAR(5) NOT NULL
, [Amount] NUMERIC(16, 4) NOT NULL DEFAULT 0.0000
, [SequenceNumber] INT NOT NULL
, [SequenceType] CHAR(1) NOT NULL
)
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50019.0000, 1, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50021.0000, 2, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50023.0000, 3, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50025.0000, 4, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50027.0000, 5, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50029.0000, 6, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50033.0000, 8, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 50035.0000, 9, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 2754246.0000, 10, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 2660870.0000, 11, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 7938347.0000, 12, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00963', 7951196.0000, 13, 'D');
INSERT INTO #Table1 VALUES (1, '62546J307', '00964', 2660870.0000, 11, 'P');
INSERT INTO #Table1 VALUES (1, '62546J307', '00964', 7938347.0000, 12, 'D');
SELECT * FROM #Table1;
DROP TABLE #Table1
表格行
我需要根据“列1”,“列2”和“列3”的列“ SequenceType”值找出最大和最小金额。
输出列:Column1,Column2,Column3,MaxAmountForSeqTypeP,MinAmountForSeqTypeP,MaxAmountForSeqTypeD,MinAmountForSeqTypeP
预期产量
像这样:
select Column1, Column2, Column3,
max(case when SequenceType = 'P' then amount else null end) MaxP,
min(case when SequenceType = 'P' then amount else null end) MinP,
max(case when SequenceType = 'D' then amount else null end) MaxD,
min(case when SequenceType = 'D' then amount else null end) MinD
from Table1
group by Column1, Column2, Column3
或者这可能会更快:
;with cte as (
select Column1, Column2, Column3,
case when SequenceType = 'P' then amount else null end P,
case when SequenceType = 'D' then amount else null end D
from Table1
)
select Column1, Column2, Column3, Max(P) MaxP, Min(P) MinP, Max(D) MaxD, MIn(D) MinD
from cte
group by Column1, Column2, Column3
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句