当前位置： 代码迷 >> Sql Server >> 根据条件筛选出错误数据
  详细解决方案

根据条件筛选出错误数据

热度：19   发布时间：2016-04-24 09:16:55.0
根据条件筛选出异常数据
数据为
team card 金额 交易类型 交易状态
A NO.1 2490 消费 未成功
A NO.1 40 消费 未成功
A NO.1 150 消费 未成功
A NO.1 80 消费 未成功
A NO.1 1200 消费 成功
B NO.10 10000 消费 成功
C NO.10 505 消费 成功
A NO.2 1186 消费 成功
A NO.3 2250 消费 成功
A NO.4 9650 消费 成功
B NO.4 3500 消费 成功
B NO.4 6105 消费 成功
B NO.4 3800 消费 成功
C NO.4 515 消费 未成功
C NO.4 1000 消费 未成功
C NO.4 562 消费 未成功
C NO.4 18568 消费 未成功
B NO.5 78 消费 未成功
C NO.5 500 消费 未成功
B NO.6 288 消费 未成功
C NO.6 501 消费 未成功
B NO.7 27488 消费 未成功
C NO.7 502 消费 未成功
B NO.8 192 消费 未成功
C NO.8 503 消费 成功
B NO.9 150 消费 成功
C NO.9 504 消费 成功

我想要结果为
card  team 金额-成功 占比1 金额-失败 占比2 笔数-成功 占比3 笔数-失败 占比4 MAX MIX
NO.1 A 1200 30.30% 2760 69.70% 1 20.00% 4 80.00% 1200 0
NO.10 B 10000 100.00% 0.00% 1 100.00% 0 0.00% 10000 0
NO.10 C 505 100.00% 0.00% 1 100.00% 0 0.00% 505 0
NO.2 A 1186 100.00% 0.00% 1 100.00% 0 0.00% 1186 0
NO.3 A 2250 100.00% 0.00% 1 100.00% 0 0.00% 2250 0
NO.4 A 9650 100.00% 0.00% 1 100.00% 0 0.00% 9650 0
NO.4 B 13405 100.00% 0.00% 3 100.00% 0 0.00% 3500 6105
NO.4 C 0 0.00% 20645 100.00% 0 0.00% 4 100.00% 0 0
NO.5 B 0 0.00% 78 100.00% 0 0.00% 1 100.00% 0 0
NO.5 C 0 0.00% 500 100.00% 0 0.00% 1 100.00% 0 0
NO.6 B 0 0.00% 288 100.00% 0 0.00% 1 100.00% 0 0
NO.6 C 0 0.00% 501 100.00% 0 0.00% 1 100.00% 0 0
NO.7 B 0 0.00% 27488 100.00% 0 0.00% 1 100.00% 0 0
NO.7 C 0 0.00% 502 100.00% 0 0.00% 1 100.00% 0 0
NO.8 B 0 0.00% 192 100.00% 0 0.00% 1 100.00% 0 0
NO.8 C 503 100.00% 0 0.00% 1 100.00% 0 0.00% 503 0
NO.9 B 150 100.00% 0 0.00% 1 100.00% 0 0.00% 150 0
NO.9 C 504 100.00% 0 0.00% 1 100.00% 0 0.00% 504 0

描述
card 
team
金额-成功 1card 对应 1team 成功金额
占比1 1card 对应 1team 成功金额/1card 对应 1team 成功金额+1card 对应 1team 未成功金额
金额-成功 1card 对应 1team 未成功金额
占比2 1card 对应 1team 未成功金额/1card 对应 1team 成功金额+1card 对应 1team 未成功金额
笔数-失败 1card 对应 1team 成功笔数
占比3 1card 对应 1team 成功笔数/1card 对应 1team 成功笔数+1card 对应 1team 未成功笔数
笔数-失败 1card 对应 1team 未成功笔数
占比4      1card 对应 1team 未成功笔数/1card 对应 1team 成功笔数+1card 对应 1team 未成功笔数
MAX  1card对应1team 中CARD最大值
MIX          1card对应1team 中CARD最小值


