创建一张表,并且想表中插入中文和时间
CREATE
TABLE
[
T_Scores
]
(
[ Date ] [ datetime ] NULL ,
[ Name ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Score ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
);
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF200000000 AS DateTime ), N ' 拜仁 ' , N ' 胜 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF300000000 AS DateTime ), N ' 奇才 ' , N ' 胜 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF300000000 AS DateTime ), N ' 湖人 ' , N ' 胜 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF400000000 AS DateTime ), N ' 拜仁 ' , N ' 负 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF200000000 AS DateTime ), N ' 拜仁 ' , N ' 负 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF600000000 AS DateTime ), N ' 奇才 ' , N ' 胜 ' );
[ Date ] [ datetime ] NULL ,
[ Name ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Score ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
);
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF200000000 AS DateTime ), N ' 拜仁 ' , N ' 胜 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF300000000 AS DateTime ), N ' 奇才 ' , N ' 胜 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF300000000 AS DateTime ), N ' 湖人 ' , N ' 胜 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF400000000 AS DateTime ), N ' 拜仁 ' , N ' 负 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF200000000 AS DateTime ), N ' 拜仁 ' , N ' 负 ' );
INSERT [ T_Scores ] ( [ Date ] , [ Name ] , [ Score ] ) VALUES ( CAST ( 0x00009AF600000000 AS DateTime ), N ' 奇才 ' , N ' 胜 ' );
查询结果如图:
现在想实现如图的现实效果:
首先先建立一个返回分手的函数
create
function
num
( @name nvarchar ( 50 ), @win nvarchar ( 50 ))
returns int
as
begin
declare @num int
select @num = count (Score) from T_Scores where name = @name and Score = @win
return @num
end
( @name nvarchar ( 50 ), @win nvarchar ( 50 ))
returns int
as
begin
declare @num int
select @num = count (Score) from T_Scores where name = @name and Score = @win
return @num
end
再在Sql查询中调用
select
distinct
(name),dbo.num(name,
'
胜
'
)
as
'
胜
'
,dbo.num(name,
'
负
'
)
as
'
负
'
from
T_Scores
显示效果如图: