求SQL语句

needanothercoder 2018-03-20 09:51:11
求SQL语句
已有两个表名称分别为“票”和“项目分值”内容如下
想要查询出第三个表“统计结果”（“统计结果”表里面只有两列：“姓名”和“总分”）
记分规则：如果“项目分值”中的得分是A，就取“分值列表”中的第一个数字，如果是B就取第二个数字，C取第三个数字。

表名：票
项目 分值列表
思想品德 15,10,-15
贡献与失误 15,10,-15
任务完成 15,13,10
出勤率 10,8,4
表达能力 8,6,4
团结协作 9,7,5
业务能力 8,6,4
工作态度 5,4,2
效率和质量 5,4,2
本人作用 5,4,2

表名：项目分值
姓名 项目 得分
安丰顺 思想品德 A
安丰顺 贡献与失误 B
安丰顺 任务完成 C
安丰顺 出勤率 C
安丰顺 表达能力 A
安丰顺 团结协作 A
安丰顺 业务能力 B
安丰顺 工作态度 C
安丰顺 效率和质量 C
安丰顺 本人作用 A
卞树明 思想品德 A
卞树明 贡献与失误 A
卞树明 任务完成 A
卞树明 出勤率 C
卞树明 表达能力 A
卞树明 团结协作 B
卞树明 业务能力 B
卞树明 工作态度 C
卞树明 效率和质量 A
卞树明 本人作用 C
...全文
1212 5 打赏 收藏 转发到动态 举报
写回复
用AI写文章
5 条回复
切换为时间正序
请发表友善的回复…
发表回复
rings2000 2018-04-10
  • 打赏
  • 举报
 回复
use Tempdb go --> --> 听雨停了-->生成测试数据 if not object_id(N'Tempdb..#tabA') is null drop table #tabA Go Create table #tabA([项目] nvarchar(25),[分值列表] nvarchar(29)) Insert #tabA select N'思想品德',N'15,10,-15' union all select N'贡献与失误',N'15,10,-15' union all select N'任务完成',N'15,13,10' union all select N'出勤率',N'10,8,4' union all select N'表达能力',N'8,6,4' union all select N'团结协作',N'9,7,5' union all select N'业务能力',N'8,6,4' union all select N'工作态度',N'5,4,2' union all select N'效率和质量',N'5,4,2' union all select N'本人作用',N'5,4,2' Go Select * from #tabA if not object_id(N'Tempdb..#tabB') is null drop table #tabB Go Create table #tabB([姓名] nvarchar(23),[项目] nvarchar(25),[得分] nvarchar(21)) Insert #tabB select N'安丰顺',N'思想品德',N'A' union all select N'安丰顺',N'贡献与失误',N'B' union all select N'安丰顺',N'任务完成',N'C' union all select N'安丰顺',N'出勤率',N'C' union all select N'安丰顺',N'表达能力',N'A' union all select N'安丰顺',N'团结协作',N'A' union all select N'安丰顺',N'业务能力',N'B' union all select N'安丰顺',N'工作态度',N'C' union all select N'安丰顺',N'效率和质量',N'C' union all select N'安丰顺',N'本人作用',N'A' union all select N'卞树明',N'思想品德',N'A' union all select N'卞树明',N'贡献与失误',N'A' union all select N'卞树明',N'任务完成',N'A' union all select N'卞树明',N'出勤率',N'C' union all select N'卞树明',N'表达能力',N'A' union all select N'卞树明',N'团结协作',N'B' union all select N'卞树明',N'业务能力',N'B' union all select N'卞树明',N'工作态度',N'C' union all select N'卞树明',N'效率和质量',N'A' union all select N'卞树明',N'本人作用',N'C' Go Select * from #tabB --测试数据结束 --创建字符串分割函数 CREATE FUNCTION [dbo].[f_splitSTR]( @s varchar(8000), --待分拆的字符串 @split varchar(10) --数据分隔符 )RETURNS @re TABLE(col varchar(100)) AS BEGIN DECLARE @splitlen int SET @splitlen=LEN(@split+'a')-2 WHILE CHARINDEX(@split,@s)>0 BEGIN INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1)) SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'') END INSERT @re VALUES(@s) RETURN END --结果 ;WITH cte AS ( SELECT 项目, col, CASE ROW_NUMBER() OVER(PARTITION BY 项目 ORDER BY 分值列表) WHEN 1 THEN 'A' WHEN 2 THEN 'B' WHEN 3 THEN 'C' END AS rn FROM #taba a OUTER APPLY ecs.dbo.f_splitSTR( a.分值列表,',') ) SELECT b.姓名,sum(cast(a.col as int)) as 总分 FROM cte AS a INNER JOIN #tabB AS b ON a.项目=b.项目 and a.rn=b.得分 GROUP BY b.姓名
听雨停了 2018-03-20
  • 打赏
  • 举报
 回复 