这个东西有办法一步实现么
请赐教
------解决思路----------------------
SELECT card,team
	,ISNULL(SUM(CASE WHEN 交易状态='成功'THEN 金额 END),0)[金额-成功]
	,LTRIM(CAST(ISNULL(SUM(CASE WHEN 交易状态='成功'THEN 金额 END),0)*100./ISNULL(SUM(金额),1) AS DECIMAL(19,2)))+'%'[占比1]
	,ISNULL(SUM(CASE WHEN 交易状态='未成功'THEN 金额 END),0)[金额-失败]
	,LTRIM(CAST(ISNULL(SUM(CASE WHEN 交易状态='未成功'THEN 金额 END),0)*100./ISNULL(SUM(金额),1) AS DECIMAL(19,2)))+'%'[占比2]
	,ISNULL(SUM(CASE WHEN 交易状态='成功'THEN 1 END),0)[笔数-成功]
	,LTRIM(CAST(ISNULL(SUM(CASE WHEN 交易状态='成功'THEN 1 END),0)*100./ISNULL(SUM(1),1) AS DECIMAL(19,2)))+'%'[占比3]
	,ISNULL(SUM(CASE WHEN 交易状态='未成功'THEN 1 END),0)[笔数-失败]
	,LTRIM(CAST(ISNULL(SUM(CASE WHEN 交易状态='未成功'THEN 1 END),0)*100./ISNULL(SUM(1),1) AS DECIMAL(19,2)))+'%'[占比4]
	,ISNULL(MAX(CASE WHEN 交易状态='成功'THEN 金额 END),0)[MAX]
	,ISNULL(MIN(CASE WHEN 交易状态='成功'THEN 金额 ELSE 0 END),0)[MIX]
FROM TB
GROUP BY card,team
ORDER BY card
最后， MIX的规律不明确
------解决思路----------------------
use Tempdb
go
--> --> 
 
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([team] nvarchar(1),[card] nvarchar(5),[金额] MONEY,[交易类型] nvarchar(2),[交易状态] nvarchar(3))
Insert #T
select N'A',N'NO.1',2490,N'消费',N'未成功' union all
select N'A',N'NO.1',40,N'消费',N'未成功' union all
select N'A',N'NO.1',150,N'消费',N'未成功' union all
select N'A',N'NO.1',80,N'消费',N'未成功' union all
select N'A',N'NO.1',1200,N'消费',N'成功' union all
select N'B',N'NO.10',10000,N'消费',N'成功' union all
select N'C',N'NO.10',505,N'消费',N'成功' union all
select N'A',N'NO.2',1186,N'消费',N'成功' union all
select N'A',N'NO.3',2250,N'消费',N'成功' union all
select N'A',N'NO.4',9650,N'消费',N'成功' union all
select N'B',N'NO.4',3500,N'消费',N'成功' union all
select N'B',N'NO.4',6105,N'消费',N'成功' union all
select N'B',N'NO.4',3800,N'消费',N'成功' union all
select N'C',N'NO.4',515,N'消费',N'未成功' union all
select N'C',N'NO.4',1000,N'消费',N'未成功' union all
select N'C',N'NO.4',562,N'消费',N'未成功' union all
select N'C',N'NO.4',18568,N'消费',N'未成功' union all
select N'B',N'NO.5',78,N'消费',N'未成功' union all
select N'C',N'NO.5',500,N'消费',N'未成功' union all
select N'B',N'NO.6',288,N'消费',N'未成功' union all
select N'C',N'NO.6',501,N'消费',N'未成功' union all
select N'B',N'NO.7',27488,N'消费',N'未成功' union all
select N'C',N'NO.7',502,N'消费',N'未成功' union all
select N'B',N'NO.8',192,N'消费',N'未成功' union all
select N'C',N'NO.8',503,N'消费',N'成功' union all
select N'B',N'NO.9',150,N'消费',N'成功' union all
select N'C',N'NO.9',504,N'消费',N'成功'
Go
SELECT  [team]
       ,[card]
       ,[金额-成功]=SUM(CASE WHEN [交易状态]='成功'	 THEN [金额] ELSE 0 END)
       ,[占比1]=STR(SUM(CASE WHEN [交易状态]='成功'	 THEN [金额] ELSE 0 END)/SUM([金额]),5,2)+'%'
       ,[金额-失败]=SUM(CASE WHEN [交易状态]='未成功'	 THEN [金额] ELSE 0 END)
       ,[占比2]=STR(SUM(CASE WHEN [交易状态]='未成功'	 THEN [金额] ELSE 0 END)/SUM([金额]),5,2)+'%'
       ,[笔数-成功]=SUM(CASE WHEN [交易状态]='成功'	 THEN 1 ELSE 0 END)
       ,[占比3]=STR(SUM(CASE WHEN [交易状态]='成功'	 THEN 1.0 ELSE 0 END)/COUNT(*),5,2)+'%'
       ,[笔数-失败]=SUM(CASE WHEN [交易状态]='未成功'	 THEN 1 ELSE 0 END)
       ,[占比4]=STR(SUM(CASE WHEN [交易状态]='未成功'	 THEN 1.0 ELSE 0 END)/COUNT(*),5,2)+'%'
       ,[MAX金额]=MAX([金额])  
       ,[MIN金额]=MIN([金额])  
