对SQL_Server 部分知识的回顾
来源:互联网 发布:三菱plc编程软件 编辑:程序博客网 时间:2024/06/05 20:50
1.给SQL server 加注释,如果只有一行 “--”,如果有多行的话,请使用/*.......*/,这个跟SAS的注释一样。
1.1 SQL SERVER 有行的问题,所以使用了 set nocount on set nocount off ,分别放在开始和结尾。
2.能不能在查询当中生成一个新表,当然是可以的,SELECT * INTO new table name SQL server
create table tab_new as select col1,col2… from tab_old definition onlyoracle
Select * Into new_table_name from old_table_name;Mysql
3.如何在查询中,行转列,列转行
use test
go
select * from row_to_linedrop table row_to_line
/*************** 先建一个表格,供大家好操作 use MS SQL server*********************/
set nocount on
CREATE TABLE row_to_line
(
user_name character varying(30) NOT NULL, -- 学生名称
yingyu integer, -- 得分
yuwen integer,
huaxue integer,
wuli integer,
CONSTRAINT row_to_line_pkey PRIMARY KEY (user_name)
);
insert into row_to_line select 'liqiu', 80, 90, 90, 89;
insert into row_to_line select 'lingling', 89, 99, 100, 90;
insert into row_to_line select 'xingxing', 90, 94, 97, 99;
set nocount off
/**************行转列 代码 SQL server*********************/
set nocount on
select a.user_name,
a.title,
a.score into coltorow
from
(
(select user_name, yingyu as "score", 'yingyu' as title from row_to_line)
union (select user_name, yuwen as "score", 'yuwen' as title from row_to_line)
union (select user_name, huaxue as "score", 'huaxue' as title from row_to_line)
union (select user_name, wuli as "score", 'wuli' as title from row_to_line)
) a
order by a.user_name, a.title
set nocount off
drop table coltorow
select * from coltorow
/**************列转ROW代码 SQL server method 1*********************/
SELECT user_name, MAX(CASE title WHEN 'yuwen' THEN Score ELSE 0 END) AS "语文", MAX(CASE title WHEN 'huaxue' THEN Score ELSE 0 END) AS "数学", MAX(CASE title WHEN 'yingyu' THEN Score ELSE 0 END) AS "英语", MAX(CASE title WHEN 'wuli' THEN Score ELSE 0 END) AS "生物"FROM coltorowGROUP BY user_name
/**************rows transfer to column code for SQL server method 2********************/
SELECT
a.user_name,
b.score as "语文",
c.score as "化学",
d.score as "英语",
e.score as "生物"
FROM (select distinct user_name from coltorow) a
left join (select score, user_name FROM coltorow where title = 'yuwen') b on b.user_name=a.user_name
left join (select score, user_name FROM coltorow where title = 'huaxue') c on c.user_name=a.user_name
left join (select score, user_name FROM coltorow where title = 'yingyu') d on d.user_name=a.user_name
left join (select score, user_name FROM coltorow where title = 'wuli') e on e.user_name=a.user_name
阅读全文
0 0
- 对SQL_Server 部分知识的回顾
- java对sql_server的操作
- 对数据库知识的回顾
- 对SAS知识的点滴回顾
- 操作系统部分重点知识回顾
- div知识的回顾
- javaWeb知识的回顾
- 回顾以前的知识
- 对与java io知识的部分总结
- 关于java的知识回顾
- 前三天的知识回顾
- C++ ”类“ 知识的回顾
- Access和SQL_Server的区别
- 数据库的部分知识
- 知识回顾
- 回顾知识
- 对C++片断的回顾
- 一张图来回顾自己的知识
- javascript中.push()与.concat()的区别
- 监听动画的事件
- AngularJS Phonecat (步骤8-步骤9)
- Android开发之如何保证Service不被杀掉(永活不死)
- 【C++】实现Singleton模式
- 对SQL_Server 部分知识的回顾
- 继承与多态的区别
- 【微信开发】定制消息推送
- Android Monkey日志自动分析脚本
- Cordova插件之支付整理
- POJ
- 【 Python进阶】python strip() split()函数实战
- github(To prevent you from losing history, non-fast-forward updates were rejected)
- Spring xmlDI和注解