- 处理表重复记录(查询和删除)
/* *****************************************************************************************************************************************************
1、Num、Name相同的重复值记录,没有大小关系只保留一条
2、Name相同,ID有大小关系时,保留大或小其中一个记录
整理人：中国风(Roy)
日期:2008.06.06
***************************************************************************************************************************************************** */
-- 1、用于查询重复处理记录(如果列没有大小关系时2000用生成自增列和临时表处理,SQL2005用row_number函数处理)
-- > --> (Roy)生成測試數據
if not object_id ( ' Tempdb..#T ' ) is null
drop table #T
Go
Create table #T( [ ID ] int , [ Name ] nvarchar ( 1 ), [ Memo ] nvarchar ( 2 ))
Insert #T
select 1 ,N ' A ' ,N ' A1 ' union all
select 2 ,N ' A ' ,N ' A2 ' union all
select 3 ,N ' A ' ,N ' A3 ' union all
select 4 ,N ' B ' ,N ' B1 ' union all
select 5 ,N ' B ' ,N ' B2 '
Go
-- I、Name相同ID最小的记录(推荐用1,2,3),方法3在SQl05时，效率高于1、2
方法1:
Select * from #T a where not exists ( select 1 from #T where Name = a.Name and ID < a.ID)
方法2:
select a. * from #T a join ( select min (ID)ID,Name from #T group by Name) b on a.Name = b.Name and a.ID = b.ID
方法3:
select * from #T a where ID = ( select min (ID) from #T where Name = a.Name)
方法4:
select a. * from #T a join #T b on a.Name = b.Name and a.ID >= b.ID group by a.ID,a.Name,a.Memo having count ( 1 ) = 1
方法5:
select * from #T a group by ID,Name,Memo having ID = ( select min (ID) from #T where Name = a.Name)
方法6:
select * from #T a where ( select count ( 1 ) from #T where Name = a.Name and ID < a.ID) = 0
方法7:
select * from #T a where ID = ( select top 1 ID from #T where Name = a.name order by ID)
方法8:
select * from #T a where ID !>all ( select ID from #T where Name = a.Name)
方法9(注:ID为唯一时可用):
select * from #T a where ID in ( select min (ID) from #T group by Name)
-- SQL2005:
方法10:
select ID,Name,Memo from ( select * , min (ID) over (partition by Name) as MinID from #T a)T where ID = MinID
方法11:
select ID,Name,Memo from ( select * ,row_number() over (partition by Name order by ID) as MinID from #T a)T where MinID = 1
生成结果:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1
(2 行受影响)
*/
-- II、Name相同ID最大的记录,与min相反:
方法1:
Select * from #T a where not exists ( select 1 from #T where Name = a.Name and ID > a.ID)
方法2:
select a. * from #T a join ( select max (ID)ID,Name from #T group by Name) b on a.Name = b.Name and a.ID = b.ID order by ID
方法3:
select * from #T a where ID = ( select max (ID) from #T where Name = a.Name) order by ID
方法4:
select a. * from #T a join #T b on a.Name = b.Name and a.ID <= b.ID group by a.ID,a.Name,a.Memo having count ( 1 ) = 1
方法5:
select * from #T a group by ID,Name,Memo having ID = ( select max (ID) from #T where Name = a.Name)
方法6:
select * from #T a where ( select count ( 1 ) from #T where Name = a.Name and ID > a.ID) = 0
方法7:
select * from #T a where ID = ( select top 1 ID from #T where Name = a.name order by ID desc )
方法8:
select * from #T a where ID !<all ( select ID from #T where Name = a.Name)
方法9(注:ID为唯一时可用):
select * from #T a where ID in ( select max (ID) from #T group by Name)
-- SQL2005:
方法10:
select ID,Name,Memo from ( select * , max (ID) over (partition by Name) as MinID from #T a)T where ID = MinID
方法11:
select ID,Name,Memo from ( select * ,row_number() over (partition by Name order by ID desc ) as MinID from #T a)T where MinID = 1
生成结果2:
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2
(2 行受影响)
*/
-- 2、删除重复记录有大小关系时,保留大或小其中一个记录
-- > --> (Roy)生成測試數據
if not object_id ( ' Tempdb..#T ' ) is null
drop table #T
Go
Create table #T( [ ID ] int , [ Name ] nvarchar ( 1 ), [ Memo ] nvarchar ( 2 ))
Insert #T
select 1 ,N ' A ' ,N ' A1 ' union all
select 2 ,N ' A ' ,N ' A2 ' union all
select 3 ,N ' A ' ,N ' A3 ' union all
select 4 ,N ' B ' ,N ' B1 ' union all
select 5 ,N ' B ' ,N ' B2 '
Go
-- I、Name相同ID最小的记录(推荐用1,2,3),保留最小一条
方法1:
delete a from #T a where exists ( select 1 from #T where Name = a.Name and ID < a.ID)
方法2:
delete a from #T a left join ( select min (ID)ID,Name from #T group by Name) b on a.Name = b.Name and a.ID = b.ID where b.Id is null
方法3:
delete a from #T a where ID not in ( select min (ID) from #T where Name = a.Name)
方法4(注:ID为唯一时可用):
delete a from #T a where ID not in ( select min (ID) from #T group by Name)
方法5:
delete a from #T a where ( select count ( 1 ) from #T where Name = a.Name and ID < a.ID) > 0
方法6:
delete a from #T a where ID <> ( select top 1 ID from #T where Name = a.name order by ID)
方法7:
delete a from #T a where ID >any ( select ID from #T where Name = a.Name)
select * from #T
生成结果:
/*
ID Name Memo
----------- ---- ----
1 A A1
4 B B1
(2 行受影响)
*/
-- II、Name相同ID保留最大的一条记录:
方法1:
delete a from #T a where exists ( select 1 from #T where Name = a.Name and ID > a.ID)
方法2:
delete a from #T a left join ( select max (ID)ID,Name from #T group by Name) b on a.Name = b.Name and a.ID = b.ID where b.Id is null
方法3:
delete a from #T a where ID not in ( select max (ID) from #T where Name = a.Name)
方法4(注:ID为唯一时可用):
delete a from #T a where ID not in ( select max (ID) from #T group by Name)
方法5:
delete a from #T a where ( select count ( 1 ) from #T where Name = a.Name and ID > a.ID) > 0
方法6:
delete a from #T a where ID <> ( select top 1 ID from #T where Name = a.name order by ID desc )
方法7:
delete a from #T a where ID <any ( select ID from #T where Name = a.Name)
select * from #T
/*
ID Name Memo
----------- ---- ----
3 A A3
5 B B2
(2 行受影响)
*/
-- 3、删除重复记录没有大小关系时，处理重复值
-- > --> (Roy)生成測試數據
if not object_id ( ' Tempdb..#T ' ) is null
drop table #T
Go
Create table #T( [ Num ] int , [ Name ] nvarchar ( 1 ))
Insert #T
select 1 ,N ' A ' union all
select 1 ,N ' A ' union all
select 1 ,N ' A ' union all
select 2 ,N ' B ' union all
select 2 ,N ' B '
Go
方法1:
if object_id ( ' Tempdb..# ' ) is not null
drop table #
Select distinct * into # from #T -- 排除重复记录结果集生成临时表#
truncate table #T -- 清空表
insert #T select * from # -- 把临时表#插入到表#T中
-- 查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B
(2 行受影响)
*/
-- 重新执行测试数据后用方法2
方法2:
alter table #T add ID int identity -- 新增标识列
go
delete a from #T a where exists ( select 1 from #T where Num = a.Num and Name = a.Name and ID > a.ID) -- 只保留一条记录
go
alter table #T drop column ID -- 删除标识列
-- 查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B
(2 行受影响)
*/
-- 重新执行测试数据后用方法3
方法3:
declare Roy_Cursor cursor local for
select count ( 1 ) - 1 ,Num,Name from #T group by Num,Name having count ( 1 ) > 1
declare @con int , @Num int , @Name nvarchar ( 1 )
open Roy_Cursor
fetch next from Roy_Cursor into @con , @Num , @Name
while @@Fetch_status = 0
begin
set rowcount @con ;
delete #T where Num = @Num and Name = @Name
set rowcount 0 ;
fetch next from Roy_Cursor into @con , @Num , @Name
end
close Roy_Cursor
deallocate Roy_Cursor
-- 查看结果
select * from #T
/*
Num Name
----------- ----
1 A
2 B
(2 行受影响)
*/
不重复记录的实现
表:USER
ID NAME PASS
1 A 1234
2 A 2345
3 B 12345
4 C DEE
5 C EEE
要求姓名重复的不取出来,查询结果为
3 B 12345
-- > 测试数据：[tb]
if object_id ( ' [tb] ' ) is not null drop table [ tb ]
go
create table [ tb ] ( [ ID ] int , [ NAME ] varchar ( 1 ), [ PASS ] varchar ( 5 ))
insert [ tb ]
select 1 , ' A ' , ' 1234 ' union all
select 2 , ' A ' , ' 2345 ' union all
select 3 , ' B ' , ' 12345 ' union all
select 4 , ' C ' , ' DEE ' union all
select 5 , ' C ' , ' EEE '
-- ------------开始查询--------------------------
-- -方法1
select * from tb where name in ( select name from tb group by name having count ( 1 ) = 1 )
-- -方法2
select * from [ tb ] t where not exists ( select 1 from tb where name = t.name and pass <> t.pass)
-- --------------结果----------------------------
/* ID NAME PASS
----------- ---- -----
3 B 12345
(1 行受影响)
*/
|
求触发器更新发生数量
根据入库数量更新订单已入库数量的触发器怎么写比较好？
因为要考虑入库单的新增、更新与删除的操作，
是分开写insert,update,delete三个触发器吗？
tableA
ID CODE ORDER_QTY
IN_QTY
1 A 1000 0
tableB
AUTOID ID CODE
QTY
1 1 A 100
->
ID CODE ORDER_QTY
IN_QTY
1 A 1000 100
-- 触发器的操作1
create table 化验室纱组(本厂编号 int ,客户 int ,色号 int ,纱支 int )
create table 化验室布组(本厂编号 int ,客户 int ,色号 int ,布类 int )
go
create trigger my_trig on 化验室纱组 for insert , update , delete
as
if not exists ( select 1 from inserted)
delete 化验室布组 from deleted t where 化验室布组.本厂编号 = t.本厂编号
else if not exists ( select 1 from deleted)
insert into 化验室布组(本厂编号 ,客户 ,色号) select 本厂编号 ,客户 ,色号 from inserted
else
update 化验室布组 set 客户 = t.客户 , 色号 = t.色号 from inserted t where 化验室布组.本厂编号 = t.本厂编号
go
-- 1、insert 对化验室纱组插入数据，然后查看化验室布组表的数据
insert into 化验室纱组 values ( 1 , 2 , 3 , 4 )
insert into 化验室纱组 values ( 5 , 6 , 7 , 8 )
go
select * from 化验室布组
/*
本厂编号 客户 色号 布类
----------- ----------- ----------- -----------
1 2 3 NULL
5 6 7 NULL
（所影响的行数为 2 行）
*/
-- 2、update , 更改化验室纱组表中本厂编号=1的色号=6
update 化验室纱组 set 色号 = 6 where 本厂编号 = 1
go
select * from 化验室布组
/*
本厂编号 客户 色号 布类
----------- ----------- ----------- -----------
1 2 6 NULL
5 6 7 NULL
（所影响的行数为 2 行）
*/
-- 3、delete 化验室纱组表中本厂编号=1的那条数据
delete from 化验室纱组 where 本厂编号 = 1
go
select * from 化验室布组
/*
本厂编号 客户 色号 布类
----------- ----------- ----------- -----------
5 6 7 NULL
（所影响的行数为 1 行）
*/
drop table 化验室纱组 , 化验室布组
各位朋友，看看下面的代码如何放到一张表里
select
code,convert(numeric(8,2),round(price,2)) as
price,convert(numeric(8,2),round(price*realgliang,2)) as num from jlbiao
union
all
select code,convert(numeric(8,2),round(price,2)) as
price,price*convert(float ,smount) as num from kucun_table
insert into 表(列名1,列名2,列名3,列名4)
select code, convert (numeric( 8 , 2 ), round (price, 2 )) as price, convert (numeric( 8 , 2 ), round (price * realgliang, 2 )) as num from jlbiao
union all
select code, convert (numeric( 8 , 2 ), round (price, 2 )) as price,price * convert ( float ,smount) as num from kucun_table
表1
dm mc
2101 A
210101
AA
210102 BB
3101 C
310101
CC
310102 DD
表2
rq user
llbmc sl rl zrl je
2010-01 a01
210101-AA 1 2 2 4
2010-01 a01 210101-BB 1
2 2 4
2010-01 a01 210101-CC 1 2 2
4
2011-01 a01 210101-AA 1 2 2 4
2011-01 a01 210101-BB 1 2 2 4
2011-01 a01
210101-CC 1 2 2 4
2010-01 a02
210101-AA 1 2 2 4
2010-01 a02 210101-BB 1
2 2 4
2010-01 a02 210101-CC 1 2 2
4
2011-01 a02 210101-AA 1 2 2 4
2011-01 a02 210101-BB 1 2 2 4
2011-01 a02
210101-DD 1 2 2 4
我想要的汇总结果：
序号
项目 2011年度 2010年度
数量 总容量 总金额 平均单价 数量 总容量 总金额 平均单价
一、 A 4 8 16 16 4 8
16 16
AA 2 4 8
8 2 4 8 8
BB 2
4 8 8 2 4 8 8
二、
C 4 8 16 16 4 8
16 16
CC 2 4 8 8
2 4 8 8
BB 2
4 8 8 2 4 8 8
2011 AA 2 4 8 4 2010 AA 2 4 8 4
2011 BB 2 4 8 4 2010 BB 2 4 8 4
2011
CC 1 2 4 4 2010 CC 2 4 8 4
2011 DD 1 2 4 4 NULL NULL NULL NULL
NULL NULL
select *
from (select left(rq, 4) as rq,
right(llbmc, 2) as llbmc, sum(sl) as 数量, sum(zrl) as 总容量, sum(je) as
总金额, sum(je)/sum(sl) as 平均单价
from t1 left join t2 on (t1.dm + '-' +
t1.mc) = t2.llbmc
where left(rq, 4) = '2011'
group by
left(rq, 4), right(llbmc, 2) ) a
left join (select left(rq, 4) as
rq, right(llbmc, 2) as llbmc, sum(sl) as 数量, sum(zrl) as 总容量, sum(je)
as 总金额, sum(je)/sum(sl) as 平均单价
from t1 left join t2 on (t1.dm +
'-' + t1.mc) = t2.llbmc
where left(rq, 4) = '2010'
group by
left(rq, 4), right(llbmc, 2) ) b
on a.llbmc = b.llbmc
销售表数据如下:
日期 商品 销售数量 销售单格
2010-1-1 A 3
5
2010-1-1 B 4 6
2010-1-2 B
5 7
2010-1-3 A 7 5.5
2010-1-4
B 9 7.5
2010-1-5 A 7 5.5
现
在要计算各商品最近日期销售数量为10时的平均单价
按销售日期倒推，累计销售数量到10时的各商品销售平均价格。
--
--------------------------------------------------------------
--
Author :SQL77(只为思齐老)
--
Date :2010-03-14 10:35:02
--
Version:
--
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
--
Aug 6 2000 00:57:48
--
Copyright (c) 1988-2000 Microsoft Corporation
--
Desktop Engine on Windows NT 5.1 (Build 2600: Service Pack 3)
--
--
--------------------------------------------------------------
--
> 测试数据：#TB
if
object_id
(
'
tempdb.dbo.#TB
'
)
is
not
null
drop
table
#TB
go
create
table
#TB(
[
日期
]
datetime
,
[
商品
]
varchar
(
1
),
[
销售数量
]
int
,
[
销售单格
]
numeric(
2
,
1
))
insert
#TB
select
'
2010-1-1
'
,
'
A
'
,
3
,
5
union
all
select
'
2010-1-1
'
,
'
B
'
,
4
,
6
union
all
select
'
2010-1-2
'
,
'
B
'
,
5
,
7
union
all
select
'
2010-1-3
'
,
'
A
'
,
7
,
5.5
union
all
select
'
2010-1-4
'
,
'
B
'
,
9
,
7.5
union
all
select
'
2010-1-5
'
,
'
A
'
,
7
,
5.5
--
------------开始查询--------------------------
select
*
,(
SELECT
SUM
(销售数量)
FROM
#TB
WHERE
商品
=
T.商品
AND
日期
>=
T.日期)NUM
INTO
#T
from
#TB T
SELECT
商品,
AVG
(销售单格)销售单格
FROM
#T T
WHERE
日期
>=
(
SELECT
TOP
1
日期
FROM
#T
WHERE
商品
=
T.商品
AND
NUM
>=
10
ORDER
BY
日期
DESC
)
GROUP
BY
商品
--
--------------结果----------------------------
/*
商品 销售单格
---- ----------------------------------------
A 5.500000
B 7.250000
（所影响的行数为 2 行）
*/
declare
@table
table
(
日期
varchar
(
10
),
商品
varchar
(
10
),
销售数量
int
,
销售单格 numeric(
12
,
2
))
insert
into
@table
select
'
2010-1-1
'
,
'
A
'
,
3
,
5
union
all
select
'
2010-1-1
'
,
'
B
'
,
4
,
6
union
all
select
'
2010-1-2
'
,
'
B
'
,
5
,
7
union
all
select
'
2010-1-3
'
,
'
A
'
,
7
,
5.5
union
all
select
'
2010-1-4
'
,
'
B
'
,
9
,
7.5
union
all
select
'
2010-1-5
'
,
'
A
'
,
7
,
5.5
select
日期,商品,销售数量,销售单格,(
SELECT
SUM
(销售数量)
FROM
@table
WHERE
商品
=
a.商品
AND
日期
>=
a.日期) NUM
into
#tmp
from
@table
a
select
商品,
avg
(销售单格)
[
avg
]
from
#tmp a
where
NUM
<=
(
SELECT
MIN
(NUM)
FROM
#tmp b
WHERE
a.商品
=
b.商品
and
NUM
>=
10
)
group
by
商品
2万+
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