FROM    #T
GROUP BY [team]
       ,[card]

/*
team	card	金额-成功	占比1	金额-失败	占比2	笔数-成功	占比3	笔数-失败	占比4	MAX金额	MIN金额
A	NO.1	1200.00	 0.30%	2760.00	 0.70%	1	 0.20%	4	 0.80%	2490.00	40.00
B	NO.10	10000.00	 1.00%	0.00	 0.00%	1	 1.00%	0	 0.00%	10000.00	10000.00
C	NO.10	505.00	 1.00%	0.00	 0.00%	1	 1.00%	0	 0.00%	505.00	505.00
A	NO.2	1186.00	 1.00%	0.00	 0.00%	1	 1.00%	0	 0.00%	1186.00	1186.00
A	NO.3	2250.00	 1.00%	0.00	 0.00%	1	 1.00%	0	 0.00%	2250.00	2250.00
A	NO.4	9650.00	 1.00%	0.00	 0.00%	1	 1.00%	0	 0.00%	9650.00	9650.00
B	NO.4	13405.00	 1.00%	0.00	 0.00%	3	 1.00%	0	 0.00%	6105.00	3500.00
C	NO.4	0.00	 0.00%	20645.00	 1.00%	0	 0.00%	4	 1.00%	18568.00	515.00
B	NO.5	0.00	 0.00%	78.00	 1.00%	0	 0.00%	1	 1.00%	78.00	78.00
C	NO.5	0.00	 0.00%	500.00	 1.00%	0	 0.00%	1	 1.00%	500.00	500.00
B	NO.6	0.00	 0.00%	288.00	 1.00%	0	 0.00%	1	 1.00%	288.00	288.00
C	NO.6	0.00	 0.00%	501.00	 1.00%	0	 0.00%	1	 1.00%	501.00	501.00
B	NO.7	0.00	 0.00%	27488.00	 1.00%	0	 0.00%	1	 1.00%	27488.00	27488.00
C	NO.7	0.00	 0.00%	502.00	 1.00%	0	 0.00%	1	 1.00%	502.00	502.00
B	NO.8	0.00	 0.00%	192.00	 1.00%	0	 0.00%	1	 1.00%	192.00	192.00
C	NO.8	503.00	 1.00%	0.00	 0.00%	1	 1.00%	0	 0.00%	503.00	503.00
B	NO.9	150.00	 1.00%	0.00	 0.00%	1	 1.00%	0	 0.00%	150.00	150.00
C	NO.9	504.00	 1.00%	0.00	 0.00%	1	 1.00%	0	 0.00%	504.00	504.00
*/

------解决思路----------------------
同样可以处理，看你为0的时候，希望怎么处理
------解决思路----------------------
use Tempdb
go
--> --> 
  
if not object_id(N'Tempdb..#T') is null
    drop table #T
