首先是SQL命令
select 商户号,sum(全部) 全部,SUM(夜间消费) 夜间消费, 100*sum(夜间消费)/(sum(全部))
from (
select 商户号,count(*) as 夜间消费
from aa
where aa.交易金额>=10000
and
datepart(hh,交易时间)>=20 or datepart(hh,交易时间)<=24
group by 商户号
union all
select 商户号,count(*) as 全部
from aa
group by 商户号)
a group by 商户号;
提示
消息 207，级别 16，状态 1，第 1 行
列名 '全部' 无效。
消息 207，级别 16，状态 1，第 1 行
列名 '全部' 无效。
数据源是
交易日期 交易时间 交易金额 商户号
2015-01-22 2015-01-22 17:56:30 4997 Z08000000614316
2015-01-22 2015-01-22 17:56:30 986 Z08000000619355
2015-01-22 2015-01-22 12:11:22 2600 Z12000000038654
2015-01-22 2015-01-22 12:11:22 2560 Z08000000604840
2015-01-22 2015-01-22 12:11:23 12318 Z08000000410398
2015-01-22 2015-01-22 12:11:23 4999 Z08000000594347
2015-01-22 2015-01-22 12:11:23 1900 Z08000000089064
2015-01-22 2015-01-22 12:11:23 162.1 Z08000000501892
2015-01-22 2015-01-22 00:09:24 3000 Z08000000127297
2015-01-22 2015-01-22 00:09:25 444 Z08000000587608
2015-01-22 2015-01-22 00:09:26 454 Z08000000619164
2015-01-22 2015-01-22 00:09:26 1133 Z08000000587622
2015-01-22 2015-01-22 00:09:27 166 Z08000000100112
2015-01-22 2015-01-22 00:09:27 34200 Z08000000608556
2015-01-22 2015-01-22 00:09:29 200 Z21000000099278
2015-01-22 2015-01-22 00:09:29 35000 Z08000000616800
2015-01-22 2015-01-22 00:04:57 168 Z08000000558520
2015-01-22 2015-01-22 00:04:57 2000 Z21000000360763
2015-01-22 2015-01-22 00:04:58 176 Z08000000507007
2015-01-22 2015-01-22 00:04:58 3500 Z08000000575444
2015-01-22 2015-01-22 00:04:59 500 Z08000000499875
2015-01-22 2015-01-22 00:05:00 240 Z08000000027589
2015-01-22 2015-01-22 00:05:02 1000 Z08000000510830
2015-01-22 2015-01-22 00:05:02 300 Z08000000118054
2015-01-22 2015-01-22 12:11:23 48550 Z01000000013998
2015-01-22 2015-01-22 12:11:24 350 Z08000000445169
2015-01-22 2015-01-22 12:11:24 448 Z08000000401555
2015-01-22 2015-01-22 12:11:24 3238 Z08000000535783
2015-01-22 2015-01-22 12:11:24 170 Z08000000598932
2015-01-22 2015-01-22 12:11:24 24 Z08000000557765
2015-01-22 2015-01-22 12:11:24 1033.5 Z08000000096643
2015-01-22 2015-01-22 12:11:25 190 Z08000000276429
我想要的结果是
商户号 晚上20-24 全部 比例
Z01000000007407 0 3 0.00%
Z01000000008904 2 5 40.00%
Z01000000010055 3 5 60.00%
Z01000000010153 0 1 0.00%
Z01000000010257 0 1 0.00%
Z01000000010278 3 7 42.86%
Z01000000010744 0 3 0.00%
Z01000000010981 0 6 0.00%
Z01000000011261 1 9 11.11%
Z01000000011782 0 1 0.00%
Z01000000011936 0 1 0.00%
Z01000000012021 4 26 15.38%
Z01000000012109 2 2 100.00%
这个样子的
------解决思路----------------------
SELECT a.商户号,
a.全部,
ISNULL(b.夜间消费,0) 夜间消费,
Convert(varchar(6),
Convert(decimal(6,2),
100.0*ISNULL(b.夜间消费,0)/a.全部
)
)+'%' 比例
FROM (
select 商户号,count(*) as 全部
from aa
group by 商户号
) a
LEFT JOIN (
select 商户号,count(*) as 夜间消费
from aa
where aa.交易金额>=10000
and datepart(hh,交易时间)>=20
group by 商户号
) b
ON a.商户号 = b.商户号
------解决思路----------------------
--错误很明显,先看看下面的查询结果就知道了，结果集中有没有列名为‘全部’的？ 两个不同字段union all 应该是取第一个‘夜间消费’吧
select 商户号,count(*) as 夜间消费 from aa where aa.交易金额>=10000
and datepart(hh,交易时间)>=20 or datepart(hh,交易时间)<=24 --or 应该改为and吧
group by 商户号
union all
select 商户号,count(*) as 全部 from aa
group by 商户号