use Tempdb

go

--> --> 听雨停了-->生成测试数据

 

if not object_id(N'Tempdb..#tabA') is null

	drop table #tabA

Go

Create table #tabA([项目] nvarchar(25),[分值列表] nvarchar(29))

Insert #tabA

select N'思想品德',N'15,10,-15' union all

select N'贡献与失误',N'15,10,-15' union all

select N'任务完成',N'15,13,10' union all

select N'出勤率',N'10,8,4' union all

select N'表达能力',N'8,6,4' union all

select N'团结协作',N'9,7,5' union all

select N'业务能力',N'8,6,4' union all

select N'工作态度',N'5,4,2' union all

select N'效率和质量',N'5,4,2' union all

select N'本人作用',N'5,4,2'

Go

Select * from #tabA

if not object_id(N'Tempdb..#tabB') is null

	drop table #tabB

Go

Create table #tabB([姓名] nvarchar(23),[项目] nvarchar(25),[得分] nvarchar(21))

Insert #tabB

select N'安丰顺',N'思想品德',N'A' union all

select N'安丰顺',N'贡献与失误',N'B' union all

select N'安丰顺',N'任务完成',N'C' union all

select N'安丰顺',N'出勤率',N'C' union all

select N'安丰顺',N'表达能力',N'A' union all

select N'安丰顺',N'团结协作',N'A' union all

select N'安丰顺',N'业务能力',N'B' union all

select N'安丰顺',N'工作态度',N'C' union all

select N'安丰顺',N'效率和质量',N'C' union all

select N'安丰顺',N'本人作用',N'A' union all

select N'卞树明',N'思想品德',N'A' union all

select N'卞树明',N'贡献与失误',N'A' union all

select N'卞树明',N'任务完成',N'A' union all

select N'卞树明',N'出勤率',N'C' union all

select N'卞树明',N'表达能力',N'A' union all

select N'卞树明',N'团结协作',N'B' union all

select N'卞树明',N'业务能力',N'B' union all

select N'卞树明',N'工作态度',N'C' union all

select N'卞树明',N'效率和质量',N'A' union all

select N'卞树明',N'本人作用',N'C'

Go

Select * from #tabB

--测试数据结束



--创建字符串分割函数

CREATE FUNCTION [dbo].[f_splitSTR](

@s   varchar(8000),   --待分拆的字符串

@split varchar(10)     --数据分隔符

)RETURNS @re TABLE(col varchar(100))

AS

BEGIN

 DECLARE @splitlen int

 SET @splitlen=LEN(@split+'a')-2

 WHILE CHARINDEX(@split,@s)>0

 BEGIN

  INSERT @re VALUES(LEFT(@s,CHARINDEX(@split,@s)-1))

  SET @s=STUFF(@s,1,CHARINDEX(@split,@s)+@splitlen,'')

 END

 INSERT @re VALUES(@s)

 RETURN

END

--结果

;WITH cte AS (

SELECT 项目,

       col,

       CASE ROW_NUMBER() OVER(PARTITION BY 项目 ORDER BY 分值列表)

            WHEN 1 THEN 'A'

            WHEN 2 THEN 'B'

            WHEN 3 THEN 'C'

       END AS rn

FROM   #taba a

       OUTER APPLY ecs.dbo.f_splitSTR(

    a.分值列表,',')

)

SELECT b.姓名,sum(cast(a.col as int)) as 总分 FROM cte AS a

INNER JOIN #tabB AS b ON a.项目=b.项目 and a.rn=b.得分

GROUP BY b.姓名

