为庆祝我当选SQL版版主,发一些有用SQL语句. - 编程论坛

#2

潇洒老乌龟2006-09-28 22:04

SQL2000字符串分离

如何将'2006-01-12|2006-02-13|2006-03-15|2006-11-11|2003-1-1'按'|'分割成
2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1

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

输出结果：
2006-01-12
2006-02-13
2006-03-15
2006-11-11
2003-1-1

一字段信息包括如下：李三|李三＠d.com|公司|单位地址|
我将查询用(Select)只列出李三及单位地址的方法？

select substring(c1,1,charindex('|',c1)-1) as name,
reverse(substring(reverse(c1) , 2 , charindex('|' , reverse(c1) , 2) - 2)) as address from #t

drop table #t

name address
----- -------
li4 china
zhang3 USA
李三单位地址

（所影响的行数为 3 行）

#3

潇洒老乌龟2006-09-28 22:05

如何用SQL语句取主键字段名称及字段类型

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

#4

潇洒老乌龟2006-09-28 22:05

将多个表的数据按ID合并起来

a
id , value
1 , 11
2 , 21
3 , 31
4 , 41
5 , 51

b
id , value
1 , 12
2 , 22
3 , 32
4 , 42

c , value
1 , 13
2 , 23
3 , 33
5 , 53

d
a , value1 , value2 , value3
1 , 11 , 12 , 13
2 , 21 , 22 , 23
3 , 31 , 32 , 33
4 , 41 , 42 , 0
5 , 51 , 0 , 53

CREATE TABLE #a ([id] [char] (10),[value] [int])
CREATE TABLE #b ([id] [char] (10),[value] [int])
CREATE TABLE #c ([id] [char] (10),[value] [int])

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

drop table #a
drop table #b
drop table #c

id value1 value2 value3
---------- ----------- ----------- -----------
1 11 12 13
2 21 22 23
3 31 32 33
4 41 42 NULL
5 51 NULL 53

（所影响的行数为 5 行）

#5

潇洒老乌龟2006-09-28 22:06

如何取每个种类的前百分之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

现有表a，想得到表a中各地区(area)的商户(cust)交易金额(money)排该地区里面前百分之50%的记录.
即要：
地区A中金额前百分之50%
地区B中金额前百分之50%
....C..............50%
....D..............50%
......................

CREATE TABLE #a (
[area] [char] (10),
[cust] [char] (10),
[money] [numeric](10, 1) NULL
)

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

（所影响的行数为 5 行）

#6

潇洒老乌龟2006-09-28 22:06

怎样用SQL查询出每天刷卡次数大于3次的人员刷卡记录

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

ManID TheDateTime
---------- ------------------------------------------------------
001 2006-09-26 10:00:00.000
001 2006-09-26 10:05:00.000
001 2006-09-26 12:00:00.000
001 2006-09-26 18:00:00.000
001 2006-09-27 12:00:00.000
001 2006-09-27 18:00:00.000
001 2006-09-27 18:00:00.000
001 2006-09-27 18:00:00.000

（所影响的行数为 8 行）

#7

潇洒老乌龟2006-09-28 22:07

查询每天的累计总量

现有如下结构的表:
编号----产品名称----产量(int)----日期(varchar)
1 ----某产品1 ----100 ----20060922
2 ----某产品1 ----130 ----20060923
3 ----某产品2 ----104 ----20060924
5 ----某产品1 ----100 ----20060925
7 ----某产品1 ----200 ----20060926
.
.
.
现在要求查询每天的累计产量如:
20060923的累计产量就为20060922的100+20060923的130=230.
20060924的累计产量就为230+104=334,
20060925的累计产量为334+100=434...
请问该如何查询?

create table #a(
[id] [char] (10),
[proname] [char] (10),
[output] [int],
[dt] [datetime]
)

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

drop table #a

id proname output dt 每日总量
---------- ---------- ----------- ------------------------------------------------------ -----------
1 某产品1 100 2006-09-22 00:00:00.000 100
2 某产品1 130 2006-09-23 00:00:00.000 230
3 某产品2 104 2006-09-24 00:00:00.000 334
5 某产品1 100 2006-09-25 00:00:00.000 434
7 某产品1 200 2006-09-26 00:00:00.000 634

（所影响的行数为 5 行）

#8

潇洒老乌龟2006-09-28 22:07

得到表中除Col1、Col2的所有列

例如：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

#9

潇洒老乌龟2006-09-28 22:08

两表字段横向统计表

