分类统计并算库存

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

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

假如查询出来数据是:
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

如何合并记录

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

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

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

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

在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

用SQL计算当月的天数

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

联接的很多写法

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

清除日志(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换成你数据库的名字即可，在查询分析器里面运行。

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

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

取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