tableA表数据如下:
id time do type sku season brand cata qty disamt amt date
4 2202 S120097 2 3000012 C55770 343800 100000 1 270 99 2001/1/1
4 2202 S120097 2 3000012 C55770 343800 100000 1 330 109 2001/1/1
4 2203 S120098 2 3000012 C55770 343800 100000 1 270 99 2001/1/1
4 2203 S120099 2 8000857 A01630 800200 93100 1 0 100 2001/1/1
4 2204 S120100 2 8004251 A01630 800200 90000 1 0 130 2001/1/1
4 2204 S120101 2 3000012 C55770 343800 100000 1 367 129 2001/1/1
7 2201 S137012 2 4000012 A00770 046000 10000 1 100 178 2001/1/1
7 2201 S137012 2 4000012 A00770 046000 10000 1 100 298 2001/1/1
7 2201 S137012 2 4000012 A00770 046000 10000 1 200 298 2001/1/1
7 2202 S137013 2 4000001 A00770 046000 10000 1 23 12 2001/1/1
7 2202 S137013 2 4000001 A00770 046000 10000 1 23 12 2001/1/1
7 2204 S137014 2 5000012 B46520 051500 10000 1 174 175 2001/1/1
7 2206 S137015 2 4000001 C74110 045900 10000 1 80 379 2001/1/1
7 2206 S137015 2 4000001 C74110 045900 10000 1 0 29 2001/1/1
7 2206 S137015 3 9000766 C74110 045900 10000 -1 0 -160 2001/1/1
7 2207 S137016 2 5000007 C74110 045900 10000 1 239 239 2001/1/1
其中date+id+do列能确定一笔交易希望用select能得到如下统计
date hh season brand cata qty disamt amt 交易笔数
2001/1/1 22 C55770 343800 100000 4 1237 436 2
2001/1/1 22 A01630 800200 93100 2 0 230 1
2001/1/1 22 A00770 046000 10000 5 446 798 2
2001/1/1 22 B46520 051500 10000 1 174 175 1
2001/1/1 22 C74110 045900 10000 3 319 478 2我用如下sql语句能得到这样的结果,但没法统计交易笔数
date hh season brand cata qty disamt amt
2001/1/1 22 C55770 343800 100000 4 1237 436
2001/1/1 22 A01630 800200 93100 2 0 230
2001/1/1 22 A00770 046000 10000 5 446 798
2001/1/1 22 B46520 051500 10000 1 174 175
2001/1/1 22 C74110 045900 10000 3 319 478sql语句如下:
SELECT date,left(time,2) as hh,season,brand,cata,
sum(case when left(do,1)='s' then qty else -qty end) as qty,
sum(case when left(do,1)='s' then amt else -amt end) as amt,
sum((case when sku='9000386' or txmsg6='9000888' then
case when left(do,1)='s' then amt else -amt end
else 0 end)) zkq,
sum((case when txmsg6='9000399' then
case when left(do,1)='s' then amt else -amt end
else 0 end)) dyq, sum((case when sku='9000766' then
case when left(do,1)='s' then amt else -amt end
else 0 end)) zr,
sum(case when left(do,1)='s' then disamt else -disamt end ) as disamt
FROM trans_2 a
where date=@date
group by date,left(time,2),season,brand,cata;大家帮忙想一下如何写sql能同时统计出交易笔数
id time do type sku season brand cata qty disamt amt date
4 2202 S120097 2 3000012 C55770 343800 100000 1 270 99 2001/1/1
4 2202 S120097 2 3000012 C55770 343800 100000 1 330 109 2001/1/1
4 2203 S120098 2 3000012 C55770 343800 100000 1 270 99 2001/1/1
4 2203 S120099 2 8000857 A01630 800200 93100 1 0 100 2001/1/1
4 2204 S120100 2 8004251 A01630 800200 90000 1 0 130 2001/1/1
4 2204 S120101 2 3000012 C55770 343800 100000 1 367 129 2001/1/1
7 2201 S137012 2 4000012 A00770 046000 10000 1 100 178 2001/1/1
7 2201 S137012 2 4000012 A00770 046000 10000 1 100 298 2001/1/1
7 2201 S137012 2 4000012 A00770 046000 10000 1 200 298 2001/1/1
7 2202 S137013 2 4000001 A00770 046000 10000 1 23 12 2001/1/1
7 2202 S137013 2 4000001 A00770 046000 10000 1 23 12 2001/1/1
7 2204 S137014 2 5000012 B46520 051500 10000 1 174 175 2001/1/1
7 2206 S137015 2 4000001 C74110 045900 10000 1 80 379 2001/1/1
7 2206 S137015 2 4000001 C74110 045900 10000 1 0 29 2001/1/1
7 2206 S137015 3 9000766 C74110 045900 10000 -1 0 -160 2001/1/1
7 2207 S137016 2 5000007 C74110 045900 10000 1 239 239 2001/1/1
其中date+id+do列能确定一笔交易希望用select能得到如下统计
date hh season brand cata qty disamt amt 交易笔数
2001/1/1 22 C55770 343800 100000 4 1237 436 2
2001/1/1 22 A01630 800200 93100 2 0 230 1
2001/1/1 22 A00770 046000 10000 5 446 798 2
2001/1/1 22 B46520 051500 10000 1 174 175 1
2001/1/1 22 C74110 045900 10000 3 319 478 2我用如下sql语句能得到这样的结果,但没法统计交易笔数
date hh season brand cata qty disamt amt
2001/1/1 22 C55770 343800 100000 4 1237 436
2001/1/1 22 A01630 800200 93100 2 0 230
2001/1/1 22 A00770 046000 10000 5 446 798
2001/1/1 22 B46520 051500 10000 1 174 175
2001/1/1 22 C74110 045900 10000 3 319 478sql语句如下:
SELECT date,left(time,2) as hh,season,brand,cata,
sum(case when left(do,1)='s' then qty else -qty end) as qty,
sum(case when left(do,1)='s' then amt else -amt end) as amt,
sum((case when sku='9000386' or txmsg6='9000888' then
case when left(do,1)='s' then amt else -amt end
else 0 end)) zkq,
sum((case when txmsg6='9000399' then
case when left(do,1)='s' then amt else -amt end
else 0 end)) dyq, sum((case when sku='9000766' then
case when left(do,1)='s' then amt else -amt end
else 0 end)) zr,
sum(case when left(do,1)='s' then disamt else -disamt end ) as disamt
FROM trans_2 a
where date=@date
group by date,left(time,2),season,brand,cata;大家帮忙想一下如何写sql能同时统计出交易笔数
date,left(time,2) as hh,season,brand,cata,
sum(case when left(do,1)='s' then qty else -qty end) as qty,
sum(case when left(do,1)='s' then amt else -amt end) as amt,
sum((case when sku='9000386' or txmsg6='9000888' then case when left(do,1)='s' then amt else -amt end else 0 end)) zkq,
sum((case when txmsg6='9000399' then case when left(do,1)='s' then amt else -amt end else 0 end)) dyq,
sum((case when sku='9000766' then case when left(do,1)='s' then amt else -amt end else 0 end)) zr,
sum(case when left(do,1)='s' then disamt else -disamt end ) as disamt,
count(1) as 交易笔数
FROM
tb a
where
date=@date
group by
date,left(time,2),season,brand,cata
如下:
id do date
7 S137015 2001/1/1
7 S137016 2001/1/1
4 S120097 2001/1/1
这样是3笔交易
SELECT
a.date,left(a.time,2) as hh,a.season,a.brand,a.cata,
sum(case when left(a.do,1)='s' then a.qty else -a.qty end) as qty,
sum(case when left(a.do,1)='s' then a.amt else -a.amt end) as amt,
sum((case when a.sku='9000386' or a.txmsg6='9000888' then case when left(a.do,1)='s' then a.amt else -a.amt end else 0 end)) zkq,
sum((case when a.txmsg6='9000399' then case when left(a.do,1)='s' then a.amt else -a.amt end else 0 end)) dyq,
sum((case when a.sku='9000766' then case when left(a.do,1)='s' then a.amt else -a.amt end else 0 end)) zr,
sum(case when left(a.do,1)='s' then a.disamt else -a.disamt end ) as disamt,
b.交易笔数
FROM
tb a,
(select date,id,do ,count(1) as 交易笔数 from tb group by date,id,do)b
where
a.date=@date
and
a.date=b.date
group by
a.date,left(a.time,2),a.season,a.brand,a.cata,b.交易笔数
(select date,id,do ,count(1) as 交易笔数 from tb group by date,id,do)b
连接
谢谢fredrickhu。这样结果出来的交易笔数还是记录行数。没有其他的思路大家帮帮谢谢!