行转列、是否是数据判断、转换、分页查询

一、ROW_NUMBER 

语法：ROW_NUMBER ( ) OVER ( [ <partition_by_clause> ] <order_by_clause> )
例子：select row_number() over (order by dict_type_id) ,* from E3_DICT_TYPE

 

  二、打开输出 

 

set serveroutput on;

 

三、清空表数据 

Truncate table E3_MEASURE_U2000V1R7C00

四、SQL将传递的字符串全部转换为大写UPPER（@version）

SET @rmTable = 'iknow_rmpublishtree_' + UPPER(@version)

 

五、判断是否是数据类型： 

 

select convert(int,case when isnumeric(code)=1 then code else 0 end) from IKNOW_STORY_U2000V1R7C00;
说明：

    isnumeric，用来判断是否是数据类型,当值等于1时时数据类型、值等于0是不是数据类型

六、将varchar类型转换为int类型 

1、select convert (int,'123456')
2、declare @nv nvarchar(10)
     set @nv='123456'
     declare @n int
     set @n = convert(int,@nv)
     print @n

 

七、判断一个数是不是数字如果是数字就转换

 

 select @CYCLE_FINISHCODE_CODE = 	sum(convert(numeric(10,2),case when isnumeric(code)=1 then code else 0 end))/1000 

 

八、 行转列

 

 --创建临时表，将查出的数据插入临时表

declare @tm_staticTable table(children int,tdtname varchar(255),stand_tdt varchar(255),cycle varchar(255));
insert into @tm_staticTable (children,tdtname,stand_tdt,cycle) select a.children,a.tdtname,b.stand_tdt,a.cycle
from '+@staticTable+' as a left join E3_VERSION_TDT_MAP as b on a.tdtname = b.EA_TDT where a.tdtname <> ''''

and b.EA_VERSION = '''+@version+'''

--行转列

declare @tem_calss table(id int identity(1,1) primary key,name001 varchar(255),class001 varchar(255),number001 numeric(5,2))
insert into @tem_calss values ('熊敏','语文',100)
insert into @tem_calss values ('熊敏','数学',100)
insert into @tem_calss values ('熊敏','物理',100)
insert into @tem_calss values ('熊伟','语文',100)
insert into @tem_calss values ('熊伟','数学',100)
insert into @tem_calss values ('熊伟','物理',100)

select name '姓名',
max(case course when '语文' then number else 0 end) '语文',
max(case course when '数学' then number else 0 end) '数学',
max(case course when '物理' then number else 0 end) '物理'
from @tem_calss group by name

--sql2005中行转列关键词

select * from @tem_calss pivot(max(number) for course in ('语文','数学','物理')) a

--列传行
declare @tem_tablele table (姓名 varchar(10),语文 int,数学 int,物理 int)
insert into @tem_tablele values ('熊敏',100,100,100)
insert into @tem_tablele values ('熊伟',100,100,100)
insert into @tem_tablele values ('熊无名',100,100,100)

select * from @tem_tablele

select * from
(
select 姓名,'语文' 课程,语文 分数 from @tem_tablele
union all
select 姓名,'数学' 课程,数学 分数 from @tem_tablele
union all
select 姓名,'物理' 课程,物理 分数 from @tem_tablele
) t
order by 姓名

 

九、查看字符串长度

  print len(@RunSQL)

   

十、分页查询

String sql = "select top 10 * from shangPin where changPinID not in (select top "+10*(page-1)+" changPinID from shangPin order by changPinID desc) order by changPinID desc";