对SQL_Server 部分知识的回顾

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_line
drop 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 coltorow
GROUP 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