【SQL 点滴积累】

--如何将表中的某列所有的值显示在一行中
declare @output varchar(max)
declare @tablename varchar(max)

set @tablename='OCN_Ship' --修改表名

--临时表存储在 tempdb 数据库中
if exists (select * from tempdb.dbo.sysobjects where id = object_id('tempdb.dbo.#temp'))
drop table [dbo].[#temp]

--生成拼接字段
select @output = coalesce(@output + ',' , '') + '''' +[name]+'''' from syscolumns 
where id=OBJECT_ID(@tablename) order by colorder

--生成函数
select output='function fn'+[name]+'(val, cellmeta, record) {
return fnRenderer(val, cellmeta, record, '''+[name]+''');}' from syscolumns 
where id=OBJECT_ID(@tablename) order by colorder

--生成列1
declare @colorder bigint
select @colorder= MAX(colorder) from syscolumns where id=OBJECT_ID(@tablename)
select case colorder 
when @colorder
then '{ dataIndex: '''+[name]+''', hidden: true, sortable: true }'
else '{ dataIndex: '''+[name]+''', hidden: true, sortable: true },' 
end [coldes],[name] as name,colorder as colorder
from syscolumns 
where id=OBJECT_ID(@tablename) order by colorder

--生成列2
declare @colorder bigint
select @colorder= MAX(colorder) from syscolumns where id=OBJECT_ID(@tablename)
select case colorder 
when @colorder
then '{ header: '''+[name]+''', dataIndex: ''IsDel'', renderer: fn'+[name]+', sortable: true, width: 100 }'
else '{ header: '''+[name]+''', dataIndex: ''IsDel'', renderer: fn'+[name]+', sortable: true, width: 100 },' 
end [coldes],[name] as name,colorder as colorder
from syscolumns 
where id=OBJECT_ID(@tablename) order by colorder

--生成默认文本字段
select '
var txt'+[name]+' = new Ext.form.TextField({
 fieldLabel: ''<font color="red">*</font>'+[name]+''',
 name: '''+[name]+''',
 allowBlank: false,
 blankText: ''内容不能为空'',
 maxLength: 50,
 labelStyle: ''padding-right:10px; text-align:right;''
 });' 
as coldes,[name] as name,
colorder as colorder
 into #temp 
from syscolumns 
where id=OBJECT_ID(@tablename) order by colorder
select * from #temp order by colorder

--生成默认文本字段控件名称
select @output = coalesce(@output + ',' , '') + 'txt' +[name] from syscolumns 
where id=OBJECT_ID(@tablename) order by colorder
select @output


--查询表不允许为NULL的列
declare @output nvarchar(max)
select @output = coalesce(@output + ',' , '') + '''' +syscolumns.[name]+'''' 
FROM syscolumns, systypes WHERE syscolumns.xusertype = systypes.xusertype 
AND syscolumns.id = object_id('Orderidx')
and syscolumns.isnullable=0
order by syscolumns.colorder asc
select @output

--筛选出数据库 NewCRM和CRM 中的关于直通车项目的差异表
SELECT 'select * into NewCRM.dbo.'+B.TABLE_NAME+' from CRM.dbo.'+B.TABLE_NAME FROM (
select TABLE_NAME FROM NewCRM.INFORMATION_SCHEMA.TABLES 
Where TABLE_TYPE='BASE TABLE' AND TABLE_NAME LIKE '%DC_%' AND TABLE_NAME NOT LIKE '%bak%') A
RIGHT JOIN (
select TABLE_NAME FROM CRM.INFORMATION_SCHEMA.TABLES 
Where TABLE_TYPE='BASE TABLE' AND TABLE_NAME LIKE '%DC_%' AND TABLE_NAME NOT LIKE '%bak%') B
ON A.TABLE_NAME=B.TABLE_NAME
WHERE A.TABLE_NAME IS NULL OR B.TABLE_NAME IS NULL
ORDER BY B.TABLE_NAME ASC

