---------------------------------------------------------------- -- Author :DBA_HuangZJ(發糞塗牆) -- Date :2014-08-29 17:04:50 -- Version: -- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([name] varchar(4),[score] varchar(3)) insert [huang] select '张三','5分' union all select '张三','4分' union all select '张三','5分' union ALL select '李四','3分' union all select '李四','5分' union all select '李四','5分' union all select '李四','4分' --------------开始查询--------------------------select [name],COUNT(CASE WHEN [score]='5分' THEN 1 ELSE NULL END )[5分个数],COUNT(CASE WHEN [score]<>'5分' THEN 1 ELSE NULL END)[非5分个数] from [huang] GROUP BY [name] ----------------结果---------------------------- /* name 5分个数 非5分个数 ---- ----------- ----------- 李四 2 2 张三 2 1 */
还是这样?---------------------------------------------------------------- -- Author :DBA_HuangZJ(發糞塗牆) -- Date :2014-08-29 17:04:50 -- Version: -- Microsoft SQL Server 2012 - 11.0.5058.0 (X64) -- May 14 2014 18:34:29 -- Copyright (c) Microsoft Corporation -- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor) -- ---------------------------------------------------------------- --> 测试数据:[huang] if object_id('[huang]') is not null drop table [huang] go create table [huang]([name] varchar(4),[score] varchar(3)) insert [huang] select '张三','5分' union all select '张三','4分' union all select '张三','5分' union ALL select '李四','3分' union all select '李四','5分' union all select '李四','5分' union all select '李四','4分' --------------开始查询--------------------------select [name]+'同学本学期共得5分'+CAST(COUNT(CASE WHEN [score]='5分' THEN 1 ELSE NULL END ) AS VARCHAR(2))+'个,其他'+CAST(COUNT(CASE WHEN [score]<>'5分' THEN 1 ELSE NULL END)AS VARCHAR(2))+'个' AS [输出] from [huang] GROUP BY [name] ----------------结果---------------------------- /* 输出 ----------------------------------- 李四同学本学期共得5分2个,其他2个 张三同学本学期共得5分2个,其他1个 */
use Tempdb go --> -->
if not object_id(N'Tempdb..#T') is null drop table #T Go Create table #T([name] nvarchar(2),[score] nvarchar(2)) Insert #T select N'张三',N'5分' union all select N'张三',N'4分' union all select N'张三',N'5分' union all select N'李四',N'3分' union all select N'李四',N'5分' union all select N'李四',N'5分' union all select N'李四',N'4分' union ALL select N'王五',N'4分' --加一条王五 Go SELECT [name]+N'同学本学期共得5分'+RTRIM(SUM(CASE WHEN [score]=N'5分' THEN 1 ELSE 0 END))+N'个,其他'+RTRIM(SUM(CASE WHEN [score]!=N'5分' THEN 1 ELSE 0 END))+N'个' AS Display FROM #T AS a GROUP BY [name] /* 李四同学本学期共得5分2个,其他2个 王五同学本学期共得5分0个,其他1个 张三同学本学期共得5分2个,其他1个 */
借用楼上的数据SELECT DISTINCT *,COUNT(1) OVER (PARTITION BY name,score) AS num FROM huang
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-29 17:04:50
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([name] varchar(4),[score] varchar(3))
insert [huang]
select '张三','5分' union all
select '张三','4分' union all
select '张三','5分' union ALL
select '李四','3分' union all
select '李四','5分' union all
select '李四','5分' union all
select '李四','4分'
--------------开始查询--------------------------select [name],COUNT(CASE WHEN [score]='5分' THEN 1 ELSE NULL END )[5分个数],COUNT(CASE WHEN [score]<>'5分' THEN 1 ELSE NULL END)[非5分个数]
from [huang]
GROUP BY [name]
----------------结果----------------------------
/*
name 5分个数 非5分个数
---- ----------- -----------
李四 2 2
张三 2 1
*/
-- Author :DBA_HuangZJ(發糞塗牆)
-- Date :2014-08-29 17:04:50
-- Version:
-- Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
-- May 14 2014 18:34:29
-- Copyright (c) Microsoft Corporation
-- Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)
--
----------------------------------------------------------------
--> 测试数据:[huang]
if object_id('[huang]') is not null drop table [huang]
go
create table [huang]([name] varchar(4),[score] varchar(3))
insert [huang]
select '张三','5分' union all
select '张三','4分' union all
select '张三','5分' union ALL
select '李四','3分' union all
select '李四','5分' union all
select '李四','5分' union all
select '李四','4分'
--------------开始查询--------------------------select [name]+'同学本学期共得5分'+CAST(COUNT(CASE WHEN [score]='5分' THEN 1 ELSE NULL END ) AS VARCHAR(2))+'个,其他'+CAST(COUNT(CASE WHEN [score]<>'5分' THEN 1 ELSE NULL END)AS VARCHAR(2))+'个' AS [输出]
from [huang]
GROUP BY [name]
----------------结果----------------------------
/*
输出
-----------------------------------
李四同学本学期共得5分2个,其他2个
张三同学本学期共得5分2个,其他1个
*/
go
--> -->
if not object_id(N'Tempdb..#T') is null
drop table #T
Go
Create table #T([name] nvarchar(2),[score] nvarchar(2))
Insert #T
select N'张三',N'5分' union all
select N'张三',N'4分' union all
select N'张三',N'5分' union all
select N'李四',N'3分' union all
select N'李四',N'5分' union all
select N'李四',N'5分' union all
select N'李四',N'4分' union ALL
select N'王五',N'4分' --加一条王五
Go
SELECT [name]+N'同学本学期共得5分'+RTRIM(SUM(CASE WHEN [score]=N'5分' THEN 1 ELSE 0 END))+N'个,其他'+RTRIM(SUM(CASE WHEN [score]!=N'5分' THEN 1 ELSE 0 END))+N'个' AS Display
FROM #T AS a
GROUP BY [name]
/*
李四同学本学期共得5分2个,其他2个
王五同学本学期共得5分0个,其他1个
张三同学本学期共得5分2个,其他1个
*/
在关键字 'over' 附近有语法错误。