SQL语句问题

Question

我有一个表如下：
create table test
( id integer,
  attr1 varchar(10),
  attr2 varchar(10),
  amount integer,
  flag integer
  );
  
  insert into test
  select 1, 'AA', 'AC', 9, 0
  Union all
  select 2, 'BB', 'BT', 10, 0
  UNION ALL
  SELECT 3, 'AA', 'AC', 6, 1
  UNION ALL
  SELECT 4, 'CC', 'BG', 20, 0
  UNION ALL
  SELECT 5, 'BB', 'BT', 3, 1
  UNION ALL
  SELECT 6, 'AA', 'AC', 3, 1
Id 是主键，Flag = 0 的记录代表总的未使用数量， Flag = 1的记录代表已使用的数量
按ATTR1，ATTR2分组，同组中Flag = 0 的记录只有一条，Flag = 1 的可能有多条，也可能没有
同组Flag = 0 的Id 肯定小于Flag = 1 的Id我想得到的结果是按ATTR1,ATTR2分组，得到已使用的和未使用的数量
未使用数量记录的id 取原本未使用数量记录的id，flag 也是一样
未使用数量若 = 0就过滤掉我写的SQL:
  SELECT ID, ATTR1, ATTR2, AMOUNT, FLAG FROM TEST
  WHERE FLAG = 1
  UNION ALL
  SELECT MIN(ID), ATTR1, ATTR2, MAX (AMOUNT)-SUM(AMOUNT)+MAX(AMOUNT)
  , 0
  FROM TEST
  GROUP BY ATTR1,ATTR2
  HAVING MAX(AMOUNT) - SUM(AMOUNT)+MAX(AMOUNT) > 0
  
求其他写法

Answer 1

select 
ATTR1, ATTR2,
sum(case when flag=0 then amount else 0 end) as 未使用,
sum(case when flag=1 then amount else 0 end) as 已使用
from test group by ATTR1, ATTR2
/*
ATTR1      ATTR2      未使用         已使用
---------- ---------- ----------- -----------
AA         AC         9           9
CC         BG         20          0
BB         BT         10          3
*/

Answer 2

select * from 
(
select id,
attr1,
attr2,
amount=t.amount-isnull((select sum(amount) 
from test 
where t.attr1=attr1 
and t.attr2=attr2 
and flag=1 
and t.flag<>1 
group by attr1,attr2),0),
flag  
from test t
)tt
where tt.amount<>0
order by flag desc
/*
id          attr1      attr2      amount      flag
----------- ---------- ---------- ----------- -----------
3           AA         AC         6           1
5           BB         BT         3           1
6           AA         AC         3           1
4           CC         BG         20          0
2           BB         BT         7           0
*/

Answer 3

SELECT attr1,attr2,CASE WHEN flag=0 THEN  SUM(amount) ELSE  MAX (AMOUNT)-SUM(AMOUNT)+MAX(AMOUNT) END Amount,flag
  FROM test 
  GROUP BY attr1,attr2,flag
  ORDER BY flag DESC 
attr1 attr2 Amount flag
---------- ---------- ----------- -----------
AA AC 3 1
BB BT 3 1
AA AC 9 0
BB BT 10 0
CC BG 20 0(5 行受影响)
楼住我觉得你的查询貌似有点不符合你自身的要求。另外，对于汇总的操作，最好还是用case when ，少用union。