比例统计

[czyoooo]

表
ID NAME EAT1 EAT2
1 A 苹果 桔子
2 B 苹果 桔子
3 A 梨子 苹果
4 A 梨子 香蕉
5 A 杨桃 火龙果
6 B 火龙果 桔子
查询：吃苹果的比例
A 2/4
B 1/2

[唐诗三百首]

create table #t(ID int,NAME varchar(10),EAT1 varchar(10),EAT2 varchar(10))

insert into #t(ID,NAME,EAT1,EAT2)
 select 1,'A','苹果','桔子' union all
 select 2,'B','苹果','桔子' union all
 select 3,'A',' 梨子','苹果' union all
 select 4,'A','梨子','香蕉' union all
 select 5,'A','杨桃','火龙果' union all
 select 6,'B','火龙果','桔子'


select a.NAME,
       吃苹果的比例=rtrim(sum(case when EAT1='苹果' or EAT2='苹果' then 1 else 0 end))+'/'+rtrim(count(1))
 from #t a
 group by a.NAME

/*
NAME       吃苹果的比例
---------- -------------------------
A          2/4
B          1/2

(2 row(s) affected)
*/

[czyoooo]

如果 吃苹果次数要求大于2次的呢？

[唐诗三百首]

引用 2 楼 czyoooo 的回复:

如果 吃苹果次数要求大于2次的呢？

select a.NAME,
       吃苹果的比例=rtrim(sum(case when EAT1='苹果' or EAT2='苹果' then 1 else 0 end))+'/'+rtrim(count(1))
 from #t a
 group by a.NAME
 having sum(case when EAT1='苹果' or EAT2='苹果' then 1 else 0 end)>2

[二月十六]

语句：

--测试数据
;WITH tab(ID,NAME,EAT1,EAT2) AS (
 select 1,'A','苹果','桔子' union all
 select 2,'B','苹果','桔子' union all
 select 3,'A','梨子','苹果' union all
 select 4,'A','梨子','香蕉' union all
 select 5,'A','杨桃','火龙果' union all
 select 6,'B','火龙果','桔子'
)
--测试数据结束
SELECT  a.NAME ,
        CONVERT(NVARCHAR(100), ( SELECT COUNT(1)
                                 FROM   tab b
                                 WHERE  a.NAME = b.NAME
                                        AND ( b.EAT1 = '苹果'
                                              OR b.EAT2 = '苹果'
                                            )
                                 GROUP BY b.NAME
                               )) + '/' + CONVERT(NVARCHAR(100), ( SELECT
                                                              COUNT(1)
                                                              FROM
                                                              tab b
                                                              WHERE
                                                              a.NAME = b.NAME
                                                              GROUP BY b.NAME
                                                              )) AS 吃苹果的比例
FROM    tab a
GROUP BY a.NAME;

结果：

[二月十六]

大于2的

--测试数据
;WITH tab(ID,NAME,EAT1,EAT2) AS (
 select 1,'A','苹果','桔子' union all
 select 2,'B','苹果','桔子' union all
 select 3,'A','梨子','苹果' union all
 select 4,'A','梨子','香蕉' union all
 select 5,'A','杨桃','火龙果' union all
 select 6,'B','火龙果','桔子'
)
--测试数据结束
SELECT  a.NAME ,
        CONVERT(NVARCHAR(100), ( SELECT COUNT(1)
                                 FROM   tab b
                                 WHERE  a.NAME = b.NAME
                                        AND ( b.EAT1 = '苹果'
                                              OR b.EAT2 = '苹果'
                                            )
                                 GROUP BY b.NAME
                               )) + '/' + CONVERT(NVARCHAR(100), ( SELECT
                                                              COUNT(1)
                                                              FROM
                                                              tab b
                                                              WHERE
                                                              a.NAME = b.NAME
                                                              GROUP BY b.NAME
                                                              )) AS 吃苹果的比例
FROM    tab a
WHERE ( SELECT COUNT(1)
                                 FROM   tab b
                                 WHERE  a.NAME = b.NAME
                                        AND ( b.EAT1 = '苹果'
                                              OR b.EAT2 = '苹果'
                                            )
                                 GROUP BY b.NAME
                               )>2
GROUP BY a.NAME;

[中国风]

引用 2 楼 czyoooo 的回复:

如果 吃苹果次数要求大于2次的呢？

>=2次 e.g.

use Tempdb
go
--> --> 中国风(Roy)生成測試數據
 
if not object_id(N'Tempdb..#1') is null
	drop table #1
Go
Create table #1([ID] int,[NAME] nvarchar(21),[EAT1] nvarchar(23),[EAT2] nvarchar(23))
Insert #1
select 1,N'A',N'苹果',N'桔子' union all
select 2,N'B',N'苹果',N'桔子' union all
select 3,N'A',N'梨子',N'苹果' union all
select 4,N'A',N'梨子',N'香蕉' union all
select 5,N'A',N'杨桃',N'火龙果' union all
select 6,N'B',N'火龙果',N'桔子'
Go
SELECT  [NAME] ,
       RTRIM(SUM(CASE WHEN N'苹果' IN ([EAT1],[EAT2])   THEN 1
                       ELSE 0
                  END ))+'/' +RTRIM(COUNT(*)) AS Count1
FROM    #1
GROUP BY [NAME]
HAVING SUM(CASE WHEN N'苹果' IN ([EAT1],[EAT2])   THEN 1
                       ELSE 0
                  END )>=2;

/*
NAME	Count1
A	2/4
*/

[中国风]

HAVING SUM(CASE WHEN N'苹果' IN ([EAT1],[EAT2]) THEN 1 ELSE 0 END )>2; ---在HAVING限制结果集条件

[wwfxgm]

都是高人。这么复杂的代码。