如果有表t1 ,t2

t1包含了f1,f2,f3...int型字段
t2包含了f1,f2,f3...int型字段
如：
t1 t2
======================= =======================
f1 f2 f3 f1 f2 f3
======================= =======================
10 20 30 10 -20 30
20 30 40 20 30 -40
.... ....

现在希望生成一个查询
==========================================================
t1.f1 t1.f2 t1.f3 t2.f1 t2.f2 t2.f3 sum
==========================================================
10 20 30 10 -20 30 80
20 30 40 20 30 -40 120

如果有关联字段ID，如下：

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

#10

潇洒老乌龟2006-09-28 22:08

用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.商品名称

#11

潇洒老乌龟2006-09-28 22:09

分类统计并算库存

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件

--要求以商品类别，商品名称，商品规格，商品单价进行分组，得出库存结余数

商品类别商品名称商品规格商品单价库存结余数量
服装西服 175xx 1000 7
服装西服 175xx 2000 15

drop table test

select 商品类别,商品名称,商品规格,商品单价,
sum(case 出入库状态 when '1' then 数量 else -数量 end) as 库存结余数量
from #test
group by 商品类别,商品名称,商品规格,商品单价

商品类别商品名称商品规格商品单价库存结余数量
-------- -------- -------- -------- ------------
服装西服 175xx 1000.00 7
服装西服 175xx 2000.00 15

（所影响的行数为 2 行）

#12

潇洒老乌龟2006-09-28 22:09

根据表中的列值把一行拆分成若干行

假如查询出来数据是:
ID start end
1 1 100

如何实现根据start与end的值,将这一行拆分成若干行?
期望结果集是:
ID start end
1 1 1
1 1 2
1 1 3
………………
1 1 100
总计就要有一百行了。

--原数据
declare @t table (
ID int,
start int,
[end] int
)
insert @t select
1, 1, 100

--临时表
select top 8000 identity(int,1,1) as id into # from syscolumns,syscolumns

--查询
select b.id,b.start,t.id as [end]
from @t b,# t
where t.id between b.start and b.[end]

--删除临时表
drop table #

create table #(id int,start int,[end] int)
insert into #(id,start,[end])
select 1,1,100

declare @end int
select @end=[end] - 1 from #
set rowcount @end
select identity(int,1,1) as id into #t1 from sysobjects,syscolumns
set rowcount 0

insert into #(id,start,[end])
select 1,1,id from #t1

select * from # order by [end]

drop table #
drop table #t1

#13

潇洒老乌龟2006-09-28 22:10

如何合并记录

我现在想把几条记录合并成一条，比如
姓名科目
张三语文
张三英语
张三物理
李四语文
李四化学

我想合并成
李四化学语文
张三数学物理英语

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 姓名

drop function fn_合并
drop table tablename

姓名科目
---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
李四化学语文
张三数学物理英语

（所影响的行数为 2 行）

如下表
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 行）

#14

潇洒老乌龟2006-09-28 22:10

SQL中如何区分大小写

比如说一个表中两条记录的address字段值分别为：aaaa 和 aAAa，如果用
select * from mytable where address = 'aaaa'
则两条记录都查出来了，我希望只得到一条记录，这样的SQL应该怎么写？

create table #a(
[id] [char] (10),
[address] [char] (10)
)
insert into #a(id , address) values('1' , 'aaaa')
insert into #a(id , address) values('1' , 'aAAa')

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 行）

#15

潇洒老乌龟2006-09-28 22:11

在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

#16

wohemachen2006-09-29 15:25

这么多呀~ 学习一下~

#17

CrazyWeed09072006-09-29 16:31

没什么东西,但还是支持你一下

#18

潇洒老乌龟2006-10-01 20:10

用SQL计算当月的天数

select DATEDIFF(dd,getdate(),DATEADD(mm, 1, getdate()))

#19

潇洒老乌龟2006-10-01 20:10

联接的很多写法

DECLARE
@TA TABLE (IDA INT,VA VARCHAR(10))
DECLARE
@TB TABLE (IDB INT,VB VARCHAR(10))

INSERT INTO @TA
SELECT
1,'AA' UNION SELECT
2,'BC' UNION SELECT
3,'CCC'

INSERT INTO @TB
SELECT
1,'2' UNION SELECT
3,'58' UNION SELECT
4,'67'

--内联接简单写法
SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A,@TB B
WHERE A.IDA=B.IDB

--内联接
SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A INNER JOIN @TB B
ON A.IDA=B.IDB

SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A JOIN @TB B
ON A.IDA=B.IDB

--左外联接
SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A LEFT JOIN @TB B
ON A.IDA=B.IDB

SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A LEFT OUTER JOIN @TB B
ON A.IDA=B.IDB

--右外联接
SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A RIGHT JOIN @TB B
ON A.IDA=B.IDB

SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A RIGHT OUTER JOIN @TB B
ON A.IDA=B.IDB

--完整外联接
SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A FULL JOIN @TB B
ON A.IDA=B.IDB

SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A FULL OUTER JOIN @TB B
ON A.IDA=B.IDB

--交叉联接
SELECT A.IDA,A.VA,B.IDB,B.VB FROM @TA A CROSS JOIN @TB B

--自联接
SELECT A.IDA,A.VA,B.IDA,B.VA FROM @TA A,@TA B WHERE A.IDA=B.IDA+1

#20

潇洒老乌龟2006-10-01 20:11

清除日志(clearlog)

清除日志：

DECLARE @LogicalFileName sysname,
@MaxMinutes INT,
@NewSize INT
USE szwzcheck -- 要操作的数据库名
SELECT @LogicalFileName = 'szwzcheck_Log', -- 日志文件名
@MaxMinutes = 10, -- Limit on time allowed to wrap log.
@NewSize = 20 -- 你想设定的日志文件的大小(M)
-- Setup / initialize
DECLARE @OriginalSize int
SELECT @OriginalSize = size
FROM sysfiles
WHERE name = @LogicalFileName
SELECT 'Original Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),@OriginalSize) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(@OriginalSize*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
CREATE TABLE DummyTrans
(DummyColumn char (8000) not null)
DECLARE @Counter INT,
@StartTime DATETIME,
@TruncLog VARCHAR(255)
SELECT @StartTime = GETDATE(),
@TruncLog = 'BACKUP LOG ' + db_name() + ' WITH TRUNCATE_ONLY'
DBCC SHRINKFILE (@LogicalFileName, @NewSize)
EXEC (@TruncLog)
-- Wrap the log if necessary.
WHILE @MaxMinutes > DATEDIFF (mi, @StartTime, GETDATE()) -- time
AND @OriginalSize = (SELECT size FROM sysfiles WHERE name =
@LogicalFileName)
AND (@OriginalSize * 8 /1024) > @NewSize
BEGIN -- Outer loop.
SELECT @Counter = 0
WHILE ((@Counter < @OriginalSize / 16) AND (@Counter < 50000))
BEGIN -- update
INSERT DummyTrans VALUES ('Fill Log')
DELETE DummyTrans
SELECT @Counter = @Counter + 1
END
EXEC (@TruncLog)
END
SELECT 'Final Size of ' + db_name() + ' LOG is ' +
CONVERT(VARCHAR(30),size) + ' 8K pages or ' +
CONVERT(VARCHAR(30),(size*8/1024)) + 'MB'
FROM sysfiles
WHERE name = @LogicalFileName
DROP TABLE DummyTrans
SET NOCOUNT OFF

把szwzcheck换成你数据库的名字即可，在查询分析器里面运行。

#21

潇洒老乌龟2006-10-01 20:11

日志文件损坏，如何恢复数据库

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

#22

潇洒老乌龟2006-10-01 20:12

取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

#23

潇洒老乌龟2006-10-01 20:12

求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)

#24

潇洒老乌龟2006-10-01 20:13

实现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

select dbo.L2U(12.93,1),dbo.L2U(12.93,0)

CREATE PROCEDURE dbo.L2U
(
@n_LowerMoney numeric(15,2),
@v_TransType int,
@RET VARCHAR(200) output
)
AS

Declare @v_LowerStr VARCHAR(200) -- 小写金额
Declare @v_UpperPart VARCHAR(200)
Declare @v_UpperStr VARCHAR(200) -- 大写金额
Declare @i_I int

set nocount On

select @v_LowerStr = LTRIM(RTRIM(STR(@n_LowerMoney,20,2))) --四舍五入为指定的精度并删除数据左右空格

select @i_I = 1
select @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
select @v_UpperStr = @v_UpperPart + @v_UpperStr
select @i_I = @i_I + 1
end

--------print '//v_UpperStr ='+@v_UpperStr +'//'

if ( @v_TransType=0 )
begin
select @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')
select @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')
select @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')
select @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元')
select @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')
select @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')
select @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')
select @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')
select @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')
select @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')
select @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')
select @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
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=8) 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<>8) 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