吉普赛的歌 版主 2018-03-20
  • 打赏
  • 举报
 回复 
USE tempdb
GO
IF OBJECT_ID('ticket') IS NOT NULL DROP TABLE ticket
IF OBJECT_ID('score') IS NOT NULL DROP TABLE score
GO
CREATE TABLE ticket(project NVARCHAR(20),valueList VARCHAR(20))
CREATE TABLE score([name] NVARCHAR(10),project NVARCHAR(20), scoreNo VARCHAR(5))
GO
INSERT INTO ticket(	project,valueList)
SELECT '思想品德','15,10,-15'
UNION ALL SELECT '贡献与失误','15,10,-15'
UNION ALL SELECT '任务完成','15,13,10'
UNION ALL SELECT '出勤率','10,8,4'
UNION ALL SELECT '表达能力','8,6,4' 
UNION ALL SELECT '团结协作','9,7,5' 
UNION ALL SELECT '业务能力','8,6,4' 
UNION ALL SELECT '工作态度','5,4,2' 
UNION ALL SELECT '效率和质量','5,4,2' 
UNION ALL SELECT '本人作用','5,4,2' 

INSERT INTO score(name,project,scoreNo)
SELECT '安丰顺','思想品德','A' 
UNION ALL SELECT '安丰顺','贡献与失误','B' 
UNION ALL SELECT '安丰顺','任务完成','C'
UNION ALL SELECT '安丰顺','出勤率','C' 
UNION ALL SELECT '安丰顺','表达能力','A' 
UNION ALL SELECT '安丰顺','团结协作','A'
UNION ALL SELECT '安丰顺','业务能力','B' 
UNION ALL SELECT '安丰顺','工作态度','C' 
UNION ALL SELECT '安丰顺','效率和质量','C' 
UNION ALL SELECT '安丰顺','本人作用','A' 
UNION ALL SELECT '卞树明','思想品德','A' 
UNION ALL SELECT '卞树明','贡献与失误','A' 
UNION ALL SELECT '卞树明','任务完成','A' 
UNION ALL SELECT '卞树明','出勤率','C' 
UNION ALL SELECT '卞树明','表达能力','A' 
UNION ALL SELECT '卞树明','团结协作','B' 
UNION ALL SELECT '卞树明','业务能力','B' 
UNION ALL SELECT '卞树明','工作态度','C'
UNION ALL SELECT '卞树明','效率和质量','A' 
UNION ALL SELECT '卞树明','本人作用','C' 

SELECT s.name AS [姓名],
SUM(
	CAST(
		PARSENAME(REPLACE('15,10,-15',',','.'),
			CASE WHEN s.scoreNo='C' THEN 1
				WHEN s.scoreNo='B' THEN 2
				WHEN s.scoreNo='A' THEN 3
			END
	)
	AS INT)
) AS [总分]
FROM score s INNER JOIN ticket t ON s.project=t.project
GROUP BY s.name
/*
姓名	总分
安丰顺	20
卞树明	50
*/
吉普赛的歌 版主 2018-03-20
  • 打赏
  • 举报
 回复
上面的 #2 忘记了替换, 弄错了个地方， 按下面的： 
USE tempdb
GO
IF OBJECT_ID('ticket') IS NOT NULL DROP TABLE ticket
IF OBJECT_ID('score') IS NOT NULL DROP TABLE score
GO
CREATE TABLE ticket(project NVARCHAR(20),valueList VARCHAR(20))
CREATE TABLE score([name] NVARCHAR(10),project NVARCHAR(20), scoreNo VARCHAR(5))
GO
INSERT INTO ticket(	project,valueList)
SELECT '思想品德','15,10,-15'
UNION ALL SELECT '贡献与失误','15,10,-15'
UNION ALL SELECT '任务完成','15,13,10'
UNION ALL SELECT '出勤率','10,8,4'
UNION ALL SELECT '表达能力','8,6,4' 
UNION ALL SELECT '团结协作','9,7,5' 
UNION ALL SELECT '业务能力','8,6,4' 
UNION ALL SELECT '工作态度','5,4,2' 
UNION ALL SELECT '效率和质量','5,4,2' 
UNION ALL SELECT '本人作用','5,4,2' 