Go
Create table #T([team] nvarchar(1),[card] nvarchar(5),[金额] MONEY,[交易类型] nvarchar(2),[交易状态] nvarchar(3))
Insert #T
select N'A',N'NO.1',2490,N'消费',N'未成功' union all
select N'A',N'NO.1',40,N'消费',N'未成功' union all
select N'A',N'NO.1',150,N'消费',N'未成功' union all
select N'A',N'NO.1',80,N'消费',N'未成功' union all
select N'A',N'NO.1',1200,N'消费',N'成功' union all
select N'B',N'NO.10',10000,N'消费',N'成功' union all
select N'C',N'NO.10',505,N'消费',N'成功' union all
select N'A',N'NO.2',1186,N'消费',N'成功' union all
select N'A',N'NO.3',2250,N'消费',N'成功' union all
select N'A',N'NO.4',9650,N'消费',N'成功' union all
select N'B',N'NO.4',3500,N'消费',N'成功' union all
select N'B',N'NO.4',6105,N'消费',N'成功' union all
select N'B',N'NO.4',3800,N'消费',N'成功' union all
select N'C',N'NO.4',515,N'消费',N'未成功' union all
select N'C',N'NO.4',1000,N'消费',N'未成功' union all
select N'C',N'NO.4',562,N'消费',N'未成功' union all
select N'C',N'NO.4',18568,N'消费',N'未成功' union all
select N'B',N'NO.5',78,N'消费',N'未成功' union all
select N'C',N'NO.5',500,N'消费',N'未成功' union all
select N'B',N'NO.6',288,N'消费',N'未成功' union all
select N'C',N'NO.6',501,N'消费',N'未成功' union all
select N'B',N'NO.7',27488,N'消费',N'未成功' union all
select N'C',N'NO.7',502,N'消费',N'未成功' union all
select N'B',N'NO.8',192,N'消费',N'未成功' union all
select N'C',N'NO.8',503,N'消费',N'成功' union all
select N'B',N'NO.9',150,N'消费',N'成功' union all
select N'C',N'NO.9',504,N'消费',N'成功'
Go
SELECT  [team]
       ,[card]
       ,[金额-成功]=SUM(CASE WHEN [交易状态]='成功'     THEN [金额] ELSE 0 END)
       ,[占比1]=STR(SUM(CASE WHEN [交易状态]='成功'     THEN [金额] ELSE 0 END)*100/ISNULL(NULLIF(SUM([金额]),0),1),5,2)+'%'
       ,[金额-失败]=SUM(CASE WHEN [交易状态]='未成功'     THEN [金额] ELSE 0 END)
       ,[占比2]=STR(SUM(CASE WHEN [交易状态]='未成功'     THEN [金额] ELSE 0 END)*100/ISNULL(NULLIF(SUM([金额]),0),1),5,2)+'%'
       ,[笔数-成功]=SUM(CASE WHEN [交易状态]='成功'     THEN 1 ELSE 0 END)
       ,[占比3]=STR(SUM(CASE WHEN [交易状态]='成功'     THEN 1.0 ELSE 0 END)*100/COUNT(*),5,2)+'%'
       ,[笔数-失败]=SUM(CASE WHEN [交易状态]='未成功'     THEN 1 ELSE 0 END)
       ,[占比4]=STR(SUM(CASE WHEN [交易状态]='未成功'     THEN 1.0 ELSE 0 END)*100/COUNT(*),5,2)+'%'
       ,[MAX金额]=MAX([金额])  
       ,[MIN金额]=MIN([金额])  
FROM    #T
GROUP BY [team]
       ,[card]
 
