这样的语句在SQLSERVER，应该怎么写？

zbguolei 2012-11-20 07:53:09

select '语文优秀',min(语文) from (select top 15 percent 语文 from cj order by 语文 desc)
union all
select '数学',min(数学) from (select top 15 percent 数学 from cj order by 数学 desc)
union all
select '英语',min(英语) from (select top 15 percent 英语 from cj order by 英语 desc)
union all
select '生物',min(生物) from (select top 15 percent 生物 from cj order by 生物 desc)
union all
select '政治',min(政治) from (select top 15 percent 政治 from cj order by 政治 desc)
union all
select '历史',min(历史) from (select top 15 percent 历史 from cj order by 历史 desc)
union all
select '物理',min(物理) from (select top 15 percent 物理 from cj order by 物理 desc)
union all
select '化学',min(化学) from (select top 15 percent 化学 from cj order by 化学 desc)
UNION ALL
select '总分',min(总分) from (select top 15 percent 总分 from cj order by 总分 desc)
union all
select '语文良好分',min(语文) from (select top 40 percent 语文 from cj order by 语文 desc)
union all
select '数学',min(数学) from (select top 40 percent 数学 from cj order by 数学 desc)
union all
select '英语',min(英语) from (select top 40 percent 英语 from cj order by 英语 desc)
union all
select '生物',min(生物) from (select top 40 percent 生物 from cj order by 生物 desc)
union all
select '政治',min(政治) from (select top 40 percent 政治 from cj order by 政治 desc)
union all
select '历史',min(历史) from (select top 40 percent 历史 from cj order by 历史 desc)
union all
select '物理',min(物理) from (select top 40 percent 物理 from cj order by 物理 desc)
union all
select '化学',min(化学) from (select top 40 percent 化学 from cj order by 化学 desc)
UNION ALL
select '总分',min(总分) from (select top 40 percent 总分 from cj order by 总分 desc)
union all
select '语文及格分',min(语文) from (select top 60 percent 语文 from cj order by 语文 desc)
union all
select '数学',min(数学) from (select top 60 percent 数学 from cj order by 数学 desc)
union all
select '英语',min(英语) from (select top 60 percent 英语 from cj order by 英语 desc)
union all
select '生物',min(生物) from (select top 60 percent 生物 from cj order by 生物 desc)
union all
select '政治',min(政治) from (select top 60 percent 政治 from cj order by 政治 desc)
union all
select '历史',min(历史) from (select top 60 percent 历史 from cj order by 历史 desc)
union all
select '物理',min(物理) from (select top 60 percent 物理 from cj order by 物理 desc)
union all
select '化学',min(化学) from (select top 60 percent 化学 from cj order by 化学 desc)
UNION ALL select '总分',min(总分) from (select top 60 percent 总分 from cj order by 总分 desc);

以上代码在ACCESS中，可以正常查询。在sqlserver中，应该怎么修改呢？

...全文

123 1 打赏收藏转发到动态举报

写回复

用AI写文章

1 条回复

切换为时间正序

请发表友善的回复…

发表回复

發糞塗牆 2012-11-20

打赏
举报

