insert into #a(id,name) values(\'11\',\'aaaa\')
insert into #a(id,name) values(\'11\',\'bbbb\')
insert into #a(id,name) values(\'11\',\'cccc\')
insert into #a(id,name) values(\'22\',\'dddd\')
insert into #a(id,name) values(\'22\',\'eeee\')
insert into #a(id,name) values(\'22\',\'ffff\')
select * from #a b
where name=(select top 1 name from #a where id=b.id)
insert into #a(id,name) values(\'11\',\'aaaa\')
insert into #a(id,name) values(\'11\',\'bbbb\')
insert into #a(id,name) values(\'11\',\'cccc\')
insert into #a(id,name) values(\'22\',\'dddd\')
insert into #a(id,name) values(\'22\',\'eeee\')
insert into #a(id,name) values(\'22\',\'ffff\')
select id1=identity(int,1,1),* into #t from #a
go
select id,name from #t where id1 in(select min(id1) from #t group by id)
declare @Days varchar(4000)
declare @tmpDay varchar(10)
set @Days=\'2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1\'
set @tmpDay=\'\'
declare @i int
set @i=0
while @i<len(@Days)
begin
set @i=@i+1
if SUBSTRING(@Days,@i,1)=\'|\'
begin
print left(@Days,@i-1)
set @Days=SUBSTRING(@Days,@i+1,len(@Days))
set @i=0
end
end
print @Days
1:
SELECT TABLE_NAME,COLUMN_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE TABLE_NAME<>\'dtproperties\'
2:
EXEC sp_pkeys @table_name=\'表名\'
3:
select o.name as 表名,c.name as 字段名,k.colid as 字段序号,k.keyno as 索引顺序,t.name as 类型
from sysindexes i
join sysindexkeys k on i.id = k.id and i.indid = k.indid
join sysobjects o on i.id = o.id
join syscolumns c on i.id=c.id and k.colid = c.colid
join systypes t on c.xusertype=t.xusertype
where o.xtype = \'U\' and o.name=\'要查询的表名\'
and exists(select 1 from sysobjects where xtype = \'PK\' and parent_obj=i.id and name = i.name)
order by o.name,k.colid作者: 潇洒老乌龟 时间: 2006-10-19 09:27
0004--将多个表的数据按ID合并起来
将多个表的数据按ID合并起来
a
id , value
1 , 11
2 , 21
3 , 31
4 , 41
5 , 51
insert into #a(id,value) values(\'1\',11)
insert into #a(id,value) values(\'2\',21)
insert into #a(id,value) values(\'3\',31)
insert into #a(id,value) values(\'4\',41)
insert into #a(id,value) values(\'5\',51)
insert into #b(id,value) values(\'1\',12)
insert into #b(id,value) values(\'2\',22)
insert into #b(id,value) values(\'3\',32)
insert into #b(id,value) values(\'4\',42)
insert into #c(id,value) values(\'1\',13)
insert into #c(id,value) values(\'2\',23)
insert into #c(id,value) values(\'3\',33)
insert into #c(id,value) values(\'5\',53)
select isnull(isnull(#a.id,#b.id),#c.id) id,#a.value value1,#b.value value2,#c.value value3
from #a full join #b on #a.id=#b.id
full join #c on isnull(#a.id,#b.id)=#c.id
insert into #a(a1,a2) values(\'1\',9)
insert into #a(a1,a2) values(\'2\',10)
insert into #a(a1,a2) values(\'3\',67)
insert into #a(a1,a2) values(\'4\',33)
insert into #b(b1,b2) values(\'2\',31)
insert into #b(b1,b2) values(\'4\',99)
--显示NULL
--select isnull(#a.a1,#b.b1) a1,#a.a2 a2,#b.b2 b2
--from #a full join #b on #a.a1=#b.b1
--显示0
select #A.a1, #A.a2, isnull(#B.b2, 0)
from #A left join #B on #A.a1 = #B.b1
drop table #a
drop table #b作者: 潇洒老乌龟 时间: 2006-10-19 09:27
0005--如何取每个种类的前百分之x的记录
示例数据(表a):
area cust money
--------------
A 123 20.0
A 159 20.0
A 456 25.0
A 483 30.0
A 789 40.0
A 597 50.0
B 147 10.0
B 258 20.0
B 369 25.0
B 384 30.0
希望得到的如下结果
area cust money
--------------
A 483 30.0
A 789 40.0
A 597 50.0
B 369 25.0
B 384 30.0
insert into #a(area,cust,money) values(\'A\',\'123\',20.0)
insert into #a(area,cust,money) values(\'A\',\'159\',20.0)
insert into #a(area,cust,money) values(\'A\',\'456\',25.0)
insert into #a(area,cust,money) values(\'A\',\'483\',30.0)
insert into #a(area,cust,money) values(\'A\',\'789\',40.0)
insert into #a(area,cust,money) values(\'A\',\'597\',50.0)
insert into #a(area,cust,money) values(\'B\',\'147\',10.0)
insert into #a(area,cust,money) values(\'B\',\'258\',20.0)
insert into #a(area,cust,money) values(\'B\',\'369\',25.0)
insert into #a(area,cust,money) values(\'B\',\'384\',30.0)
select * from #a t
where cust in
(
select top 50 percent cust from #a where area=t.area order by money desc
)
drop table #a
//结果
area cust money
---------- ---------- ------------
A 483 30.0
A 789 40.0
A 597 50.0
B 369 25.0
B 384 30.0
declare @record table(ManID varchar(10),TheDateTime datetime)
insert @record
select \'001\', \'2006-09-26 10:00:00\' union all
select \'001\', \'2006-09-26 10:05:00\' union all
select \'001\', \'2006-09-26 12:00:00\' union all
select \'001\', \'2006-09-26 18:00:00\' union all
select \'002\', \'2006-09-26 08:00:00\' union all
select \'002\', \'2006-09-26 10:00:00\' union all
select \'002\', \'2006-09-26 20:00:00\' union all
select \'003\', \'2006-09-26 21:00:00\' union all
select \'001\', \'2006-09-27 12:00:00\' union all
select \'001\', \'2006-09-27 18:00:00\' union all
select \'001\', \'2006-09-27 18:00:00\' union all
select \'001\', \'2006-09-27 18:00:00\' union all
select \'002\', \'2006-09-27 08:00:00\'
select * from @record a where
exists(select * from @record where ManID = a.ManID and datediff(dd,TheDateTime,a.TheDateTime) = 0 group by ManID having count(*) >3)
order by TheDateTime,ManID
insert into #a(id,proname,output,dt) values(\'1\',\'某产品1\',100,\'2006-09-22\')
insert into #a(id,proname,output,dt) values(\'2\',\'某产品1\',130,\'2006-09-23\')
insert into #a(id,proname,output,dt) values(\'3\',\'某产品2\',104,\'2006-09-24\')
insert into #a(id,proname,output,dt) values(\'5\',\'某产品1\',100,\'2006-09-25\')
insert into #a(id,proname,output,dt) values(\'7\',\'某产品1\',200,\'2006-09-26\')
select *,(select sum(output) from #a where dt<=b.dt) as \'每日总量\'
from #a b
order by dt
例如：userno_fm、userno_to
create table test(
num int identity(1,1),
userno_fm varchar(10),
userno_to varchar(10),
username varchar(10))
select * from test
declare @sql varchar(8000)
select @sql=\'\'
select @sql=@sql+\',\'+[name] from
(select [name] from syscolumns where object_id(N\'[test]\')=[id] and [name] not in (\'userno_fm\',\'userno_to\')) A
set @sql=\'select \'+stuff(@sql,1,1,\'\')+\' from [test]\'
--print @sql
exec (@sql)
drop table test作者: 潇洒老乌龟 时间: 2006-10-19 09:28
0009--两表字段横向统计表
select
sum(t1.f1) as [t1.f1],
sum(t1.f2) as [t1.f2],
sum(t1.f3) as [t1.f3],
sum(t2.f1) as [t1.f1],
sum(t2.f2) as [t1.f2],
sum(t2.f3) as [t1.f3],
sum(
isnull(t1.f1,0)+
isnull(t1.f2,0)+
isnull(t1.f3,0)+
isnull(t2.f1,0)+
isnull(t2.f2,0)+
isnull(t2.f3,0)
) as [Sum]
from t1 a inner join t2 b on a.id=b.id
select
t1.f1 as [t1.f1],
t1.f2 as [t1.f2],
t1.f3 as [t1.f3],
t2.f1 as [t1.f1],
t2.f2 as [t1.f2],
t2.f3 as [t1.f3],
isnull(t1.f1,0)+
isnull(t1.f2,0)+
isnull(t1.f3,0)+
isnull(t2.f1,0)+
isnull(t2.f2,0)+
isnull(t2.f3,0) as [Sum]
from t1 a inner join t2 b on a.id=b.id作者: 潇洒老乌龟 时间: 2006-10-19 09:29
0010--用SQL算剩余量
请教SQL语句的具体写法!
现有2张表: 一张是商品总量表,一张是商品出库表.如下:
商品总量表:
商品名称 商品总量
a 100
b 200
商品出库表:
商品名称 商品出库数量
a 20
a 30
b 10
b 50
b 60
如何用一条SQL语句将每种商品的剩余量写出来?
1、Select A.商品名称,A. 商品总量-B. 商品出库数量合计 AS 商品的剩余量 from 商品总量表 AS A ,(Select商品名称 , sum(商品出库数量) as商品出库数量合计 from商品出库表 group by商品名称) AS B WHERE A. 商品名称=B. 商品名称
1、Select A.商品名称,A. 商品总量-B. 商品出库数量合计 AS 商品的剩余量 from 商品总量表 AS A LEFT JOIN ,(Select商品名称 , sum(商品出库数量) as商品出库数量合计 from商品出库表 group by商品名称) AS B ON A. 商品名称=B. 商品名称
最好用左联接处理
select a.商品名称 as 商品名称 , a.商品总量 - b.商品出库数量 as 剩余量
from 商品总量表 as a , (select 商品名称 , sum(商品出库数量) as 商品出库数量 group by 商品名称) as b
where a.商品名称 = b.商品名称作者: 潇洒老乌龟 时间: 2006-10-19 09:31
0011--分类统计并算库存
create table #test
(
商品类别 varchar(50),
商品名称 varchar(50),
商品规格 varchar(50),
商品单价 decimal(13,2),
数量 int,
出入库状态 char(1)--出入库状态(1为入库,0为出库)
)
insert into #test select \'服装\',\'西服\',\'175xx\',1000.00,10,\'1\'--单价为1000的西服入库了10件
insert into #test select \'服装\',\'西服\',\'175xx\',2000.00,20,\'1\'--单价为2000的西服入库了20件
insert into #test select \'服装\',\'西服\',\'175xx\',1000.00,3,\'0\'--单价为1000的西服出库了3件
insert into #test select \'服装\',\'西服\',\'175xx\',2000.00,5,\'0\'--单价为2000的西服出库了5件
if object_id(\'tablename\') is not null drop table tablename
select \'张三\' as 姓名, \'语文\' as 科目
into tablename
union select \'张三\', \'数学\'
union select \'张三\', \'英语\'
union select \'张三\', \'物理\'
union select \'李四\', \'语文\'
union select \'李四\', \'化学\'
go
----------------------
if object_id(\'fn_合并\') is not null drop function fn_合并
go
create function fn_合并(
@姓名 varchar(20)
)
returns varchar(300)
as
begin
declare @r varchar(300)
set @r=\'\'
select top 3 @r=@r+\' \'+科目 from tablename where 姓名=@姓名
if @r<>\'\'
set @r=stuff(@r,1,1,\'\')
return @r
end
go
--调用
select 姓名,dbo.fn_合并(姓名) as 科目 from tablename
group by 姓名
如下表
A AA1
A AA2
B BB1
B BB2
相应记录合并，并把字段二的合在一块，得出的结果为
A AA1,AA2
B BB1,BB2
create table csdn(id char(10),txt varchar(10))
insert csdn
select \'A\',\'AA1\' union all
select \'A\',\'AA2\' union all
select \'B\',\'BB1\' union all
select \'B\',\'BB2\'
select * from csdn
go
create function Gettxt(@id char)
returns varchar(8000)
as
begin
declare @s varchar(8000)
set @s=\'\'
select @s=@s +\',\' +txt from csdn where id=@id
--return @s
return stuff(@s,1,1,\'\')
end
go
select id,dbo.Gettxt(id) txt from csdn group by id
go
drop function Gettxt
drop table csdn
go
id txt
---------- ---------
A AA1,AA2
B BB1,BB2
（所影响的行数为 2 行）
有一个表X：
name pre1 pre2 pre3
A 11 12 13
A 21 22 23
B 31 32 33
B 41 42 43
C 51 52 53
C 61 62 63
.....
.....
我希望能把其中的元素选出来放到另一个表Y中，Y只有一行一列：结构如下：
ALL
A-11-12-13-21-22-23
B-31-32-33-41-42-43
C-51-52-53-61-62-63
.....
x表可能有几千行，但是列数一定，在Y表中通过“-”和回车合在了一起，怎么实现呢？
create table x (
name varchar(10),
pre1 varchar(10),
pre2 varchar(10),
pre3 varchar(10)
)
insert x
select \'A\', \'11\', \'12\', \'13\' union all
select \'A\', \'21\', \'22\', \'23\' union all
select \'B\', \'31\', \'32\', \'33\' union all
select \'B\', \'41\', \'42\', \'43\' union all
select \'C\', \'51\', \'52\', \'53\' union all
select \'C\', \'61\', \'62\', \'63\'
go
create function fn_all(
@name varchar(20)
)
returns varchar(300)
as
begin
declare @r varchar(300)
set @r=@name
select @r=@r+\'-\'+ pre1 + \'-\' + pre2 + \'-\' + pre3 from x where name=@name
return @r
end
go
--调用
select dbo.fn_all(name) as [All] from x
group by name
--删除环境
drop function dbo.fn_all
go
drop table x
go
All
-------------------
A-11-12-13-21-22-23
B-31-32-33-41-42-43
C-51-52-53-61-62-63
select * from #a where address = \'aaaa\' COLLATE Chinese_PRC_CS_AS
select * from #a where address = \'aAAa\' COLLATE Chinese_PRC_CS_AS
drop table #a
id address
---------- ----------
1 aaaa
（所影响的行数为 1 行）
id address
---------- ----------
1 aAAa
（所影响的行数为 1 行）
如何查只以大写AB开头的呢？
通常情况下写select * from 表名 where 列名 like \'AB%\'
但是这样，以小写ab开头的纪录也会被查找出来 如何查只以大写AB开头的呢？
select * from table where left(col,2) = \'AB%\' COLLATE Chinese_PRC_CS_AS
select * from table where col like \'AB%\' COLLATE Chinese_PRC_CS_AS作者: 潇洒老乌龟 时间: 2006-10-19 09:33
0015--在top后面使用变量
create table a([id] [int])
insert into a(id) values(1)
insert into a(id) values(2)
insert into a(id) values(3)
insert into a(id) values(4)
insert into a(id) values(5)
declare @num as int
declare @sql as varchar(2000)
set @num = 2
set @sql = \'select top \' + cast(@num as char) + \' * from a\'
exec(@sql)
drop table a
id
-----------
1
2作者: 潇洒老乌龟 时间: 2006-10-19 09:33
0016--用SQL计算当月的天数
1.先建一个与你要恢复的数据库名称一样的数据库。
2.停止sql server,把你的数据库替换这个数据库。
3.重启sql server,把数据库设置为紧急状态。
sp_configure \'allow\',1
reconfigure with overirde
update sysdatabases set status=32768 where name=\'yourdata\'
4.重建日志文件。
dbcc rebuild_log(\'yourdata\',\'your data path\\newdata_log.ldf\')
5.取消紧急模式。
update sysdatabases set status=0 where name=\'yourdata\'
restore sysdatabases yourdata with recovery
sp_configure \'allow\',0
reconfigure with override
6.重起sql server
7.ok作者: 潇洒老乌龟 时间: 2006-10-19 09:35
0020--取n到m条记录的语句
1.
select top m * from tablename where id not in (select top n * from tablename)
2.
select top m * into 临时表(或表变量) from tablename order by columnname -- 将top m笔插入
set rowcount n
select * from 表变量 order by columnname desc
3.
select top n * from
(select top m * from tablename order by columnname) a
order by columnname desc
4.如果tablename里没有其他identity列，那么：
select identity(int) id0,* into #temp from tablename
取n到m条的语句为：
select * from #temp where id0 >=n and id0 <= m
如果你在执行select identity(int) id0,* into #temp from tablename这条语句的时候报错,那是因为你的DB中间的select into/bulkcopy属性没有打开要先执行：
exec sp_dboption 你的DB名字,\'select into/bulkcopy\',true
5.如果表里有identity属性，那么简单：
select * from tablename where identitycol between n and m作者: 潇洒老乌龟 时间: 2006-10-19 09:37
0021--求group by后的sum以及第一条记录
id t1 t2 t3
a1 1 x1 aa
a1 2 b2 1b
a2 3 c3 3c
a2 4 q4 1d
group by id , t1求sum t2 t3求top 1
结果
a1 3 x1 aa
a2 7 c3 3c
create table test (id varchar(10),t1 int,t2 varchar(10),t3 varchar(10))
go
insert into test
select \'a1\' , 1 , \'x1\' , \'aa\' union
select \'a1\' , 2 , \'b2\' , \'1b\' union
select \'a2\' , 3 , \'c3\' , \'3c\' union
select \'a2\' , 4 , \'q4\' , \'1d\'
select id,sum(t1) t1 ,
(select top 1 t2 from test B where B.Id=A.ID) t2 ,
(select top 1 t3 from test B where B.Id=A.ID) t3
from test A group by id
drop table test
id t1 t2 t3
---------- ----------- ---------- ----------
a1 3 x1 aa
a2 7 c3 3c
（所影响的行数为 2 行）
--使用如下一段语句也行。
select id ,
t1 = (select sum(t1) from test c where id=b.id),
t2,t3
from test b
where t1=(select top 1 t1 from test where id=b.id)作者: 潇洒老乌龟 时间: 2006-10-19 09:37
0022--实现193.5转为一百玖拾叁元五角的存储过程
/********************************************************
作者：(birdie_7761@cmmail.com)
版本：1.0
创建时间：20020227
修改时间：
功能：小写金额转换成大写
参数：n_LowerMoney 小写金额
v_TransType 种类 -- 1: directly translate, 0: read it in words
输出：大写金额
********************************************************/
if exists (select * from dbo.sysobjects where id = object_id(N\'[dbo].[L2U]\') and xtype in (N\'FN\', N\'IF\', N\'TF\'))
drop function [dbo].[L2U]
GO
CREATE FUNCTION dbo.L2U(@n_LowerMoney numeric(15,2),@v_TransType int)
RETURNS VARCHAR(200) AS
BEGIN
Declare @v_LowerStr VARCHAR(200) -- 小写金额
Declare @v_UpperPart VARCHAR(200)
Declare @v_UpperStr VARCHAR(200) -- 大写金额
Declare @i_I int
set @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2))) --四舍五入为指定的精度并删除数据左右空格
set @i_I = 1
set @v_UpperStr = \'\'
while ( @i_I <= len(@v_LowerStr))
begin
select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)
WHEN \'.\' THEN \'元\'
WHEN \'0\' THEN \'零\'
WHEN \'1\' THEN \'壹\'
WHEN \'2\' THEN \'贰\'
WHEN \'3\' THEN \'叁\'
WHEN \'4\' THEN \'肆\'
WHEN \'5\' THEN \'伍\'
WHEN \'6\' THEN \'陆\'
WHEN \'7\' THEN \'柒\'
WHEN \'8\' THEN \'捌\'
WHEN \'9\' THEN \'玖\'
END
+
case @i_I
WHEN 1 THEN \'分\'
WHEN 2 THEN \'角\'
WHEN 3 THEN \'\'
WHEN 4 THEN \'\'
WHEN 5 THEN \'拾\'
WHEN 6 THEN \'佰\'
WHEN 7 THEN \'仟\'
WHEN 8 THEN \'万\'
WHEN 9 THEN \'拾\'
WHEN 10 THEN \'佰\'
WHEN 11 THEN \'仟\'
WHEN 12 THEN \'亿\'
WHEN 13 THEN \'拾\'
WHEN 14 THEN \'佰\'
WHEN 15 THEN \'仟\'
WHEN 16 THEN \'万\'
ELSE \'\'
END
set @v_UpperStr = @v_UpperPart + @v_UpperStr
set @i_I = @i_I + 1
end
if ( 0 = @v_TransType)
begin
set @v_UpperStr = REPLACE(@v_UpperStr,\'零拾\',\'零\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零佰\',\'零\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零仟\',\'零\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零零零\',\'零\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零零\',\'零\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零角零分\',\'整\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零分\',\'整\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零角\',\'零\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零亿零万零元\',\'亿元\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'亿零万零元\',\'亿元\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零亿零万\',\'亿\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零万零元\',\'万元\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'万零元\',\'万元\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零亿\',\'亿\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零万\',\'万\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零元\',\'元\')
set @v_UpperStr = REPLACE(@v_UpperStr,\'零零\',\'零\')
end
-- 对壹元以下的金额的处理
if ( \'元\' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if ( \'零\' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if ( \'角\' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if ( \'分\' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if (\'整\' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = \'零元整\'
end
return @v_UpperStr
END
GO
while ( @i_I <= len(@v_LowerStr))
begin
select @v_UpperPart = case substring(@v_LowerStr,len(@v_LowerStr) - @i_I + 1,1)
WHEN \'.\' THEN \'元\'
WHEN \'0\' THEN \'零\'
WHEN \'1\' THEN \'壹\'
WHEN \'2\' THEN \'贰\'
WHEN \'3\' THEN \'叁\'
WHEN \'4\' THEN \'肆\'
WHEN \'5\' THEN \'伍\'
WHEN \'6\' THEN \'陆\'
WHEN \'7\' THEN \'柒\'
WHEN \'8\' THEN \'捌\'
WHEN \'9\' THEN \'玖\'
END
+
case @i_I
WHEN 1 THEN \'分\'
WHEN 2 THEN \'角\'
WHEN 3 THEN \'\'
WHEN 4 THEN \'\'
WHEN 5 THEN \'拾\'
WHEN 6 THEN \'佰\'
WHEN 7 THEN \'仟\'
WHEN 8 THEN \'万\'
WHEN 9 THEN \'拾\'
WHEN 10 THEN \'佰\'
WHEN 11 THEN \'仟\'
WHEN 12 THEN \'亿\'
WHEN 13 THEN \'拾\'
WHEN 14 THEN \'佰\'
WHEN 15 THEN \'仟\'
WHEN 16 THEN \'万\'
ELSE\'\'\'\'
END
select @v_UpperStr = @v_UpperPart + @v_UpperStr
select @i_I = @i_I + 1
end
-- 对壹元以下的金额的处理
if ( substring(@v_UpperStr,1,1)=\'元\' )
begin
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if (substring(@v_UpperStr,1,1)= \'零\')
begin
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if (substring(@v_UpperStr,1,1)=\'角\')
begin
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if ( substring(@v_UpperStr,1,1)=\'分\')
begin
select @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if (substring(@v_UpperStr,1,1)=\'整\')
begin
select @v_UpperStr = \'零元整\'
end
select @ret=@v_UpperStr
GO
--调用过程：
declare @ret varchar(200)
exec L2U 567983.897,1,@ret output
select @ret
CREATE FUNCTION [dbo].[f_num_chn] (@num numeric(14,2))
RETURNS varchar(100) WITH ENCRYPTION
AS
BEGIN
--版权所有：pbsql
DECLARE @n_data VARCHAR(20),@c_data VARCHAR(100),@n_str VARCHAR(10),@i int
SET @n_data=RIGHT(SPACE(14)+CAST(CAST(ABS(@num*100) AS bigint) AS varchar(20)),14)
SET @c_data=\'\'
SET @i=1
WHILE @i<=14
BEGIN
SET @n_str=SUBSTRING(@n_data,@i,1)
IF @n_str<>\' \'
BEGIN
IF not ((SUBSTRING(@n_data,@i,2)=\'00\') or
((@n_str=\'0\') and ((@i=4) or (@i= or (@i=12) or (@i=14))))
SET @c_data=@c_data+SUBSTRING(\'零壹贰叁肆伍陆柒捌玖\',CAST(@n_str AS int)+1,1)
IF not ((@n_str=\'0\') and (@i<>4) and (@i<> and (@i<>12))
SET @c_data=@c_data+SUBSTRING(\'仟佰拾亿仟佰拾万仟佰拾圆角分\',@i,1)
IF SUBSTRING(@c_data,LEN(@c_data)-1,2)=\'亿万\'
SET @c_data=SUBSTRING(@c_data,1,LEN(@c_data)-1)
END
SET @i=@i+1
END
IF @num<0
SET @c_data=\'（负数）\'+@c_data
IF @num=0
SET @c_data=\'零圆\'
IF @n_str=\'0\'
SET @c_data=@c_data+\'整\'
RETURN(@c_data)
END
GO作者: 潇洒老乌龟 时间: 2006-10-19 09:38
0023--按表中ID分类统计再合并表数据
Food表中我加了一列Counter,这一列的值应该是在另外一个表中UserFood中Food.ID出现的次数,请问这个sql如何写.
我用这个select [ID],IsNULL((Select count(1) from UserFood where UserFood.FoodID = Food.ID),0) from Food,可以等到想要的值,但是不知道如何插入到Food表中.谢谢!!!
create table food (id char(1),foodname varchar(10),counter int)
insert into food
select \'1\' , \'第一个\' , null union
select \'2\' , \'第二个\' , null union
select \'3\' , \'第三个\' , null
create table usefood (id char(1),foodid int)
insert into usefood
select \'1\' , 1 union
select \'2\' , 2 union
select \'3\' , 1 union
select \'4\' , 1
select a.id as id, a.foodname as foodname, isnull(b.counter, 0) as counter
from food as a left join (select foodid , count(*) as counter from usefood group by foodid) as b
on a.id = b.foodid
--select * from food
--select foodid , count(*) as counter from usefood group by foodid
insert into A(month,tbh,sfbs)
select left(month,4)+\'10\',tbh,sfbs
from A
where right(month,2)=\'09\'
where not exists (select 1 from A t where left(t.month,4)=left(a.month,4)
and a.tbh=t.tbh and a.sfbs=t.sfbs)作者: 潇洒老乌龟 时间: 2006-10-19 09:39
0026--行列转换
declare @sql varchar(4000)
set @sql = \'select Name as \' + \'姓名\'
select @sql = @sql + \' , sum(case Subject when \'\'\'+Subject+\'\'\' then Result end) [\'+Subject+\']\'
from (select distinct Subject from rowtocol) as a
set @sql = @sql + \' from rowtocol group by name\'
exec(@sql)
select 姓名 as Name,\'语文\' as Subject,语文 as Result from CJ union
select 姓名 as Name,\'数学\' as Subject,数学 as Result from CJ union
select 姓名 as Name,\'物理\' as Subject,物理 as Result from CJ
order by 姓名 desc
SQL语句之合并行列转换
有表rowtocol，其数据如下：
a b
1 1
1 2
1 3
2 1
2 2
3 1
如何转换成如下结果:
a b
1 1,2,3
2 1,2
3 1
创建一个合并的函数
create function f_rowtocol(@a int)
returns varchar(8000)
as
begin
declare @str varchar(8000)
set @str = \'\'
select @str = @str + \',\' + cast(b as varchar) from rowtocol where a = @a
set @str = right(@str , len(@str) - 1)
return(@str)
End
go
调用自定义函数得到结果：
select distinct a ,dbo.f_rowtocol(a) from rowtocol
SELECT id as \'人员\' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS \'1月\' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS \'2月\' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS \'3月\' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS \'4月\' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS \'5月\' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS \'6月\' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS \'7月\' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS \'8月\' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS \'9月\' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS \'10月\' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS \'11月\' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS \'12月\'
FROM a
GROUP BY ID
第二个：
SELECT id as \'人员\' ,
SUM(CASE month WHEN 1 THEN data ELSE 0 END) AS \'1月\' ,
SUM(CASE month WHEN 2 THEN data ELSE 0 END) AS \'2月\' ,
SUM(CASE month WHEN 3 THEN data ELSE 0 END) AS \'3月\' ,
SUM(CASE month WHEN 4 THEN data ELSE 0 END) AS \'4月\' ,
SUM(CASE month WHEN 5 THEN data ELSE 0 END) AS \'5月\' ,
SUM(CASE month WHEN 6 THEN data ELSE 0 END) AS \'6月\' ,
SUM(CASE month WHEN 7 THEN data ELSE 0 END) AS \'7月\' ,
SUM(CASE month WHEN 8 THEN data ELSE 0 END) AS \'8月\' ,
SUM(CASE month WHEN 9 THEN data ELSE 0 END) AS \'9月\' ,
SUM(CASE month WHEN 10 THEN data ELSE 0 END) AS \'10月\' ,
SUM(CASE month WHEN 11 THEN data ELSE 0 END) AS \'11月\' ,
SUM(CASE month WHEN 12 THEN data ELSE 0 END) AS \'12月\' ,
SUM(data) as \'合计\'
FROM a
GROUP BY ID
create table test(店面 varchar(10),产品 varchar(10),金额 int)
insert into test select \'A店\',\'产品1\',100
insert into test select \'A店\',\'产品2\',100
insert into test select \'A店\',\'产品3\',100
insert into test select \'B店\',\'产品1\',200
insert into test select \'B店\',\'产品3\',200
go
declare @sql varchar(8000)
set @sql=\'select 店面\'
select @sql=@sql+\',\'+产品+\'=sum(case 产品 when \'\'\'+产品+\'\'\' then 金额 else 0 end)\'
from test group by 产品 order by 产品
set @sql=@sql+\',总计=sum(金额) from test group by 店面\'
exec(@sql)
go
--生成测试数据
create table test1(A varchar(20),b int,c int,d int,e int)
insert into test1 select \'x\',1,2 ,3 ,4
insert into test1 select \'y\',5,6 ,7 ,8
insert into test1 select \'z\',9,10,11,12
--生成中间数据表
declare @s varchar(8000)
set @s=\'create table test2(a varchar(20)\'
select @s=@s+\',\'+A+\' varchar(10)\' from test1
set @s=@s+\')\'
exec(@s)
--借助中间表实现行列转换
declare @name varchar(20)
declare t_cursor cursor for
select name from syscolumns
where id=object_id(\'test1\') and colid>1 order by colid
open t_cursor
fetch next from t_cursor into @name
while @@fetch_status=0
begin
exec(\'select \'+@name+\' as t into test3 from test1\')
set @s=\'insert into test2 select \'\'\'+@name+\'\'\'\'
select @s=@s+\',\'\'\'+rtrim(t)+\'\'\'\' from test3
exec(@s)
exec(\'drop table test3\')
fetch next from t_cursor into @name
end
close t_cursor
deallocate t_cursor
--查看行列互换处理结果
select * from test2
/*
a x y z
---- ---- ---- ----
b 1 5 9
c 2 6 10
d 3 7 11
e 4 8 12
*/
--删除测试数据
drop table test1,test2作者: 潇洒老乌龟 时间: 2006-10-19 09:39
0027--按日期统计的交叉表
create table test(日期 datetime, 用户ID varchar(10) , 数量 int)
insert into test select \'2006.9.20\',\'8\' ,20
insert into test select \'2006.9.1\' ,\'10\',20
insert into test select \'2006.9.18\',\'1\' ,20
insert into test select \'2006.9.1\' ,\'5\' ,20
insert into test select \'2006.9.13\',\'1\' ,20
insert into test select \'2006.9.14\',\'3\' ,20
insert into test select \'2006.9.14\',\'2\' ,20
insert into test select \'2006.9.20\',\'7\' ,20
insert into test select \'2006.9.10\',\'6\' ,20
insert into test select \'2006.9.20\',\'1\' ,20
go
declare @sql varchar(8000)
set @sql=\'\'
select @sql=@sql+\',[用户ID\'+rtrim(用户ID)+\']=sum(case 用户ID when \'+rtrim(用户ID)+\' then 数量 else 0 end)\' from test group by 用户ID order by 用户ID
set @sql=\'select datepart(dd,日期) as 日期\' +@sql+\' from test group by datepart(dd,日期) order by datepart(dd,日期)\'
写个简单的(一天只打卡二次,上班一次,下班一次):
declare @t table(姓名 char(10),员工编号 int,时间 datetime,方向 bit)
insert @t
select \'a\',1,\'2006-09-20 08:00:00\',0 union all
select \'a\',1,\'2006-09-20 16:00:00\',1 union all
select \'b\',2,\'2006-09-20 08:00:00\',0 union all
select \'b\',2,\'2006-09-20 15:00:00\',1 union all
select \'c\',3,\'2006-09-20 08:00:00\',0 union all
select \'c\',3,\'2006-09-20 14:00:00\',1 union all
select \'a\',1,\'2006-09-21 08:00:00\',0 union all
select \'a\',1,\'2006-09-21 16:00:00\',1 union all
select \'b\',2,\'2006-09-21 08:00:00\',0 union all
select \'b\',2,\'2006-09-21 15:00:00\',1 union all
select \'c\',3,\'2006-09-21 08:00:00\',0 union all
select \'c\',3,\'2006-09-21 14:00:00\',1
--select * from @t
SELECT a.员工编号,a.姓名,
日期 = convert(varchar(10),a.时间,112),
工作时间 = datediff(hh,a.时间,b.时间)
FROM @t as a
INNER JOIN @t as b
ON a.员工编号 = b.员工编号 and a.方向 = 0 and b.方向 = 1
WHERE datediff(dd,a.时间,b.时间) = 0
/*结果
员工编号 姓名 日期 工作时间
1 a 20060920 8
2 b 20060920 7
3 c 20060920 6
1 a 20060921 8
2 b 20060921 7
3 c 20060921 6
*/作者: 潇洒老乌龟 时间: 2006-10-19 09:40
0029--按某一字段分类排序
有一表
a b c
7 aa 153
9 aa 152
6 aa 120
8 aa 168
5 bb 159
7 bb 179
8 bb 149
9 bb 139
6 bb 169
对b列中的值来分类排序并分别加一序号,形成一新表
px a b c
1 6 aa 120
2 9 aa 152
3 7 aa 153
4 8 aa 168
1 9 bb 139
2 8 bb 149
3 5 bb 159
4 6 bb 169
5 7 bb 179
一张表里面以两个字段为唯一字段，当几条记录的这两个字段完全相同时，需要删除重复项，如下表
a b c d
1 2 3 4
1 5 3 5
1 2 7 9
以a、b为唯一字段，第一条和第三条的a、b完全相同，所以，需要删除第一条记录1 2 3 4 或者第三条记录1 2 7 9
即如下结果：
a b c d
1 2 3 4
1 5 3 5
或
a b c d
1 5 3 5
1 2 7 9
请问各位大侠这种sql语句怎么写
CREATE TABLE Tb1(id int, [a] varchar(255), varchar(255), [c] varchar(255), [d] varchar(255))
INSERT Tb1(id, [a], , [c], [d])
SELECT 1, \'1\',\'2\',\'3\',\'4\'
UNION ALL SELECT 2, \'1\',\'5\',\'3\',\'5\'
UNION ALL SELECT 3, \'1\',\'2\',\'7\',\'9\'
UNION ALL SELECT 4, \'1\',\'4\',\'7\',\'6\'
delete Tb1 where [id] not in (select max([id]) from Tb1 group by a,b )
select * from tb1
drop table tb1
如果要同时删除第一和第三行
即如下结果：
a b c d
1 5 3 5
语句如下：
delete m from tb t
inner join
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
on m.a = n.a and m.b = n.b
或
delete * from tb as m,
(
select a ,b
from tb
group by a , b
having count(*)>1
)n
where m.a = n.a and m.b = n.b
------------------------------------------------------------------------------------
在几千条记录里,存在着些相同的记录,如何能用SQL语句,删除掉重复的呢?谢谢!
1、查找表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断
select * from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
2、删除表中多余的重复记录，重复记录是根据单个字段（peopleId）来判断，只留有rowid最小的记录
delete from people
where peopleId in (select peopleId from people group by peopleId having count(peopleId) > 1)
and rowid not in (select min(rowid) from people group by peopleId having count(peopleId )>1)
3、查找表中多余的重复记录（多个字段）
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
4、删除表中多余的重复记录（多个字段），只留有rowid最小的记录
delete from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
5、查找表中多余的重复记录（多个字段），不包含rowid最小的记录
select * from vitae a
where (a.peopleId,a.seq) in (select peopleId,seq from vitae group by peopleId,seq having count(*) > 1)
and rowid not in (select min(rowid) from vitae group by peopleId,seq having count(*)>1)
比方说在A表中存在一个字段“name”，而且不同记录之间的“name”值有可能会相同，
现在就是需要查询出在该表中的各记录之间，“name”值存在重复的项；
Select Name,Count(*) From A Group By Name Having Count(*) > 1
如果还查性别也相同大则如下:
Select Name,sex,Count(*) From A Group By Name,sex Having Count(*) > 1
------------------------------------------------------------------------------------------------
declare @max integer,@id integer
declare cur_rows cursor local for select 主字段,count(*) from 表名 group by 主字段 having count(*) >； 1
open cur_rows
fetch cur_rows into @id,@max
while @@fetch_status=0
begin
select @max = @max -1
set rowcount @max
delete from 表名 where 主字段 = @id
fetch cur_rows into @id,@max
end
close cur_rows
set rowcount 0
方法二
有两个意义上的重复记录，一是完全重复的记录，也即所有字段均重复的记录，二是部分关键字段重复的记录，比如Name字段重复，而其他字段不一定重复或都重复可以忽略。
1、对于第一种重复，比较容易解决，使用
select distinct * from tableName
就可以得到无重复记录的结果集。
如果该表需要删除重复的记录（重复记录保留1条），可以按以下方法删除
select distinct * into #Tmp from tableName
drop table tableName
select * into tableName from #Tmp
drop table #Tmp
发生这种重复的原因是表设计不周产生的，增加唯一索引列即可解决。
2、这类重复问题通常要求保留重复记录中的第一条记录，操作方法如下
假设有重复的字段为Name,Address，要求得到这两个字段唯一的结果集
select identity(int,1,1) as autoID, * into #Tmp from tableName
select min(autoID) as autoID into #Tmp2 from #Tmp group by Name,autoID
select * from #Tmp where autoID in(select autoID from #tmp2)
最后一个select即得到了Name，Address不重复的结果集（但多了一个autoID字段，实际写时可以写在select子句中省去此列）
select * from tablename where id in (
select id from tablename
group by id
having count(id) > 1)作者: 潇洒老乌龟 时间: 2006-10-19 09:41
0031--记录编号缺号的处理
找最小未使用id如下：
select (case when exists (select 1 from test where id=1)
then min(id+1) else 1 end)
from test
where id not in(select id-1 from test)
CREATE TABLE [bhb] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[bh] [int] NULL
) ON [PRIMARY]
Declare @intI int
Set @intI = 1
Declare @intChdI int
While @intI < 500 --根据实际需要来确定循环次数
Begin
if not EXISTS(select bh from twhere bh=@intI)
begin
Insert Into bhb(bh) Values (@intI)
end
Set @intI= @intI + 1
End
*/
Declare @intI int
set @intI=1
update t set bh=(select bh from bhb where id=@intI),@intI=@intI+1 where bh is null
drop table bhb
while exists (select 1 from tablename where 编号 is null)
begin
set rowcount 1
update tablename
set 编号=(select min(编号)+1 from tablename a where 编号 is not null
and not exists (select 1 from tablename where 编号=a.编号+1)
)
where 编号 is null
set rowcount 0
end
--检查结果
select * from tablename
order by 编号
假设你的表名为test1， 字段名分别为id int,name varchar(10)创建如下存储过程应该能够实现。
CREATE PROCEDURE Sp_AddID AS
BEGIN
DECLARE @id int, @name varchar(10)
DECLARE @currentID int
DECLARE @maxID int
SELECT @currentID=1
SELECT @maxID=MAX(id) FROM test1
DECLARE find_id CURSOR FOR
SELECT id,name FROM Test1
OPEN find_id
FETCH NEXT FROM find_id INTO @id,@name
WHILE @@fetch_status =0
BEGIN
IF @id IS NULL
BEGIN
IF @currentID > @maxID
BEGIN
UPDATE test1 SET id=@currentID WHERE name=@name
SELECT @currentID=@currentID+1
END
ELSE
BEGIN
DECLARE @currentName varchar(10)
DECLARE @flag int
SELECT @flag=0
WHILE @currentID < @maxID AND @flag=0
BEGIN
IF NOT EXISTS(SELECT name FROM test1 WHERE id=@currentID)
BEGIN
UPDATE test1 SET id=@currentID WHERE name=@name
SELECT @flag=1
END
SELECT @currentID=@currentID+1
END
END
END
FETCH NEXT FROM find_id INTO @id,@name
END
CLOSE find_id
DEALLOCATE find_id
END
GO作者: 潇洒老乌龟 时间: 2006-10-19 09:41
0032--分类不显示查询
CREATE TABLE t([编号] int, [客户] varchar(255), [订货产品] varchar(255))
INSERT t([编号], [客户], [订货产品])
SELECT 1, \'无锡\',\'钢材\'
UNION ALL SELECT 2, \'无锡\',\'汽配件\'
UNION ALL SELECT 3, \'无锡\',\'电线\'
UNION ALL SELECT 4, \'北京\',\'烤鸭\'
UNION ALL SELECT 5, \'北京\',\'湖南鱼\'
UNION ALL SELECT 6, \'重庆\',\'火锅\'
select
编号,
客户=case when exists(select 1 from t where 客户=a.客户 and 编号<a.编号) then \'\' else a.客户 end,
订货产品
from
t a
order by
a.编号
declare @sql varchar(4000)
set @sql = \'select 年份 \'
select @sql = @sql + \' , sum(case 站名 when \'\'\'+ 站名 +\'\'\' then 数据 end) [\'+ 站名 +\']\'
from (select distinct 站名 from #tab) as a
set @sql = @sql + \', avg(数据) as 平均 from #tab group by 年份\'
exec(@sql)
--另外的做法
declare @sql varchar(8000)
set @sql=\'select 年份\'
select @sql=@sql+\',[\'+站名+\']=max(case 站名 when \'\'\'+站名+\'\'\' then 数据 end)\' from 表 group by 站名 order by 站名
set @sql=@sql+\',avg(数据) as 平均 from 表 group by 年份\'
exec(@sql)
declare @name varchar(1000)
set @name=\'\'
select @name=@name+\',max(case when 站名=\'\'\'+站名+\'\'\' then 數據 end) [\'+站名+\']\' from test group by 站名 order by 站名
select @name=\'select identity(int,1,1)序號,年份\'+@name+\',cast(avg(數據) as decimal(15,1)) 平均 into #a from test group by 年份 order by 1 select * from #a\'
exec (@name)
/*
序號 年份 AA BB CC 平均
1 1986 23.5 21.0 21.8 22.1
2 1987 25.6 22.0 23.0 23.5
3 1988 23.0 22.7 21.8 22.5
*/作者: 潇洒老乌龟 时间: 2006-10-19 09:42
0033--行列转换和交叉表(使用sum或max两种方案解决)
if object_id(\'tempdb..#tmp\') is not null
drop table #tmp
GO
create table #tmp(workerid varchar(10),types varchar(10),moneys int)
insert #tmp
select \'0001\', \'g_zf\', 10 union all
select \'0002\', \'g_zf\', 12 union all
select \'0003\', \'g_zf\', 15 union all
select \'0001\', \'g_hsf\', 25 union all
select \'0001\', \'g_dhf\', 60 union all
select \'0002\', \'g_hsf\', 80
declare @sql varchar(4000)
set @sql = \'select workerid \'
select @sql = @sql + \' , sum(case types when \'\'\'+types+\'\'\' then moneys else 0 end) [\'+types+\']\'
from (select distinct types from #tmp) as a
set @sql = @sql + \' from #tmp group by workerid\'
exec(@sql)
--以下这段代码同样可以实现功能
declare @sql varchar(8000)
set @sql = \'\'
select @sql = @sql + \',[\' + types + \'] = max(case types when \'\'\' + types + \'\'\' then moneys else 0 end)\'
from #tmp group by types order by types desc
set @sql = \'select workerid\' + @sql + \' from #tmp group by workerid\'
EXEC(@sql)
set nocount on
if @OrderType=\'\'
begin
set @strOrder = \'\'
set @strTmp=\'\'
end
else if @OrderType = \'0\' -- 降序
begin
set @strTmp = @tblName + \'.\' + @priKeyName + \'>(select max([\' + @priKeyName + \']) from \'
set @strOrder = \' order by \' + @tblName + \'.\' + @priKeyName + \' asc\'
end
else if @OrderType = \'1\' -- 降序
begin
set @strTmp = @tblName + \'.\' + @priKeyName + \'<(select min([\' + @priKeyName + \']) from \'
set @strOrder = \' order by \' + @tblName + \'.\' + @priKeyName + \' desc\'
end
else -- 用户自定义排序规则
begin
set @strTmp = \'\'
set @strOrder = \' order by \' + @OrderType
end
set @strJoin = \' \' + @strJoin + \' \'
set @strNonResult = \'select \' + @fldNames + \' from \' + @tblName + @strJoin + \' where 1=2\'
if @strWhere = \'\' -- 如果没有额外的查询条件
begin
set @strTotal = N\'select @rowcount = count(*) from \' + @tblname
end
else
begin
set @strTotal = N\'select @rowcount = count(*) from \' + @tblname + \' where \' + @strWhere
end
-- 取得所有符合查询条件的记录数
if @PageIndex=1
begin
exec sp_executeSql @strTotal,N\'@rowcount int output\',@rowcount output
if @rowcount = 0
begin
exec sp_executeSql @strNonResult
return 0
end
end
else
set @rowcount=0
-- 执行查询，此时记录集不为空
-- 得到记录的页数，并调整页号，分页从1开始
if @PageIndex =1 -- 如果是第一页
begin
if @strWhere = \'\'
set @strByPage = N\'select top \' + cast(@PageSize as varchar) + \' \' + @fldNames + \' from \' + @tblName + @strJoin + @strOrder
else
set @strByPage = N\'select top \' + cast(@PageSize as varchar) + \' \' + @fldNames + \' from \' + @tblName + @strJoin + \' where \' + @strWhere + @strOrder
end
else -- 以后页
begin
if (@OrderType=\'0\' or @OrderType=\'1\') -- 按主键升序或降序
begin
if @strWhere = \'\'
set @strByPage = N\'select top \' + cast(@PageSize as varchar) + \' \' + @fldnames
+ \' from \' + @tblName
+ @strJoin
+ \' where \' + @strTmp
+ \' (select top \' + cast((@PageIndex-1) * @PageSize as varchar) + \' \' + @priKeyName
+ \' from \' + @tblName + @strOrder + \') as tmptbl)\'
+ @strOrder
else
set @strByPage = N\'select top \' + cast(@PageSize as varchar) + \' \' + @fldnames
+ \' from \' + @tblName
+ @strJoin
+ \' where \' + @strTmp
+ \' (select top \'+ cast((@PageIndex-1) * @PageSize as varchar) + \' \' + @priKeyName
+ \' from \' + @tblName + \' where \' + @strWhere + @strOrder + \') as tmptbl)\'
+ \' and \' + @strWhere
+ @strOrder
end
else -- 没有排序规则或者用户自定义规则
begin
if @strWhere = \'\'
set @strByPage = N\'select top \' + cast(@PageSize as varchar) + \' \' + @fldnames
+ \' from \' + @tblName
+ @strJoin
+ \' where not exists (select * from \'
+ \' (select top \' + cast((@PageIndex-1) * @PageSize as varchar) + \' * from \'
+ @tblName + @strorder + \') as tmpTable \'
+ \' where tmpTable.\' + @priKeyName + \' = \' + @tblName +\'.\' + @priKeyName + \')\'
+ @strorder
else
set @strByPage = N\'select top \' + cast(@PageSize as varchar) + \' \' + @fldnames
+ \' from \' + @tblName
+ @strJoin
+ \' where not exists (select * from \'
+ \' (select top \' + cast((@PageIndex-1) * @PageSize as varchar) + \' * from \'
+ @tblName + \' where \' + @strWhere + @strorder + \') as tmpTable \'
+ \' where tmpTable.\' + @priKeyName + \' = \' + @tblName + \'.\' + @priKeyName + \')\'
+ \' and \' + @strWhere
+ @strorder
end
end
exec sp_executeSql @strByPage
return @rowcount
set nocount off
方案二利用SQL的游标存储过程分页)
ALTER procedure [dbo].[selPagesByCursor]
@PageIndex int,--第N页
@PageSize int,--每页行数
@sqlstr nvarchar(4000)
as
set nocount on
declare @P1 int,--P1是游标的id
@rowcount int
exec sp_cursoropen @P1 output,@sqlstr,@scrollopt=1,@ccopt=1,@rowcount=@rowcount output
--select @rowcount as 总行数,ceiling(1.0*@rowcount/@pagesize) as 页数,@PageIndex as 当前页
set @PageIndex=(@PageIndex-1)*@PageSize+1
exec sp_cursorfetch @P1,16,@PageIndex,@PageSize
exec sp_cursorclose @P1
return @rowcount
set nocount off
方案三结合通用表达式进行分页)
ALTER procedure [dbo].[selTestTable]
@PageIndex int,
@PageSize int
as
Declare @rowcount numeric
Declare @intStart numeric
set nocount on
set @intStart=(@PageIndex-1)*@PageSize+1
if @intStart=1
SELECT @rowcount=count(ID) from TestTable where FirstName like \'%aa%\'
else
set @rowcount=0
;
WITH PartsCTE AS(Select ROW_NUMBER() OVER(order by ID) as row,
*
From TestTable where FirstName like \'%aa%\'
)
Select *
From PartsCTE A
where row between @intStart and @intStart+@PageSize-1
return @rowcount
set nocount off
if object_id(\'tempdb..#tmp\') is not null
drop table #tmp
GO
create table #tmp(a int,b int,c int , d int)
insert #tmp
select 1,0 ,0 ,10 union all
select 2,10,5 ,null union all
select 3,20,10,null union all
select 4,30,15,null
update #tmp
set d=(select isnull(sum(b-c),0)
from #tmp TT
where TT.a<=#tmp.a)+(select d from #tmp where a=1)
select * from #tmp
drop table #tmp
a b c d
----------- ----------- ----------- -----------
1 0 0 10
2 10 5 15
3 20 10 25
4 30 15 40
if object_id(\'tempdb..#tab\') is not null
drop table #tab
GO
create table #tab (
[id] [char](10),[age] [int],[rdate] [datetime])
insert into #tab(id,age,rdate) values(\'a\' , 1 , \'2006-01-01\')
insert into #tab(id,age,rdate) values(\'a\' , 2 , \'2006-01-02\')
insert into #tab(id,age,rdate) values(\'a\' , 3 , \'2006-01-03\')
insert into #tab(id,age,rdate) values(\'a\' , 4 , \'2006-01-04\')
insert into #tab(id,age,rdate) values(\'a\' , 5 , \'2006-01-05\')
insert into #tab(id,age,rdate) values(\'a\' , 6 , \'2006-01-06\')
insert into #tab(id,age,rdate) values(\'b\' , 1 , \'2006-02-01\')
insert into #tab(id,age,rdate) values(\'b\' , 2 , \'2006-02-02\')
insert into #tab(id,age,rdate) values(\'b\' , 3 , \'2006-02-03\')
insert into #tab(id,age,rdate) values(\'b\' , 4 , \'2006-02-04\')
insert into #tab(id,age,rdate) values(\'c\' , 1 , \'2006-03-01\')
insert into #tab(id,age,rdate) values(\'c\' , 2 , \'2006-03-02\')
insert into #tab(id,age,rdate) values(\'c\' , 3 , \'2006-03-03\')
insert into #tab(id,age,rdate) values(\'d\' , 1 , \'2006-04-01\')
insert into #tab(id,age,rdate) values(\'d\' , 2 , \'2006-04-02\')
insert into #tab(id,age,rdate) values(\'e\' , 1 , \'2006-05-01\')
--按时间rdate升序取前三条
select * from #tab t
where rdate in
(
select top 3 rdate from #tab where id=t.id order by rdate
)
id age rdate
---------- ----------- ------------------------------------------------------
a 1 2006-01-01 00:00:00.000
a 2 2006-01-02 00:00:00.000
a 3 2006-01-03 00:00:00.000
b 1 2006-02-01 00:00:00.000
b 2 2006-02-02 00:00:00.000
b 3 2006-02-03 00:00:00.000
c 1 2006-03-01 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
d 1 2006-04-01 00:00:00.000
d 2 2006-04-02 00:00:00.000
e 1 2006-05-01 00:00:00.000
(所影响的行数为 12 行)
--按时间rdate降序取前三条
select * from #tab t
where rdate in
(
select top 3 rdate from #tab where id=t.id order by rdate desc
)
order by id , rdate desc
id age rdate
---------- ----------- ------------------------------------------------------
a 6 2006-01-06 00:00:00.000
a 5 2006-01-05 00:00:00.000
a 4 2006-01-04 00:00:00.000
b 4 2006-02-04 00:00:00.000
b 3 2006-02-03 00:00:00.000
b 2 2006-02-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 1 2006-03-01 00:00:00.000
d 2 2006-04-02 00:00:00.000
d 1 2006-04-01 00:00:00.000
e 1 2006-05-01 00:00:00.000
(所影响的行数为 12 行)
--上面包含了总数不到3个的记录(即id为d,e的数据)，如果要取消它们，以升序为例(降序同理)
select * from #tab m
where rdate in
(
select top 3 rdate from
(
select * from #tab t
where id in
(
select id from #tab group by id having(count(*)) >= 3
)
) n
where m.id = n.id order by rdate
)
id age rdate
---------- ----------- ------------------------------------------------------
a 1 2006-01-01 00:00:00.000
a 2 2006-01-02 00:00:00.000
a 3 2006-01-03 00:00:00.000
b 1 2006-02-01 00:00:00.000
b 2 2006-02-02 00:00:00.000
b 3 2006-02-03 00:00:00.000
c 1 2006-03-01 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
(所影响的行数为 9 行)
--在上面的例中我们发现rdate都是不相同的，如果相同怎么办？
--例如id=a，第三条，第四条rdate相同，都为2006-01-03。
id age rdate
---------- ----------- ------------------------------------------------------
a 1 2006-01-01 00:00:00.000
a 2 2006-01-02 00:00:00.000
a 3 2006-01-03 00:00:00.000
a 4 2006-01-03 00:00:00.000
a 5 2006-01-05 00:00:00.000
a 6 2006-01-06 00:00:00.000
b 1 2006-02-01 00:00:00.000
b 2 2006-02-02 00:00:00.000
b 3 2006-02-03 00:00:00.000
b 4 2006-02-04 00:00:00.000
c 1 2006-03-01 00:00:00.000
c 2 2006-03-02 00:00:00.000
c 3 2006-03-03 00:00:00.000
d 1 2006-04-01 00:00:00.000
d 2 2006-04-02 00:00:00.000
e 1 2006-05-01 00:00:00.000
--如果想把第三、四都取出来，使用上面的语句即可。如果只取一条(只取第三不取第四)则要使用临时表了。
if object_id(\'tempdb..#tab\') is not null
drop table #tab
GO
if object_id(\'tempdb..#temp\') is not null
drop table #temp
GO
create table #tab (
[id] [char](10),[age] [int],[rdate] [datetime])
insert into #tab(id,age,rdate) values(\'a\' , 1 , \'2006-01-01\')
insert into #tab(id,age,rdate) values(\'a\' , 2 , \'2006-01-02\')
insert into #tab(id,age,rdate) values(\'a\' , 3 , \'2006-01-03\')
insert into #tab(id,age,rdate) values(\'a\' , 4 , \'2006-01-03\')
insert into #tab(id,age,rdate) values(\'a\' , 5 , \'2006-01-05\')
insert into #tab(id,age,rdate) values(\'a\' , 6 , \'2006-01-06\')
insert into #tab(id,age,rdate) values(\'b\' , 1 , \'2006-02-01\')
insert into #tab(id,age,rdate) values(\'b\' , 2 , \'2006-02-02\')
insert into #tab(id,age,rdate) values(\'b\' , 3 , \'2006-02-03\')
insert into #tab(id,age,rdate) values(\'b\' , 4 , \'2006-02-04\')
insert into #tab(id,age,rdate) values(\'c\' , 1 , \'2006-03-01\')
insert into #tab(id,age,rdate) values(\'c\' , 2 , \'2006-03-02\')
insert into #tab(id,age,rdate) values(\'c\' , 3 , \'2006-03-03\')
insert into #tab(id,age,rdate) values(\'d\' , 1 , \'2006-04-01\')
insert into #tab(id,age,rdate) values(\'d\' , 2 , \'2006-04-02\')
insert into #tab(id,age,rdate) values(\'e\' , 1 , \'2006-05-01\')
--按时间rdate升序取前三条(其他方法同上)
select id1=identity(int,1,1),* into #temp from #tab order by id , rdate /*(降序用rdate desc)*/
select * from #temp t
where id1 in
(
select top 3 id1 from #temp where id=t.id order by id1
)作者: 潇洒老乌龟 时间: 2006-10-19 09:44
0038--同时求最高值和最低值
if object_id(\'tempdb..#商品表\') is not null
drop table #商品表
GO
create table #商品表 ([商品ID] [char](1) , [名称] [char](4))
insert into #商品表(商品ID,名称) values(\'1\' , \'牙膏\')
insert into #商品表(商品ID,名称) values(\'2\' , \'牙刷\')
--select * from #商品表
if object_id(\'tempdb..#供货商表\') is not null
drop table #供货商表
GO
create table #供货商表([供货商ID] [char](1),[商品ID] [char](1) , [价格] [decimal](18,1))
insert into #供货商表(供货商ID,商品ID,价格) values(\'1\' , \'1\' , \'2.0\')
insert into #供货商表(供货商ID,商品ID,价格) values(\'2\' , \'1\' , \'1.8\')
insert into #供货商表(供货商ID,商品ID,价格) values(\'3\' , \'1\' , \'2.1\')
insert into #供货商表(供货商ID,商品ID,价格) values(\'4\' , \'2\' , \'1.0\')
insert into #供货商表(供货商ID,商品ID,价格) values(\'5\' , \'2\' , \'0.8\')
insert into #供货商表(供货商ID,商品ID,价格) values(\'6\' , \'2\' , \'1.0\')
insert into #供货商表(供货商ID,商品ID,价格) values(\'7\' , \'2\' , \'1.1\')
select a.商品ID , a.名称 ,
b.价格 as 最高价 , b.供货商ID as 供货商,
c.价格 as 最低价 , c.供货商ID as 供货商
from #商品表 as a ,
(select m.供货商ID , n.商品ID , n.价格 from #供货商表 m, (select 商品ID , max(价格) as 价格 from #供货商表 group by 商品ID) as n where m.商品ID = m.商品ID and m.价格 = n.价格) b ,
(select p.供货商ID , q.商品ID , q.价格 from #供货商表 p, (select 商品ID , min(价格) as 价格 from #供货商表 group by 商品ID) as q where p.商品ID = q.商品ID and p.价格 = q.价格) c
where a.商品ID = b.商品ID and a.商品ID = c.商品ID
The acc_grp_cust_tbl table is responsible to store the customer ID, and the cust_tbl table is responsible to store customer personal information such as name, address, etc… Please write a SQL query in order to provide following result.
ACC_GROUP PAYEENAMES
1400 Ma Martin/Cheung Kris/Chan Ricky
1500 Kwan Tom/Corporate Default Corporate Default
1600 Mok Mary
要注意PAYEENAMES显示的结果方式
请问Answer： 是什么？
if object_id(\'tempdb..#cust_tbl\') is not null
drop table #cust_tbl
GO
create table #cust_tbl([cust_id] [char](1) , [e_first_name] [varchar](100) , [e_last_name] [varchar](100))
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'0\',\'Martin\',\'Ma\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'1\',\'Kirs\',\'Cheung\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'2\',\'Ricky\',\'Chan\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'3\',\'Tom\',\'Kwan\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'4\',\'Corporate Default Corporate Default \',\'\')
insert into #cust_tbl(cust_id,e_first_name,e_last_name) values(\'5\',\'Mary\',\'Mok\')
if object_id(\'tempdb..#a\') is not null
drop table #a
GO
select cust_id , e_last_name + \' \' + e_first_name as PAYEENAMES into #a from #cust_tbl
if object_id(\'tempdb..#acc_grp_cust_tbl\') is not null
drop table #acc_grp_cust_tbl
GO
create table #acc_grp_cust_tbl([acc_group] [char](4) , [Cust_id1] [char](1) , [Cust_id2] [char](1) , [Cust_id3] [char](1))
insert into #acc_grp_cust_tbl(acc_group,Cust_id1,Cust_id2,Cust_id3) values(\'1400\',\'0\',\'1\',\'2\')
insert into #acc_grp_cust_tbl(acc_group,Cust_id1,Cust_id2,Cust_id3) values(\'1500\',\'3\',\'4\',\'\')
insert into #acc_grp_cust_tbl(acc_group,Cust_id1,Cust_id2,Cust_id3) values(\'1600\',\'5\',\'\',\'\')
if object_id(\'tempdb..#b\') is not null
drop table #b
GO
select * into #b from
(
select acc_group,Cust_id1 as Cust_id from #acc_grp_cust_tbl where Cust_id1 <> \'\'
union all
select acc_group,Cust_id2 as Cust_id from #acc_grp_cust_tbl where Cust_id2 <> \'\'
union all
select acc_group,Cust_id3 as Cust_id from #acc_grp_cust_tbl where Cust_id3 <> \'\'
)t
if object_id(\'tempdb..tb\') is not null
drop table tb
GO
select #b.acc_group ,#a.PAYEENAMES into tb from #a,#b where #a.cust_id = #b.cust_id
if object_id(\'tempdb..fn_合并\') is not null drop function fn_合并
go
create function fn_合并(
@acc_group varchar(20)
)
returns varchar(300)
as
begin
declare @r varchar(300)
set @r=\'\'
select top 3 @r=@r+\'/\'+PAYEENAMES from tb where acc_group=@acc_group
if @r<>\'\'
set @r=stuff(@r,1,1,\'\')
return @r
end
go
--调用
select acc_group,dbo.fn_合并(acc_group) as PAYEENAMES from tb group by acc_group
acc_group PAYEENAMES
--------- -------------
1400 Ma Martin/Cheung Kirs/Chan Ricky
1500 Kwan Tom/ Corporate Default Corporate Default
1600 Mok Mary
select a+10*b+1 as n
from (
select 0 as a
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
) as t1,(
select 0 as b
union all select 1
union all select 2
union all select 3
union all select 4
union all select 5
union all select 6
union all select 7
union all select 8
union all select 9
) as t2
order by n作者: 潇洒老乌龟 时间: 2006-10-26 00:03
0041 -- 怎麼樣用SQL語句把TXT文檔導入到數據庫
我現在TXT文檔的格式為:
1720061016000057208
1720061016000057208
2620061016000054221
2620061016000053042
2220061016000154955
2620061016000154083
--显示不同条件的分组结果记录集
--建立测试环境
create table #a(dw varchar(20),sl int,bz int)
insert into #a
select \'1001\',1,0 union all
select \'1002\',25,2 union all
select \'1003\',5,1 union all
select \'1001\',20,1 union all
select \'1002\',10,3
create table #b(dw varchar(20),mc nvarchar(100))
insert into #b
select \'1001\',\'公司a\' union all
select \'1002\',\'公司b\' union all
select \'1003\',\'公司c\' union all
select \'1004\',\'公司d\' union all
select \'1005\',\'公司e\'
--测试
select B.mc
,isnull((select sum(sl) tzs from #a A where A.dw=B.dw group by dw),0) \'sl\'
,isnull((select sum(sl) tzs from #a A where bz=0 and A.dw=B.dw group by dw),0) \'tzs(bz=0)\'
,isnull((select sum(sl) tzs from #a A where bz=1 and A.dw=B.dw group by dw),0) \'tzs(bz=1)\'
,isnull((select sum(sl) tzs from #a A where bz=2 and A.dw=B.dw group by dw),0) \'tzs(bz=2)\'
,isnull((select sum(sl) tzs from #a A where bz=3 and A.dw=B.dw group by dw),0) \'tzs(bz=3)\'
from #b B
--显示结果
/*
dw sl tzs(bz=0) zs(bz=1) jzzs(bz=2) jjbg(bz=3)
公司a 21 1 1 0 0
公司b 35 0 0 1 1
公司c 5 0 1 0 0
公司d 0 0 0 0 0
公司e 0 0 0 0 0
*/
--删除测试环境
drop table #a
drop table #b作者: SuperLSF 时间: 2006-10-28 08:29
不错，值得收藏！
or (@i=12) or (@i=14))))
利用ID大于多少和SELECT TOP分页） 