INSERT INTO score(name,project,scoreNo)
SELECT '安丰顺','思想品德','A' 
UNION ALL SELECT '安丰顺','贡献与失误','B' 
UNION ALL SELECT '安丰顺','任务完成','C'
UNION ALL SELECT '安丰顺','出勤率','C' 
UNION ALL SELECT '安丰顺','表达能力','A' 
UNION ALL SELECT '安丰顺','团结协作','A'
UNION ALL SELECT '安丰顺','业务能力','B' 
UNION ALL SELECT '安丰顺','工作态度','C' 
UNION ALL SELECT '安丰顺','效率和质量','C' 
UNION ALL SELECT '安丰顺','本人作用','A' 
UNION ALL SELECT '卞树明','思想品德','A' 
UNION ALL SELECT '卞树明','贡献与失误','A' 
UNION ALL SELECT '卞树明','任务完成','A' 
UNION ALL SELECT '卞树明','出勤率','C' 
UNION ALL SELECT '卞树明','表达能力','A' 
UNION ALL SELECT '卞树明','团结协作','B' 
UNION ALL SELECT '卞树明','业务能力','B' 
UNION ALL SELECT '卞树明','工作态度','C'
UNION ALL SELECT '卞树明','效率和质量','A' 
UNION ALL SELECT '卞树明','本人作用','C' 

SELECT s.name AS [姓名],
SUM(
	CAST(
		PARSENAME(REPLACE(t.valueList,',','.'),
			CASE WHEN s.scoreNo='C' THEN 1
				WHEN s.scoreNo='B' THEN 2
				WHEN s.scoreNo='A' THEN 3
			END
	)
	AS INT)
) AS [总分]
FROM score s INNER JOIN ticket t ON s.project=t.project
GROUP BY s.name
/*
姓名	总分
安丰顺	71
卞树明	79
*/

SELECT s.name AS [姓名],
	s.project AS [项目],
	CAST(
		PARSENAME(REPLACE(t.valueList,',','.'),
			CASE WHEN s.scoreNo='C' THEN 1
				WHEN s.scoreNo='B' THEN 2
				WHEN s.scoreNo='A' THEN 3
			END
	)
	AS INT) AS [单科分数]
FROM score s INNER JOIN ticket t ON s.project=t.project
ORDER BY s.name
/*
姓名	项目	单科分数
安丰顺	思想品德	15
安丰顺	贡献与失误	10
安丰顺	任务完成	10
安丰顺	出勤率	4
安丰顺	表达能力	8
安丰顺	团结协作	9
安丰顺	业务能力	6
安丰顺	工作态度	2
安丰顺	效率和质量	2
安丰顺	本人作用	5
卞树明	思想品德	15
卞树明	贡献与失误	15
卞树明	任务完成	15
卞树明	出勤率	4
卞树明	表达能力	8
卞树明	团结协作	7
卞树明	业务能力	6
卞树明	工作态度	2
卞树明	效率和质量	5
卞树明	本人作用	2
*/
二月十六 版主 2018-03-20
  • 打赏
  • 举报
 回复
创建函数：
ALTER FUNCTION dbo.F_Split

 (

     @SplitString nvarchar(max),  --源字符串

     @Separator nvarchar(10)=' '  --分隔符号，默认为空格

 )

 RETURNS @SplitStringsTable TABLE  --输出的数据表

 (

     [id] int identity(1,1),

     [value] nvarchar(max)

 )

 AS

 BEGIN

     DECLARE @CurrentIndex int;

     DECLARE @NextIndex int;

     DECLARE @ReturnText nvarchar(max);



     SELECT @CurrentIndex=1;

     WHILE(@CurrentIndex<=len(@SplitString))

         BEGIN

             SELECT @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);

             IF(@NextIndex=0 OR @NextIndex IS NULL)

                 SELECT @NextIndex=len(@SplitString)+1;

                 SELECT @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);

                 INSERT INTO @SplitStringsTable([value]) VALUES(@ReturnText);

                 SELECT @CurrentIndex=@NextIndex+1;

             END

     RETURN;

 END

GO




--测试数据

if not object_id(N'Tempdb..#Bill') is null

	drop table #Bill

Go

Create table #Bill([项目] nvarchar(25),[分值列表] nvarchar(29))