/*
team	card	金额-成功	占比1	金额-失败	占比2	笔数-成功	占比3	笔数-失败	占比4	MAX金额	MIN金额
A	NO.1	1200.00	30.30%	2760.00	69.70%	1	20.00%	4	80.00%	2490.00	40.00
B	NO.10	10000.00	100.0%	0.00	 0.00%	1	100.0%	0	 0.00%	10000.00	10000.00
C	NO.10	505.00	100.0%	0.00	 0.00%	1	100.0%	0	 0.00%	505.00	505.00
A	NO.2	1186.00	100.0%	0.00	 0.00%	1	100.0%	0	 0.00%	1186.00	1186.00
A	NO.3	2250.00	100.0%	0.00	 0.00%	1	100.0%	0	 0.00%	2250.00	2250.00
A	NO.4	9650.00	100.0%	0.00	 0.00%	1	100.0%	0	 0.00%	9650.00	9650.00
B	NO.4	13405.00	100.0%	0.00	 0.00%	3	100.0%	0	 0.00%	6105.00	3500.00
C	NO.4	0.00	 0.00%	20645.00	100.0%	0	 0.00%	4	100.0%	18568.00	515.00
B	NO.5	0.00	 0.00%	78.00	100.0%	0	 0.00%	1	100.0%	78.00	78.00
C	NO.5	0.00	 0.00%	500.00	100.0%	0	 0.00%	1	100.0%	500.00	500.00
B	NO.6	0.00	 0.00%	288.00	100.0%	0	 0.00%	1	100.0%	288.00	288.00
C	NO.6	0.00	 0.00%	501.00	100.0%	0	 0.00%	1	100.0%	501.00	501.00
B	NO.7	0.00	 0.00%	27488.00	100.0%	0	 0.00%	1	100.0%	27488.00	27488.00
C	NO.7	0.00	 0.00%	502.00	100.0%	0	 0.00%	1	100.0%	502.00	502.00
B	NO.8	0.00	 0.00%	192.00	100.0%	0	 0.00%	1	100.0%	192.00	192.00
C	NO.8	503.00	100.0%	0.00	 0.00%	1	100.0%	0	 0.00%	503.00	503.00
B	NO.9	150.00	100.0%	0.00	 0.00%	1	100.0%	0	 0.00%	150.00	150.00
C	NO.9	504.00	100.0%	0.00	 0.00%	1	100.0%	0	 0.00%	504.00	504.00
*/


处理金额为0的情况，百分比少*100
------解决思路----------------------
SELECT card,team
	,ISNULL(SUM(CASE WHEN 交易状态='成功'THEN 金额 END),0)[金额-成功]
	,LTRIM(CAST(ISNULL(SUM(CASE WHEN 交易状态='成功'THEN 金额 END),0)*100.
		/(CASE WHEN ISNULL(SUM(金额),0)=0 THEN 1 ELSE SUM(金额) END) AS DECIMAL(19,2)))+'%'[占比1]
	,ISNULL(SUM(CASE WHEN 交易状态='未成功'THEN 金额 END),0)[金额-失败]
	,LTRIM(CAST(ISNULL(SUM(CASE WHEN 交易状态='未成功'THEN 金额 END),0)*100.
		/(CASE WHEN ISNULL(SUM(金额),0)=0 THEN 1 ELSE SUM(金额) END) AS DECIMAL(19,2)))+'%'[占比2]
	,ISNULL(SUM(CASE WHEN 交易状态='成功'THEN 1 END),0)[笔数-成功]
	,LTRIM(CAST(ISNULL(SUM(CASE WHEN 交易状态='成功'THEN 1 END),0)*100./ISNULL(SUM(1),1) AS DECIMAL(19,2)))+'%'[占比3]
	,ISNULL(SUM(CASE WHEN 交易状态='未成功'THEN 1 END),0)[笔数-失败]
	,LTRIM(CAST(ISNULL(SUM(CASE WHEN 交易状态='未成功'THEN 1 END),0)*100./ISNULL(SUM(1),1) AS DECIMAL(19,2)))+'%'[占比4]
	,ISNULL(MAX(CASE WHEN 交易状态='成功'THEN 金额 END),0)[MAX]
	,ISNULL(MIN(CASE WHEN 交易状态='成功'THEN 金额 ELSE 0 END),0)[MIX]
FROM TB
GROUP BY card,team
ORDER BY card
处理分母为0
------解决思路----------------------
引用:
要是遇见分母为0的话怎么办呢

用NULLIF()转成NULL
WITH table1(a,b) AS (
    SELECT 20,50 UNION ALL
    SELECT 0,0
)
SELECT STR(a*100.0/NULLIF(b,0),5,1)+'%' p
  FROM table1

p
------
 40.0%
NULL
  相关解决方案

代码迷 - 您身边的软件异常，代码异常，编程异常助手（发现，解决，分享）
Copyright © 2009-2013 DAIMAMI.COM. All rights reserved.