有一个表
序号 姓名 品德 政绩 廉洁 票号
1 张三 100 95 80 001
2 张三 80 100 90 002
3 张三 60 60 60 003
我希望通过sql语句查询出结果 《合计表》 《平均表》 数据 项目
240 品德
255 政绩
230 廉洁数据 项目
80 品德
85 政绩
76.67 廉洁初学sql,请各位大侠帮忙给看看!
分少,一片心意!
序号 姓名 品德 政绩 廉洁 票号
1 张三 100 95 80 001
2 张三 80 100 90 002
3 张三 60 60 60 003
我希望通过sql语句查询出结果 《合计表》 《平均表》 数据 项目
240 品德
255 政绩
230 廉洁数据 项目
80 品德
85 政绩
76.67 廉洁初学sql,请各位大侠帮忙给看看!
分少,一片心意!
SELECT SUM([品德]) AS [数据],'品德' AS [项目] FROM TB
UNION ALL SELECT SUM([政绩]) AS [数据],'政绩' AS [项目] FROM TB
UNION ALL SELECT SUM([廉洁]) AS [数据],'廉洁' AS [项目] FROM TB--2
SELECT AVG([品德]) AS [数据],'品德' AS [项目] FROM TB
UNION ALL SELECT AVG([政绩]) AS [数据],'政绩' AS [项目] FROM TB
UNION ALL SELECT AVG([廉洁]) AS [数据],'廉洁' AS [项目] FROM TB
--序号 姓名 品德 政绩 廉洁 票号
--1 张三 100 95 80 001
--2 张三 80 100 90 002
--3 张三 60 60 60 003
use City;
go
set nocount on
if OBJECT_ID(N'A') is not null drop table A
go
create table A
(
序号 int identity(1,1) primary key not null,
姓名 nvarchar(100),
品德 int,
政绩 int,
廉洁 int,
票号 int
)
go
insert into A
select '张三', 100, 95, 80, 001 union all
select '张三', 80, 100, 90, 002 union all
select '张三', 60, 60, 60, 003
go
with cte as
(
select SUM(品德) as '品德',SUM(政绩) as '政绩',SUM(廉洁) as '廉洁' from A group by 姓名 --这句是求平均
--select avg(品德) as '品德',avg(政绩) as '政绩',avg(廉洁) as '廉洁' from A group by 姓名 --这句是求总和
)
,
cte1 as
(select * from cte,(select top 3 ROW_NUMBER()over(order by getdate()) as RN from sysobjects) as B)
select
case when RN=1 then 品德
when RN=2 then 政绩
when RN=3 then 廉洁
else '' end as '数据',
case when RN=1 then '品德'
when RN=2 then '政绩'
when RN=3 then '廉洁'
else '' end as '项目'
from cte1
/*
数据 项目
240 品德
255 政绩
230 廉洁数据 项目
80 品德
85 政绩
76.67 廉洁*/
(
序号 int identity(1,1),
姓名 varchar(100),
品德 INT,
政绩 INT,
廉洁 INT,
票号 varchar(100)
)
go
insert into #T
select '张三', '100', '95', '80', '001' UNION ALL
select '张三', '80', '100', '90', '002' UNION ALL
select '张三', '60', '60', '60', '003'
go
SELECT * FROM #T--和
SELECT SUM([品德]) AS [数据],'品德' AS [项目] FROM #T UNION ALL
SELECT SUM([政绩]) AS [数据],'政绩' AS [项目] FROM #T UNION ALL
SELECT SUM([廉洁]) AS [数据],'廉洁' AS [项目] FROM #T--平均
SELECT AVG([品德]) AS [数据],'品德' AS [项目] FROM #T UNION ALL
SELECT AVG([政绩]) AS [数据],'政绩' AS [项目] FROM #T UNION ALL
SELECT AVG([廉洁]) AS [数据],'廉洁' AS [项目] FROM #T
-1
SELECT SUM([品德]) AS [数据],'品德' AS [项目] FROM TB group by 序号,姓名
--2
SELECT AVG([品德]) AS [数据],'品德' AS [项目] FROM TB group by 序号,姓名
SELECT SUM([品德]) AS [数据],'品德' AS [项目] FROM TB group by 姓名
--2
SELECT AVG([品德]) AS [数据],'品德' AS [项目] FROM TB group by 姓名
if OBJECT_ID(N'tb') is not null drop table tb
go
create table tb
(
序号 int identity(1,1) primary key not null,
姓名 nvarchar(100),
品德 int,
政绩 int,
廉洁 int,
票号 int
)
go
insert into tb
select '张三', 100, 95, 80, 001 union all
select '张三', 80, 100, 90, 002 union all
select '张三', 60, 60, 60, 003
go
select * from tb
go
select 数据=sum(品德),项目='品德' from tb
union all
select 数据=sum(政绩),项目='政绩' from tb
union all
select 数据=sum(廉洁),项目='廉洁' from tb
goselect 数据=sum(品德)/count(品德),项目='品德' from tb
union all
select 数据=sum(政绩)/count(品德),项目='政绩' from tb
union all
select 数据=sum(廉洁)/count(品德),项目='廉洁' from tb
go
数据结果为:
数据 项目
----------- ----
240 品德
255 政绩
230 廉洁(3 行受影响)数据 项目
----------- ----
80 品德
85 政绩
76 廉洁(3 行受影响)
--创建数据库
If db_id('Appraisal') is not null drop database Appraisal
Create Database Appraisal
On Primary
(
name='AppraisalSub1',
filename="F:\Test\AppraisalSub1.mdf",
size=5MB,
MAXSIZE=100MB,
filegrowth=10%
)
LOG ON
(
name='AppraisalSubLog1',
filename="F:\Test\AppraisalSubLog1.ldf",
size=5MB,
MAXSIZE=100MB,
filegrowth=10%
)
--创建楼主所需要的表
use Appraisal
create table AppraisalTable
(
序号 char(5) primary key,
姓名 nchar(20) not null,
品德 decimal(5,2) not null,
政绩 decimal(5,2) not null,
廉洁 decimal(5,2) not null,
票号 Char(3) not null
)
--插入楼主需要的数据
Insert into Appraisal.dbo.AppraisalTable(序号,姓名,品德,政绩,廉洁,票号) values('1','张三',100,95,80,'001')
Insert into Appraisal.dbo.AppraisalTable(序号,姓名,品德,政绩,廉洁,票号) values('2','张三',80,100,90,'002')
Insert into Appraisal.dbo.AppraisalTable(序号,姓名,品德,政绩,廉洁,票号) values('3','张三',60,60,60,'003')--使用查询,输出楼主所要数据,(注:数据表的字段小数位数可以调整)select sum(品德) as 数据,'品德' as 项目 from Appraisal.dbo.AppraisalTable
union
select sum(政绩) as 数据,'政绩' as 项目 from Appraisal.dbo.AppraisalTable
union
select sum(廉洁) as 数据,'廉洁' as 项目 from Appraisal.dbo.AppraisalTable select avg(品德) as 数据,'品德' as 项目 from Appraisal.dbo.AppraisalTable
union
select avg(政绩) as 数据,'政绩' as 项目 from Appraisal.dbo.AppraisalTable
union
select avg(廉洁) as 数据,'廉洁' as 项目 from Appraisal.dbo.AppraisalTable
union all
select 数据=sum(政绩)/count(品德),项目='政绩' from tb
union all
select 数据=sum(廉洁)/count(品德),项目='廉洁' from tb
go
上面写错了
应该是
select 数据=sum(品德)/count(品德),项目='品德' from tb
union all
select 数据=sum(政绩)/count(政绩),项目='政绩' from tb
union all
select 数据=sum(廉洁)/count(廉洁),项目='廉洁' from tb
go