#25

潇洒老乌龟2006-10-01 20:13

按表中ID分类统计再合并表数据

Food表中我加了一列Counter,这一列的值应该是在另外一个表中UserFood中Food.ID出现的次数,请问这个sql如何写.
我用这个select [ID],IsNULL((Select count(1) from UserFood where UserFood.FoodID = Food.ID),0) from Food,可以等到想要的值,但是不知道如何插入到Food表中.谢谢!!!

Food表
-------------
ID FoodName Counter
-------------
1 第一个 NULL
2 第二个 NULL
3 第三个 NULL

UserFood表
-------------
ID FoodID
-------------
1 1
2 2
3 1
4 1

想要的结果是:
Food表
-------------
ID FoodName Counter
-------------
1 第一个 3
2 第二个 1
3 第三个 0

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

drop table food
drop table usefood

id foodname counter
---- ---------- -----------
1 第一个 3
2 第二个 1
3 第三个 0

（所影响的行数为 3 行）

#26

潇洒老乌龟2006-10-01 20:14

求一条追回序号的语句

如何简单来实现一条根据_id1,_id2的索引顺序,更新id的序号值,如下所述

如表
id _id1 _id2
null a c
null a d
null a f
null b c
null b e

执行后:

id _id1 _id2
1 a c
2 a d
3 a f
1 b c
2 b e

declare @tab table(id int,_id1 varchar(10),_id2 varchar(10))

insert @tab values(null,'a','e')
insert @tab values(null,'a','d')
insert @tab values(null,'a','f')
insert @tab values(null,'b','c')
insert @tab values(null,'b','e')

select * from @tab

select _id1,_id2,[排名]=(select count(1) from @tab where _id1=a._id1 and _id2<a._id2)+1 from @tab a
order by _id1,_id2

_id1 _id2 排名
---------- ---------- -----------
a d 1
a e 2
a f 3
b c 1
b e 2

（所影响的行数为 5 行）

#27

潇洒老乌龟2006-10-01 20:14

如何将上月的数据插入到本月中

有一个表A.
有字段id,month,tbh,sfbs
id是自动编号
month是指月份,如200609
month有重复记录
现在想求一条语句,就是取出09月的数据全部取出,然后改成10月份再存到表A中,
要求会判断表中有没有10月份数据的存在,month+tbh+sfbs三个字段是唯一的数据

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)

#28

潇洒老乌龟2006-10-01 20:14

行列转换

SQL语句之普通行列转换

假设有张学生成绩表(tb_rowtocol)如下
Name Subject Result
张三语文　　73
张三数学　　83
张三物理　　93
李四语文　　74
李四数学　　84
李四物理　　94

想变成
姓名语文数学物理
张三 73　　83　　93
李四 74　　84　　94

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)

如果上述两表互相换一下：即
表名(cj)
姓名语文数学物理
张三 73　　83　　93
李四 74　　84　　94

想变成

Name Subject Result
张三语文　　73
张三数学　　83
张三物理　　93
李四语文　　74
李四数学　　84
李四物理　　94

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

行列转换加合计

例如有表A某些人每月消费数据如下：
id data month
001 11 1
001 12 2
001 13 3
001 14 4
001 15 5
001 16 6
001 17 7
001 18 8
001 19 9
001 110 10
001 111 11
001 112 12
002 21 1
002 22 2
002 23 3
002 24 4
002 25 5
002 26 6
002 27 7
002 28 8
002 29 9
002 210 10
002 211 11
002 212 12
要实现如下结果：
人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月
001 11 12 13 14 15 16 17 18 19 110 111 112
002 21 22 23 24 25 26 27 28 29 210 211 212

人员 1月 2月 3月 4月 5月 6月 7月 8月 9月 10月 11月 12月合计
001 11 12 13 14 15 16 17 18 19 110 111 112 468
002 21 22 23 24 25 26 27 28 29 210 211 212 858

第一个：

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

表结构：
店面产品金额
--------------------------
A店产品1 100
A店产品2 100
A店产品3 100
B店产品1 200
B店产品3 200
……
---------------------------
要求的结果是：
店面名称产品1 产品2 产品3 …… 总计
A 100 100 100 …… 300
B 200 0 200 …… 400

这里产品数量不是固定的，店面也不是固定的
-----------------------------------
数据表里大约30万+的数据，
要怎么写这个存储过程能够使效率更高一些？

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

drop table test
go

