SQL: select a.title,a.username,b.adddate from table a,(select max(adddate) adddate from table where table.title=a.title) b
SQL: select * into b from a where 11
9.1、一个SQL语句的问题:行列转换
select * from v_temp
上面的视图结果如下:
user_name role_name
————————-
系统管理员 管理员
feng 管理员
feng 一般用户
test 一般用户
想把结果变成这样:
user_name role_name
—————————
系统管理员 管理员
feng 管理员,一般用户
test 一般用户
===================
create table a_test(name varchar(20),role2 varchar(20))
insert into a_test values(’李’,'管理員’)
insert into a_test values(’張’,'管理員’)
insert into a_test values(’張’,'一般用戶’)
insert into a_test values(’常’,'一般用戶’)
returns varchar(2000)
as
begin
declare @str varchar(2000)
set @str=”
select @str=@str+’,'+rtrim(role2) from a_test where [name]=@content
select @str=right(@str,len(@str)-1)
return @str
end
go
select [name],dbo.join_str([name]) role2 from a_test group by [name]
结构相同的两表,一表有记录3万条左右,一表有记录2万条左右,我怎样快速查找两表的不同记录?
============================
给你一个测试方法,从northwind中的orders表取数据。
select * into n1 from orders
select * into n2 from orders
select * from n2
alter table n1 add constraint pk_n1_id primary key (OrderID)
alter table n2 add constraint pk_n2_id primary key (OrderID)
union
select * from n2) a group by OrderID having count(*) > 1
下面的适用于双方记录一样的情况,
(
select OrderID from (select * from n1
union
select * from n2) a group by OrderID having count(*) > 1
)
至于双方互不存在的记录是比较好处理的
–删除n1,n2中若干条记录
delete from n1 where orderID in (’10728′,’10730′)
delete from n2 where orderID in (’11000′,’11001′)
– 双方都有该记录却不完全相同
select * from n1 where orderid in
(
select OrderID from (select * from n1
union
select * from n2) a group by OrderID having count(*) > 1
)
union
–n2中存在但在n1中不存的在10728,10730
select * from n1 where OrderID not in (select OrderID from n2)
union
–n1中存在但在n2中不存的在11000,11001
select * from n2 where OrderID not in (select OrderID from n1)
select top m * into 临时表(或表变量) from tablename order by columnname — 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
select identity(int) id0,* into #temp from tablename
select * from #temp where id0 >=n and id0 1′
exec(@sql)
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id
if @type=167
select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””
exec(@sql)
fetch cur_rows into @id,@max
end
close cur_rows
deallocate cur_rows
set rowcount 0
end
select * from syscolumns where id = object_id(’a_dist’)
CREATE TABLE hard (qu char (11) ,co char (11) ,je numeric(3, 0))
insert into hard values (’A',’2′,4)
insert into hard values (’A',’4′,2)
insert into hard values (’A',’6′,9)
insert into hard values (’B',’1′,4)
insert into hard values (’B',’2′,5)
insert into hard values (’B',’3′,6)
insert into hard values (’C',’3′,4)
insert into hard values (’C',’6′,7)
insert into hard values (’C',’2′,3)
———– ———– —–
A 6 9
A 2 4
B 3 6
B 2 5
C 6 7
C 3 4
而且只能用一句sql语句!!!
select * from hard a where je in (select top 2 je from hard b where a.qu=b.qu order by je)
9.5.求删除重复记录的sql语句?
怎样把具有相同字段的纪录删除,只留下一条。
例如,表test里有id,name字段
如果有name相同的记录 只留下一条,其余的删除。
name的内容不定,相同的记录数不定。
有没有这样的sql语句?
==============================
A:一个完整的解决方案:
select [标志字段id],count(*) into temp1 from [表名]
group by [标志字段id]
having count(*)>1
insert temp1
select [标志字段id],count(*) from [表名]
group by [标志字段id]
having count(*)=1
select * into temp2 from [表名]
where 标志字段id in(select 标志字段id from temp1)
delete [表名]
insert [表名]
select * from temp2
drop table temp1
drop table temp2
================================
B:
create table a_dist(id int,name varchar(20))
insert into a_dist values(1,’abc’)
insert into a_dist values(1,’abc’)
insert into a_dist values(1,’abc’)
–f_key表示是分組字段﹐即主鍵字段
as
begin
declare @max integer,@id varchar(30) ,@sql varchar(7999) ,@type integer
select @sql = ‘declare cur_rows cursor for select ‘+@f_key+’ ,count(*) from ‘ +@t_name +’ group by ‘ +@f_key +’ having count(*) > 1′
exec(@sql)
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
select @type = xtype from syscolumns where id=object_id(@t_name) and name=@f_key
if @type=56
select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+ @id
if @type=167
select @sql = ‘delete from ‘+@t_name+’ where ‘ + @f_key+’ = ‘+””+ @id +””
exec(@sql)
fetch cur_rows into @id,@max
end
close cur_rows
deallocate cur_rows
set rowcount 0
end
select * from syscolumns where id = object_id(’a_dist’)
Name Subject Result
张三 语文 80
张三 数学 90
张三 物理 85
李四 语文 85
李四 数学 92
李四 物理 82
姓名 语文 数学 物理
张三 80 90 85
李四 85 92 82
set @sql = ’select Name’
select @sql = @sql + ‘,sum(case Subject when ”’+Subject+”’ then Result end) [’+Subject+’]’
from (select distinct Subject from CJ) as a
select @sql = @sql+’ from test group by name’
exec(@sql)
id pid
1 1
1 2
1 3
2 1
2 2
3 1
如何化成表B:
id pid
1 1,2,3
2 1,2
3 1
create function fmerg(@id int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str=”
select @str=@str+’,'+cast(pid as varchar) from 表A where id=@id
set @str=right(@str,len(@str)-1)
return(@str)
End
go
select distinct id,dbo.fmerg(id) from 表A
SQL语句如下:
declare @objid int,@objname char(40)
set @objname = ‘tablename’
select @objid = id from sysobjects where id = object_id(@objname)
select ‘Column_name’ = name from syscolumns where id = @objid order by colid
EXEC sp_password NULL, ‘newpassword’, ‘User’
a. 查已知列名的情况
SELECT b.name as TableName,a.name as columnname
From syscolumns a INNER JOIN sysobjects b
ON a.id=b.id
AND b.type=’U’
AND a.name=’你的字段名字’
Select o.name As tablename,s1.name As columnname
From syscolumns s1, sysobjects o
Where s1.id = o.id
And o.type = ‘U’
And Exists (
Select 1 From syscolumns s2
Where s1.name = s2.name
And s1.id s2.id
)
select *
from (select top xxx * from yourtable) aa
where not exists(select 1 from (select top xxx-1 * from yourtable) bb where aa.id=bb.id)
fetch absolute [number] from [cursor_name]
行数为绝对行数
a. 一个月的第一天
SELECT DATEADD(mm, DATEDIFF(mm,0,getdate()), 0)
b. 本周的星期一
SELECT DATEADD(wk, DATEDIFF(wk,0,getdate()), 0)
c. 一年的第一天
SELECT DATEADD(yy, DATEDIFF(yy,0,getdate()), 0)
d. 季度的第一天
SELECT DATEADD(qq, DATEDIFF(qq,0,getdate()), 0)
e. 上个月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(mm,0,getdate()), 0))
f. 去年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate()), 0))
g. 本月的最后一天
SELECT dateadd(ms,-3,DATEADD(mm, DATEDIFF(m,0,getdate())+1, 0))
h. 本月的第一个星期一
select DATEADD(wk, DATEDIFF(wk,0,
dateadd(dd,6-datepart(day,getdate()),getdate())
), 0)
i. 本年的最后一天
SELECT dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate())+1, 0))。
When ” Then ”
Else ‘*’
End as IsPK,
Object_Name(A.id) as t_name,
A.name as c_name,
IsNull(SubString(M.text, 1, 254), ”) as pbc_init,
T.name as F_DataType,
CASE IsNull(TYPEPROPERTY(T.name, ‘Scale’), ”)
WHEN ” Then Cast(A.prec as varchar)
ELSE Cast(A.prec as varchar) + ‘,’ + Cast(A.scale as varchar)
END as F_Scale,
A.isnullable as F_isNullAble
FROM Syscolumns as A
JOIN Systypes as T
ON (A.xType = T.xUserType AND A.Id = Object_id(’sysobjects’) )
LEFT JOIN ( SysIndexes as I
JOIN Syscolumns as A1
ON ( I.id = A1.id and A1.id = object_id(’sysobjects’) and (I.status & 0×800) = 0×800 AND A1.colid 0 then ‘√’ else ” end) N’主键’,
b.name N’类型’,
a.length N’占用字节数’,
COLUMNPROPERTY(a.id,a.name,’PRECISION’) as N’长度’,
isnull(COLUMNPROPERTY(a.id,a.name,’Scale’),0) as N’小数位数’,
(case when a.isnullable=1 then ‘√’else ” end) N’允许空’,
isnull(e.text,”) N’默认值’,
isnull(g.[value],”) AS N’字段说明’
FROM syscolumns a
left join systypes b
on a.xtype=b.xusertype
inner join sysobjects d
on a.id=d.id and d.xtype=’U’ and d.name’dtproperties’
left join syscomments e
on a.cdefault=e.id
left join sysproperties g
on a.id=g.id AND a.colid = g.smallid
order by object_name(a.id),a.colorder
select rows from sysindexes where id = object_id(‘test’) and indid in (0,1)
update [23] set id1 = ‘No.’+right(’00000000′+id,6) where id not like ‘No%’ //递增
update [23] set id1= ‘No.’+right(’00000000′+replace(id1,’No.’,”),6) //补位递增
delete from [1] where (id%2)=1
奇数
update [1] set domurl = replace(domurl,’/upload/Imgswf/’,'/upload/Photo/’) where domurl like ‘%/upload/Imgswf/%’
SELECT LEFT(表名, 5)