一个表内分类计数的SQL语句问题

[raybvb09]

组名 值一 值二

组一 1 2
组二 3 4
组二 5 6
组三 7 8
组一 9 10
组一 11 12
组二 13 14
组二 15 16
组三 17 18
组一 19 20


给定一个数值范围，求各分组的各个值在此范围中的计数
如给定范围7－15，结果应为：

组名 值一计数 值二计数
组一 2 2
组二 2 1
组三 1 1

求教此SQL语句，谢谢

[liangCK]

SELECT
    组名,
    COUNT(CASE WHEN 值一 BETWEEN 7 AND 15 THEN 1 END) AS 值一计数,
    COUNT(CASE WHEN 值二 BETWEEN 7 AND 15 THEN 1 END) AS 值二计数
FROM tb
GROUP BY 组名

[Yang_]

select 组名
,sum(case when 值一 between 7 and 15 then 1 else 0 end) as 值一计数
,sum(case when 值二 between 7 and 15 then 1 else 0 end) as 值二计数
from tablename
where 值一 between 7 and 15 
or 值二 between 7 and 15
group by 组名

[水族杰纶]

select 组名,count(值一)值一计数,count(值二计数)值二计数 from tb where 值一 between 7 and 15 group by 
组名

[liangCK]

---------------------------------
--  Author: liangCK 小梁
---------------------------------
 
--> 生成测试数据: @T
DECLARE @T TABLE (组名 VARCHAR(4),值一 INT,值二 INT)
INSERT INTO @T
SELECT '组一',1,2 UNION ALL
SELECT '组二',3,4 UNION ALL
SELECT '组二',5,6 UNION ALL
SELECT '组三',7,8 UNION ALL
SELECT '组一',9,10 UNION ALL
SELECT '组一',11,12 UNION ALL
SELECT '组二',13,14 UNION ALL
SELECT '组二',15,16 UNION ALL
SELECT '组三',17,18 UNION ALL
SELECT '组一',19,20

--SQL查询如下:

SELECT
    组名,
    COUNT(CASE WHEN 值一 BETWEEN 7 AND 15 THEN 1 END) AS 值一计数,
    COUNT(CASE WHEN 值二 BETWEEN 7 AND 15 THEN 1 END) AS 值二计数
FROM @T
GROUP BY 组名

/*
组名   值一计数        值二计数
---- ----------- -----------
组二   2           1
组三   1           1
组一   2           2

(3 行受影响)
*/

[百年树人]

---测试数据---
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([组名] varchar(4),[值一] int,[值二] int)
insert [tb]
select '组一',1,2 union all
select '组二',3,4 union all
select '组二',5,6 union all
select '组三',7,8 union all
select '组一',9,10 union all
select '组一',11,12 union all
select '组二',13,14 union all
select '组二',15,16 union all
select '组三',17,18 union all
select '组一',19,20
 
---查询---
select 
  [组名],
  sum(case when [值一] between 7 and  15 then 1 else 0 end) as [值一计数],
  sum(case when [值二] between 7 and  15 then 1 else 0 end) as [值二计数]
from [tb]
group by [组名]
order by [组名]

---结果---
组名   值一计数        值二计数        
---- ----------- ----------- 
组二   2           1
组三   1           1
组一   2           2

（所影响的行数为 3 行）

[ChinaJiaBing]

declare @组名 table (组名 nvarchar(10),值一 int,值二 int)
insert into @组名 select '组一',1,2
        union all select '组二',3,4
        union all select '组二',5,6
        union all select '组三',7,8
        union all select '组一',9,10
        union all select '组一',11,12
        union all select '组二',13,14
        union all select '组二',15,16
        union all select '组三',17,18
        union all select '组一',19,20
 select a.组名,a.值一,b.值二 from
(select 组名,COUNT(*) as 值一 from @组名 where 值一 between 7 and 15
 group by 组名) a,

(select 组名,COUNT(*) 值二 from @组名  where 值二 between 7 and 15
group by 组名) b where a.组名=b.组名
order by b.值二 desc
(10 行受影响)
组名         值一          值二
---------- ----------- -----------
组一         2           2
组二         2           1
组三         1           1

(3 行受影响)

[raybvb09]

谢谢大家，又学到东西了