求一Sql语句

Question

id    transAmt      transDate             payToOffer  backToOrder
1      20.3200 2007-02-03 00:00:00.000 0 0
2      23.3200 2007-03-03 00:00:00.000 1 0
3     100.3200 2007-03-03 00:00:00.000 0 0
4     200.3200 2007-05-03 00:00:00.000 0 0
5     220.3200 2007-04-03 00:00:00.000 1 0
6     10.3200 2007-05-03 00:00:00.000 0 0
7     20.3200 2007-05-03 00:00:00.000 0 0
8     20.3200 2007-06-03 00:00:00.000 0 1
9     20.3200 2007-07-03 00:00:00.000 0 0
10     20.3200 2007-07-03 00:00:00.000 0 0
11     20.3200 2007-08-03 00:00:00.000 0 1
12     20.3200 2007-09-03 00:00:00.000 0 0
13     20.3200 2007-10-03 00:00:00.000 0 0
14     20.3200 2007-10-03 00:00:00.000 0 1
15     20.3200 2007-12-03 00:00:00.000 0 0
16     20.3200 2007-12-03 00:00:00.000 0 0
17     20.3200 2007-12-03 00:00:00.000 0 1
18     20.3200 2007-12-03 00:00:00.000 1 0
我想根据每季度来对transAmt求和,再根据payToOffer=1 or backToOrder=1表示金额已清分了clearAmt,  unclearAmt根据 payToOffer=1 and backToOrder=1 最后达到如下效果
puarter  clearAmt  unclearAmt   
1         23.3200   123.64
2         220.3200  251.28
3         20.32     60.96
4         60.96     40.64

Answer 1

一条SQL语句不好实现你要的结果，需要写存储过程，把你要的结果集插入到临时表中

Answer 2

再根据payToOffer=1 or backToOrder=1表示金额已清分了clearAmt, unclearAmt根据 payToOffer=1 and backToOrder=1 最后达到如下效果=======看不大懂了，用 SUM ＋ CASE ＋ GROUP BY ＋ DATEPART 函数一条语句应该是可以实现的

Answer 3

1
trySELECT 
 SUM(CASE WHEN payToOffer=1 THEN transAmt ELSE 0 END) clearAmt,
 SUM(CASE WHEN backToOrder=1 THEN transAmt ELSE 0 END) unclearAmt 
FROM tbl 
GROUP BY DATEPART(q, transDate)
2.
payToOffer=1 and backToOrder=1======看样本数据似乎没有同时出现这个情况

Answer 4

写错了   unclearAmt根据 payToOffer=0 and backToOrder=0