--筛选出数据库 NewCRM和CRM 中的差异表
SELECT 'select * into NewCRM.dbo.'+B.TABLE_NAME+' from CRM.dbo.'+B.TABLE_NAME FROM (
select TABLE_NAME FROM NewCRM.INFORMATION_SCHEMA.TABLES 
Where TABLE_TYPE='BASE TABLE' AND TABLE_NAME NOT LIKE '%bak%') A
RIGHT JOIN (
select TABLE_NAME FROM CRM.INFORMATION_SCHEMA.TABLES 
Where TABLE_TYPE='BASE TABLE' AND TABLE_NAME NOT LIKE '%bak%') B
ON A.TABLE_NAME=B.TABLE_NAME
WHERE A.TABLE_NAME IS NULL OR B.TABLE_NAME IS NULL

1. not exists 比 not in 的效率高
2. inner join 比 left join 的效率高
3. 使用触发器 生成 createDate,modifyDate 保证时间一致，
应用于分布式web项目
4. sql 解决并发问题，一般出现在修改的时候，防止多人修改同一条记录
rowVer timestamp 类型
5. 按照表记录中的某些列来循环插入数据（通过声明变量来存放列的值，并通过变量来区分列）
    DECLARE @compRate decimal(10,2),
            @designerRate decimal(10,2),
            @introducerRate decimal(10,2),
            @constructorRate decimal(10,2)
        select 
        @compRate = a.compRate,
        @designerRate = a.designerRate,
        @introducerRate = a.introducerRate,
        @constructorRate = a.constructorRate
        from dbo.REC_SalesBouns a


--查找出重复行并且派出ID最小的行（表示为后来重复添加的行）
select * from xm_question a 
where (a.orderno) in   
(select orderno from xm_question 
group by orderno having 
count(*) > 1)
and ID not in (select min(ID) from xm_question group by orderno having count(*)>1) 
order by orderno,createtime


 --查询排名应该在分页范围内的记录
select a.KeywordId,a.[Rank] 排名,
(cast(replace(replace(a.pageindex,'第',''),'页','') as int)-1)*10+1 beginPosition,
cast(replace(replace(a.pageindex,'第',''),'页','') as int) * 10 endPosition,
a.PageIndex,a.SEOType,a.VPNIP,a.DataTime,
b.Word 关键字,c.CompanyName 客户名称,c.DomainUrl 
from dbo.Bus_KeywordTime a
left join dbo.Bus_Keyword b on a.KeywordId=b.id
left join dbo.Bus_Customer c on b.CustomerId=c.id
where a.seotype='yahoo'
--and a.keywordid=102
and a.datatime > '2014-03-05'
and a.[rank] >= (cast(replace(replace(a.pageindex,'第',''),'页','') as int)-1)*10+1
and a.[rank] <= cast(replace(replace(a.pageindex,'第',''),'页','') as int) * 10
--与下面两句同义
--and a.[rank] not between (cast(replace(replace(a.pageindex,'第',''),'页','') as int)-1)*10+1 
--and cast(replace(replace(a.pageindex,'第',''),'页','') as int) * 10 
--and (a.[rank] < (cast(replace(replace(a.pageindex,'第',''),'页','') as int)-1)*10+1
--or a.[rank] > cast(replace(replace(a.pageindex,'第',''),'页','') as int) * 10)
and a.pageindex <> ''
order by a.datatime desc

-- T-SQL的回车和换行符（SQL）
-- sql server中的回车换行字符是  char(13)+char(10)
-- 回车：char(13)
-- 换行：char(10)

 

用参数的形式做IN 查询 解决方法是使用CHARINDEX 如： CHARINDEX('Y','Y,F,N') > 0 （索引从1开始）

批量新增或修改解决方法是使用 用户自定义表值类型参数，将DataTable 作为参数传入存储过程，对于表值类型参数（存储过程），
如果在存储过程中需要对表值类型参数进行二次修改，则可以定义一个表值变量用来创建一个表值类型参数的副本（这样做的原因是：表值类型参数是只读的）