Insert #Bill

select N'思想品德',N'15,10,-15' union all

select N'贡献与失误',N'15,10,-15' union all

select N'任务完成',N'15,13,10' union all

select N'出勤率',N'10,8,4' union all

select N'表达能力',N'8,6,4' union all

select N'团结协作',N'9,7,5' union all

select N'业务能力',N'8,6,4' union all

select N'工作态度',N'5,4,2' union all

select N'效率和质量',N'5,4,2' union all

select N'本人作用',N'5,4,2'

GO

if not object_id(N'Tempdb..#Project') is null

	drop table #Project

Go

Create table #Project([姓名] nvarchar(23),[项目] nvarchar(25),[得分] nvarchar(21))

Insert #Project

select N'安丰顺',N'思想品德',N'A' union all

select N'安丰顺',N'贡献与失误',N'B' union all

select N'安丰顺',N'任务完成',N'C' union all

select N'安丰顺',N'出勤率',N'C' union all

select N'安丰顺',N'表达能力',N'A' union all

select N'安丰顺',N'团结协作',N'A' union all

select N'安丰顺',N'业务能力',N'B' union all

select N'安丰顺',N'工作态度',N'C' union all

select N'安丰顺',N'效率和质量',N'C' union all

select N'安丰顺',N'本人作用',N'A' union all

select N'卞树明',N'思想品德',N'A' union all

select N'卞树明',N'贡献与失误',N'A' union all

select N'卞树明',N'任务完成',N'A' union all

select N'卞树明',N'出勤率',N'C' union all

select N'卞树明',N'表达能力',N'A' union all

select N'卞树明',N'团结协作',N'B' union all

select N'卞树明',N'业务能力',N'B' union all

select N'卞树明',N'工作态度',N'C' union all

select N'卞树明',N'效率和质量',N'A' union all

select N'卞树明',N'本人作用',N'C'

Go

--测试数据结束

SELECT  #Project.姓名 ,

        ( SELECT TOP 1

                    value

          FROM      dbo.F_Split(分值列表, ',') t

          WHERE     id = ( CASE WHEN 得分 = 'A' THEN 1

                                WHEN 得分 = 'B' THEN 2

                                ELSE 3

                           END )

        ) AS 总分

FROM    #Project

        JOIN #Bill ON #Project.项目 = #Bill.项目






SQL语句基础教程
SQL语句基础教程
SQL学习资料（包含所有常用的语句例子）
sql学习必备，内含sql一些常用语句的例子，有图有表，通过结果来理解sql语句的实现与结果。
PLSQL执行SQL语句
PLSQL执行SQL语句 1、执行中量SQL语句 当执行SQL语句较大时，除运行SQL语句外可以点击工具-------&gt;导入表---------&gt;（出现界面如下图所示） 点击导入就会出现执行语句的窗口如下图所示，把语句粘贴进去，自动执行，执行完毕，提交事务，导入成功！ 也可以直接打开命令行方式：点击新增SQL窗口直接执行。  ...
oracle sql developer 执行sql语句,如何使用SQL Developer执行SQL语句（上）
本章摘自Murach的《Orcle SQL和PL/SQL》一书，描述了如何使用Oracle SQL Developer以及其它工具。在这一部分，我们将会学习如何使用SQL Developer来输入并执行SQL语句及脚本。如何使用摘录窗口，解决常见的语法错误等内容。在你回顾数据库设计的同时，SQL Developer还是一个很好的可输入并运行SQL语句的工具。在接下来的部分你将学习到。如何输入并执行...
SQL语句五种分类
在介绍SQL语句五种分类前，先来简单的认识一下SQL。SQL的英文全称为Structured Query Language，SQL用来和数据库打交道，完成和数据库的通信，SQL是一套标准，但是每一个数据库都有自己的特性，当使用这个数据库特性相关的功能，这时SQL语句可能就不是标准了，但是百分之九十以上的SQL都是通用的。 SQL语句的五种分类分别是DQL、DML、DDL、TCL和TCL，下面对SQL语句的五种分类进行列举： 1、数据库查询语言（DQL） 数据查询语言DQL基本结构是由SELECT子句，
MS-SQL Server

34,594

社区成员

254,589

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章