店面产品1 产品2 产品3 总计
---- ----- ----- ----- -----------
A店 100 100 100 300
B店 200 0 200 400

#29

潇洒老乌龟2006-10-01 20:15

按日期统计的交叉表

表结构：
日期用户ID 数量
2006.9.20 8 20
2006.9.1 10 20
2006.9.18 1 20
2006.9.1 5 20
2006.9.13 1 20
2006.9.14 3 20
2006.9.14 2 20
2006.9.20 7 20
2006.9.10 6 20
2006.9.20 1 20
统计结果为（按月按计，如统计9月数据）
日期用户ID1，用户ID2，用户ID3....
1号数据汇总数据汇总数据汇总
2号数据汇总数据汇总数据汇总
3号数据汇总数据汇总数据汇总
4号数据汇总数据汇总数据汇总
5号数据汇总数据汇总数据汇总
6号数据汇总数据汇总数据汇总
7号数据汇总数据汇总数据汇总
8号数据汇总数据汇总数据汇总
9号数据汇总数据汇总数据汇总

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,日期)'

exec(@sql)

drop table test
go

日期用户ID1 用户ID10 用户ID2 用户ID3 用户ID5 用户ID6 用户ID7 用户ID8
1 0 20 0 0 20 0 0 0
10 0 0 0 0 0 20 0 0
13 20 0 0 0 0 0 0 0
14 0 0 20 20 0 0 0 0
18 20 0 0 0 0 0 0 0
20 20 0 0 0 0 0 20 20

#30

潇洒老乌龟2006-10-01 20:15

统计员工工作时间

现在有一个表（work）
字段
姓名(char)，员工编号（int），时间(datetime)，方向(cint)
说明
这是一个公司用来统计员工时间的表

员工上班时刷一下卡系统就会往表里面添加一条新纪录记下
姓名员工编号上班时间方向（0表示上班）

员工下班时刷一下卡系统也会往表里面添加一条新纪录
姓名员工编号上班时间方向（1表示上班）

现在的问题是我想统计每个员工的工作时间怎么统计

--------------------------------------------------
PS：这个数据库结构不适我设计的

大家觉得能统计出时间来吗？

欢迎大家积极发表意见分不够可以加

写个简单的(一天只打卡二次,上班一次,下班一次):
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
*/

#31

潇洒老乌龟2006-10-01 20:16

按某一字段分类排序

有一表
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

declare @tab table(a int,b varchar(2),c int)

insert @tab values(7,'aa',153)
insert @tab values(9,'aa',152)
insert @tab values(6,'aa',120)
insert @tab values(8,'aa',168)
insert @tab values(5,'bb',159)
insert @tab values(7,'bb',179)
insert @tab values(8,'bb',149)
insert @tab values(9,'bb',139)
insert @tab values(6,'bb',169)

select * from @tab

select px=(select count(1) from @tab where b=a.b and c<a.c)+1 , a,b,c from @tab a
order by b , c

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

（所影响的行数为 9 行）

在上面例中我们看到，以B分类排序，C是从小到大，如果C从大到小排序，即下面结果：
px a b c
1 8 aa 168
2 9 aa 153
3 7 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139

declare @tab table(a int,b varchar(2),c int)

insert @tab values(7,'aa',153)
insert @tab values(9,'aa',152)
insert @tab values(6,'aa',120)
insert @tab values(8,'aa',168)
insert @tab values(5,'bb',159)
insert @tab values(7,'bb',179)
insert @tab values(8,'bb',149)
insert @tab values(9,'bb',139)
insert @tab values(6,'bb',169)

select * from @tab

select px=(select count(1) from @tab where b=a.b and c>a.c)+1 , a,b,c from @tab a
order by b , c desc

px a b c
----------- ----------- ---- -----------
1 8 aa 168
2 7 aa 153
3 9 aa 152
4 6 aa 120
1 7 bb 179
2 6 bb 169
3 5 bb 159
4 8 bb 149
5 9 bb 139

（所影响的行数为 9 行）

#32

zhanghighway2006-10-02 14:05

很好啊

#33

qsrock2006-10-03 21:31

强烈的支持下!~

#34

棉花糖ONE2006-10-05 20:07

这些太好了，以后多发点

#35

HankStar2006-10-07 21:19

已经全部收集放到手机里了~

嘿嘿~

#36

Hunter_Huang2006-10-09 13:36

全是些很有用的，非常感谢楼主！

#37

licaiks2007-04-24 14:49

哈哈
我收下拉