22,209
社区成员
发帖
与我相关
我的任务
分享
USE tempdb
GO
IF OBJECT_ID('x') IS NOT NULL DROP TABLE x
GO
CREATE TABLE x(
id INT,
att VARCHAR(50)
)
INSERT INTO x
SELECT 1,'a'
union all SELECT 1,'b'
union all select 1,'c'
union all SELECT 1,'d'
union all SELECT 1,'a'
union all SELECT 1,'c'
union all SELECT 2,'bd'
union all SELECT 2,'cd'
union all SELECT 2,'b'
union all SELECT 2,'c'
union all select 2,'e'
union all SELECT 2,'f'
union all SELECT 2,'c'
union all SELECT 3,'bd'
union all SELECT 3,'a'
union all SELECT 3,'c'
union all SELECT 3,'d'
union all SELECT 3,'v'
union all SELECT 3,'a'
union all SELECT 3,'cf'
GO
--创建聚集索引, 提高效率
CREATE CLUSTERED INDEX ix_x_att ON x(att)
--查询
SELECT
--count(case when x.att like 'a%' then 1 end) a, --你原来的写法
--count(case when x.att = 'c' then 1 end) c, --你原来的写法
count(DISTINCT case when x.att like 'a%' then id end) AS a_id_distinct,
count(DISTINCT case when x.att = 'c' then id end) AS c_id_distinct
from x;
/*
a_id_distinct c_id_distinct
2 3
*/