sql查询汇总

-
处理表重复记录(查询和删除)


/*
*****************************************************************************************************************************************************

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 行受影响)


*/

SQL code -- ------------ 随机删除重复记录！ ----------------------------- -- ------------ Author: Luoyoumou ----------------------------- drop table test; -- 先求出符合条件的记录： create table test(col1 int identity ( 1 , 1 ), col2 varchar ( 10 ), col3 varchar ( 10 )); insert into test(col2, col3) select ' 我要银子 ' , ' 我没有 ' UNION ALL select ' 我要银子 ' , ' 你有没？ ' UNION ALL select ' 我要金子 ' , ' 金子涨价了 ' UNION ALL select ' 多要银元 ' , ' 我没有 ' UNION ALL select ' 我要银子 ' , ' 我没有 ' UNION ALL select ' 我要银子 ' , ' 你有没？ ' UNION ALL select ' 我要银子 ' , ' 明年才有 ' UNION ALL select ' 我要银子 ' , ' 你有没？ ' select * from test; select distinct col2, col2 from test; select t1.all_sum as ' 所有记录行 ' , t2.dis_sum as ' 无重复记录行 ' , t1.all_sum - t2.dis_sum as ' 将要删除的行数 ' from ( select count ( * ) as all_sum from test ) t1, ( select count ( * ) as dis_sum from ( select distinct col2, col3 from test) t) t2; -- -如：随机删除 col2='我要银子' 的重复记录 select sum (row_sum) as all_del from ( select col2, col3, count (col1) - 1 as ' row_sum ' from test where col2 = ' 我要银子 ' group by col2, col3 having count (col1) - 1 <> 0 ) t1 -- 删除条件：将col2、col3都相同的，视为重复记录，将其随机删除指定条件(col2='我要银子')的重复记录，只剩一行 -- 选择语句: select top ( select sum (row_sum) as all_del from ( select col2, col3, count (col1) - 1 as ' row_sum ' from test where col2 = ' 我要银子 ' group by col2, col3 having count (col1) - 1 <> 0 ) t1) t1.col1, t1.col2, t1.col3 from test t1 where t1.col2 = ' 我要银子 ' and t1.col1 not in ( select col1 from test t3 where exists ( select col2, col3 from test where col2 = ' 我要银子 ' and col2 = t3.col2 and col3 = t3.col3 group by col2, col3 having count (col1) = 1 )) order by NEWID () -- 删除语句: 将其随机删除(col2='我要银子'的重复记录)只剩一行 DELETE FROM test WHERE col1 in ( select col1 from ( select top ( select sum (row_sum) as all_del from ( select col2, col3, count (col1) - 1 as ' row_sum ' from test where col2 = ' 我要银子 ' group by col2, col3 having count (col1) - 1 <> 0 ) t1) t1.col1, t1.col2, t1.col3 from test t1 where t1.col2 = ' 我要银子 ' and t1.col1 not in ( select col1 from test t3 where exists ( select col2, col3 from test where col2 = ' 我要银子 ' and col2 = t3.col2 and col3 = t3.col3 group by col2, col3 having count (col1) = 1 )) order by NEWID ()) t ) -- 删除语句: 将其随机删除所有重复的记录，每组重复记录只剩一行 DELETE FROM test WHERE col1 in ( select col1 from ( select top ( select sum (row_sum) as all_del from ( select col2, col3, count (col1) - 1 as ' row_sum ' from test group by col2, col3 having count (col1) - 1 <> 0 ) t1) t1.col1, t1.col2, t1.col3 from test t1 where t1.col1 not in ( select col1 from test t3 where exists ( select col2, col3 from test where col2 = t3.col2 and col3 = t3.col3 group by col2, col3 having count (col1) = 1 )) order by NEWID ()) t )

 

 

 

求触发器更新发生数量
根据入库数量更新订单已入库数量的触发器怎么写比较好？
因为要考虑入库单的新增、更新与删除的操作， 是分开写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
 化验室纱组 , 化验室布组

create trigger tr_b on tableb for insert , update , delete as begin DECLARE @N1 INT , @n2 INT SET @N1 = ( SELECT COUNT ( * ) FROM INSERTED) SET @N2 = ( SELECT COUNT ( * ) FROM DELETEED) IF ( @n1 > 0 AND @N2 > 0 ) BEGIN UPDATE A SET IN_QTY = IN_QTY + (I.QTY - D.QTY) FROM TABLEA A JOIN INSERTED I ON A.CODE = I.CODE JOIN DELETED D ON A.CODE = D.CODE END IF ( @N1 > 0 AND @n2 = 0 ) begin UPDATE A SET IN_QTY = IN_QTY + I.QTY FROM TABLEA A JOIN INSERTED I ON A.CODE = I.CODE end if ( @n1 = 0 and @n2 > 0 ) begin UPDATE A SET IN_QTY = IN_QTY - d.QTY FROM TABLEA A JOIN DELETED I ON A.CODE = D.CODE end end

 

 

 

各位朋友，看看下面的代码如何放到一张表里
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


 商品