SELECT  '语文优秀' [状态],
         MIN(语文)[学科]
 FROM    ( SELECT TOP 15 PERCENT
                     语文
           FROM      cj
           ORDER BY  语文 DESC
         )a
 UNION ALL
 SELECT  '数学' ,
         MIN(数学)
 FROM    ( SELECT TOP 15 PERCENT
                     数学
           FROM      cj
           ORDER BY  数学 DESC
         )b
 UNION ALL
 SELECT  '英语' ,
         MIN(英语)
 FROM    ( SELECT TOP 15 PERCENT
                     英语
           FROM      cj
           ORDER BY  英语 DESC
         )c
 UNION ALL
 SELECT  '生物' ,
         MIN(生物)
 FROM    ( SELECT TOP 15 PERCENT
                     生物
           FROM      cj
           ORDER BY  生物 DESC
         )d
 UNION ALL
 SELECT  '政治' ,
         MIN(政治)
 FROM    ( SELECT TOP 15 PERCENT
                     政治
           FROM      cj
           ORDER BY  政治 DESC
         )e
 UNION ALL
 SELECT  '历史' ,
         MIN(历史)
 FROM    ( SELECT TOP 15 PERCENT
                     历史
           FROM      cj
           ORDER BY  历史 DESC
         )f
 UNION ALL
 SELECT  '物理' ,
         MIN(物理)
 FROM    ( SELECT TOP 15 PERCENT
                     物理
           FROM      cj
           ORDER BY  物理 DESC
         )g
 UNION ALL
 SELECT  '化学' ,
         MIN(化学)
 FROM    ( SELECT TOP 15 PERCENT
                     化学
           FROM      cj
           ORDER BY  化学 DESC
         )h
 UNION ALL
 SELECT  '总分' ,
         MIN(总分)
 FROM    ( SELECT TOP 15 PERCENT
                     总分
           FROM      cj
           ORDER BY  总分 DESC
         )j
 UNION ALL
 SELECT  '语文良好分' ,
         MIN(语文)
 FROM    ( SELECT TOP 40 PERCENT
                     语文
           FROM      cj
           ORDER BY  语文 DESC
         )k
 UNION ALL
 SELECT  '数学' ,
         MIN(数学)
 FROM    ( SELECT TOP 40 PERCENT
                     数学
           FROM      cj
           ORDER BY  数学 DESC
         )l
 UNION ALL
 SELECT  '英语' ,
         MIN(英语)
 FROM    ( SELECT TOP 40 PERCENT
                     英语
           FROM      cj
           ORDER BY  英语 DESC
         )m
 UNION ALL
 SELECT  '生物' ,
         MIN(生物)
 FROM    ( SELECT TOP 40 PERCENT
                     生物
           FROM      cj
           ORDER BY  生物 DESC
         )n
 UNION ALL
 SELECT  '政治' ,
         MIN(政治)
 FROM    ( SELECT TOP 40 PERCENT
                     政治
           FROM      cj
           ORDER BY  政治 DESC
         )o
 UNION ALL
 SELECT  '历史' ,
         MIN(历史)
 FROM    ( SELECT TOP 40 PERCENT
                     历史
           FROM      cj
           ORDER BY  历史 DESC
         )p
 UNION ALL
 SELECT  '物理' ,
         MIN(物理)
 FROM    ( SELECT TOP 40 PERCENT
                     物理
           FROM      cj
           ORDER BY  物理 DESC
         )q
 UNION ALL
 SELECT  '化学' ,
         MIN(化学)
 FROM    ( SELECT TOP 40 PERCENT
                     化学
           FROM      cj
           ORDER BY  化学 DESC
         )r
 UNION ALL
 SELECT  '总分' ,
         MIN(总分)
 FROM    ( SELECT TOP 40 PERCENT
                     总分
           FROM      cj
           ORDER BY  总分 DESC
         )s
 UNION ALL
 SELECT  '语文及格分' ,
         MIN(语文)
 FROM    ( SELECT TOP 60 PERCENT
                     语文
           FROM      cj
           ORDER BY  语文 DESC
         )t
 UNION ALL
 SELECT  '数学' ,
         MIN(数学)
 FROM    ( SELECT TOP 60 PERCENT
                     数学
           FROM      cj
           ORDER BY  数学 DESC
         )u
 UNION ALL
 SELECT  '英语' ,
         MIN(英语)
 FROM    ( SELECT TOP 60 PERCENT
                     英语
           FROM      cj
           ORDER BY  英语 DESC
         )v
 UNION ALL
 SELECT  '生物' ,
         MIN(生物)
 FROM    ( SELECT TOP 60 PERCENT
                     生物
           FROM      cj
           ORDER BY  生物 DESC
         )w
 UNION ALL
 SELECT  '政治' ,
         MIN(政治)
 FROM    ( SELECT TOP 60 PERCENT
                     政治
           FROM      cj
           ORDER BY  政治 DESC
         )y
 UNION ALL
 SELECT  '历史' ,
         MIN(历史)
 FROM    ( SELECT TOP 60 PERCENT
                     历史
           FROM      cj
           ORDER BY  历史 DESC
         )z
 UNION ALL
 SELECT  '物理' ,
         MIN(物理)
 FROM    ( SELECT TOP 60 PERCENT
                     物理
           FROM      cj
           ORDER BY  物理 DESC
         )aa
 UNION ALL
 SELECT  '化学' ,
         MIN(化学)
 FROM    ( SELECT TOP 60 PERCENT
                     化学
           FROM      cj
           ORDER BY  化学 DESC
         )bb
 UNION ALL
 SELECT  '总分' ,
         MIN(总分)
 FROM    ( SELECT TOP 60 PERCENT
                     总分
           FROM      cj
           ORDER BY  总分 DESC
         ) cc

SQL语句基础教程

在我个人编写SQL脚本时，至少会把SQL的格式排列成易于阅读的，因为其他人会阅读到你的SQL，无论是在程序中或是脚本文件中，良好的排版不仅让人看起来赏心悦目，在和他人之间做交流时也省时省力，不会因为揉成一团的...

到微软官网下载SQL Server Developer版本，现在的最新版本是SQL Server 2019 Developer。微软官网传送门：点击此处直达下载完成之后，在文件夹中找到刚才下载的文件，双击打开，打开之后的界面如下图所示。我们...

今天介绍一下如何使用SQL Server语句创建表并添加数据首先先了解一下表的模式，在数据库中根据模式进行分组避免表名称的冲突在SQL Server 2014中直接新建表是默认的前缀dbo 而命名其他的模式需要使用SQL Server...

在SQL SERVER中当查询的条件是一个选择范围是SQL语句怎么写？这条语句是在JAVA界面层有的！如：房屋单价是从1000——2000，SQL语句：select*from table where money between ? and ? 可以吗？但是我在数据库中价格...