表a
no1 name class score
1 A 初一1 60
2 B 初二2 70
3 C 初一2 30
4 D 初二1 80
5 E 初一1 45
统计各年级score>=60人数
统计出
初一 1
初二 2
请问这条语句如何写??谢谢
no1 name class score
1 A 初一1 60
2 B 初二2 70
3 C 初一2 30
4 D 初二1 80
5 E 初一1 45
统计各年级score>=60人数
统计出
初一 1
初二 2
请问这条语句如何写??谢谢
(
nol int identity(1,1) primary key,
name varchar(20),
class varchar(20),
score int
)
insert into #TT1 select 'A','初一1',60
insert into #TT1 select 'B','初二2',70
insert into #TT1 select 'C','初一2',30
insert into #TT1 select 'D','初二1',80
insert into #TT1 select 'E','初一1',45select count(substring(class,1,2)),substring(class,1,2)
from
(
select * from #TT1 where score>=60
) tt
group by substring(class,1,2)
order by count(substring(class,1,2)) asc
----------- ----
1 初一
2 初二(2 行受影响)
-- Author :SQL77(只为思齐老)
-- Date :2010-02-24 17:31:48
-- Version:
-- Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
-- Aug 6 2000 00:57:48
-- Copyright (c) 1988-2000 Microsoft Corporation
-- Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
----------------------------------------------------------------
--> 测试数据:#TB
if object_id('tempdb.dbo.#TB') is not null drop table #TB
go
create table #TB([no1] int,[name] varchar(1),[class] varchar(5),[score] int)
insert #TB
select 1,'A','初一1',60 union all
select 2,'B','初二2',70 union all
select 3,'C','初一2',30 union all
select 4,'D','初二1',80 union all
select 5,'E','初一1',45
--------------开始查询--------------------------select LEFT(CLASS,4),COUNT(*) from #TB WHERE score>60 GROUP BY LEFT(CLASS,4)
----------------结果----------------------------
/* (所影响的行数为 5 行)
----- -----------
初二1 1
初二2 1(所影响的行数为 2 行)
*/
insert into @t select 'A','初一1',60
insert into @t select 'B','初二2',70
insert into @t select 'C','初一2',30
insert into @t select 'D','初二1',80
insert into @t select 'E','初一1',45select * from @tselect left(class,2) as class,count(no1) as 人数
from @t
where score>=60
group by left(class,2)
order by 1 desc
/*
no1 name class score
----------- -------------------- -------------------- -----------
1 A 初一1 60
2 B 初二2 70
3 C 初一2 30
4 D 初二1 80
5 E 初一1 45(5 行受影响)class 人数
----- -----------
初一 1
初二 2(2 行受影响)*/
class 人数
初一 1
初二 2
初三 0
请问应该如何改进呢??
drop table tb
Go
Create table tb([no1] int,[name] nvarchar(1),[class] nvarchar(3),[score] int)
Insert tb
select 1,N'A',N'初一1',60 union all
select 2,N'B',N'初二2',70 union all
select 3,N'C',N'初一2',30 union all
select 4,N'D',N'初二1',80 union all
select 5,N'E',N'初一1',45
Go
select a.[class],
count(b.[class])人数
from (select N'初一'[class] Union select N'初二' union select N'初三')a
left join tb b on left(b.[class],2)=a.[class]
and [score]>=60
group by a.[class]
/*
class 人数
----- -----------
初一 1
初二 2
初三 0
警告: 彙總或其他 SET 作業已刪除 Null 值。(3 個資料列受到影響)
*/
if object_id('[a]') is not null drop table [a]
go
create table [a]([no1] int,[name] varchar(1),[class] varchar(5),[score] int)
insert [a]
select 1,'A','初一1',60 union all
select 2,'B','初二2',70 union all
select 3,'C','初一2',30 union all
select 4,'D','初二1',80 union all
select 5,'E','初一1',45
---查询---
select a.class,isnull(b.cnt,0) as 人数
from (select '初一' as class union select '初二' union select '初三') a
left join
(select left(class,2) as class,count(1) as cnt from a where score>=60 group by left(class,2)) b
on a.class=b.class
order by charindex(a.class,'初一初二初三')---结果---
class 人数
----- -----------
初一 1
初二 2
初三 0(所影响的行数为 3 行)