现在速度还是有点慢,大家帮忙改进一下这个下面的sql 语句
所有情况如下:记录
ID A B C Price
1 1 2 3 100
2 1 3 2 200
3 2 1 3 1000
4 2 3 1 120
5 3 1 2 130
6 3 2 1 140
7 8 9 10 1000
8 8 10 9 1100
9 9 8 10 1200
10 9 10 8 1500
11 10 8 9 900
12 10 9 8 2000....
...类似上面的记录要求
1) 1,2,3在A,B,C中的序列组合
8,9,10也是在A,B,C中的序列组合
最后合并成 2条记录
1*2*3
8*9*10(见函数sortabc)2)取得合适的price值,做为 组合的price (例如1*2*3、8*9*10组合)
(见函数 GetGroupPrice)
先取得这个组的最小Price和平均Price
a) 如果最小Price小于平均Price的 0.75 ,则取得第二小的Price做为这个组的Price
b) 如果最小Price大于平均Price的 0.75 ,则取平均Price作为 这个组的Price3)组中每个记录的值和 本组的Price 进行比较如果 本组的Price -Price <100 ,则置Price 为0
如果 本组的Price -Price >100 ,则置Price 为本组的Price -Price我的sql语句如下:
1)先update所有记录的 sortabc (这个字段存的值是a,b,c)
UPDATE tb SET sortabc =dbo.sortabc(a,b,c)
2) 取得每个组的Price ,然后update字段
UPDATE tb Set GroupPrice =dbo.GetGroupPrice(sortabc) 3) 置Group的标志位
UPDATE tb Set IsGroup = 1
WHERE sortabc IN
(
SELECT sortabc FROM
(
SELECT sortabc,Price,dbo.GetGroupPrice(sortabc) AS GroupPrice FROM tb
) UN
GROUP BY sortabc,GroupPrice HAVING COUNT(*) =6
)4)设置Price 的值
--计算差值
UPDATE TB SET ValuePrice =Price- GroupPrice WHERE IsGroup =1 --如果<100 ,则置成零
UPDATE TB SET ValuePrice =0 WHERE ValuePrice<0 OR ValuePrice<100 --如果>0 ,则把标志位置成 0
UPDATE TB SET IsGroup =0 WHERE ValuePrice >0 --取得计算的结果
SELECT DISTINCT sortabc,GroupPrice FROM TB
WHERE IsGroup =1 相关的帖子http://community.csdn.net/Expert/topic/4970/4970010.xml?temp=.4705316
http://community.csdn.net/Expert/topic/5017/5017662.xml?temp=.7935755
相关语句如下:
drop table tb
go
drop function GetGroupPrice
godrop function sortabc
go
USE [test]
GOCREATE TABLE [dbo].[tb](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A] [int] NULL,
[B] [int] NULL,
[C] [int] NULL,
[Price] [decimal](18, 4) NULL CONSTRAINT [DF_tb_Price] DEFAULT ((1000)),
[SortABC] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[GroupPrice] [decimal](18, 4) NULL,
[ValuePrice] [decimal](18, 4) NULL,
[IsGroup] [int] NULL CONSTRAINT [DF_tb_IsGroup] DEFAULT ((0))
) ON [PRIMARY]GO
--============================================================================================
create function [dbo].[sortabc]
(
@a int,
@b int,
@c int
)
returns varchar(100)
as
begin if (@a>@b) and( @b>@c)
begin
return cast(@c as varchar(3))+'*'+cast(@b as varchar(3))+'*'+cast(@a as varchar(3))
end if (@a>@c) and (@c>@b)
begin
return cast(@b as varchar(3))+'*'+cast(@c as varchar(3))+'*'+cast(@a as varchar(3))
end
if (@b>@a) and (@a>@c)
begin
return cast(@c as varchar(3))+'*'+cast(@a as varchar(3))+'*'+cast(@b as varchar(3))
end if (@b>@c) and (@c>@a)
begin
return cast(@a as varchar(3))+'*'+cast(@c as varchar(3))+'*'+cast(@b as varchar(3))
end
if (@c>@b) and (@b>@a)
begin
return cast(@a as varchar(3))+'*'+cast(@b as varchar(3))+'*'+cast(@c as varchar(3))
end if (@c>@a) and (@a>@b)
begin
return cast(@b as varchar(3))+'*'+cast(@a as varchar(3))+'*'+cast(@c as varchar(3))
end return''
endGO--============================================================================================CREATE function [dbo].[GetGroupPrice]
(
@Sortabc varchar(100)
)
returns decimal(18,4)
as
beginDECLARE @Price decimal(18,4)
DECLARE @MIN decimal(18,4)
DECLARE @AVG decimal(18,4)
--
--DECLARE @Sortabc varchar(100)
--SET @Sortabc ='1*2*15'--SELECT * FROM tb where sortabc = '1*2*6'
--update tb set price =1000.00 where id =406
--
SELECT TOP 1 @MIN =min(price) ,@AVG = avg(price) FROM tb WHERE sortabc = @Sortabc
GROUP BY sortabc
IF (@MIN <@AVG *0.75)
BEGIN
SET @price = (SELECT Top 1 Price FROM TB WHERE PRICE >@MIN AND Sortabc= @Sortabc)
END
ELSE
BEGIN
SET @Price =@AVG
END
SET @price = @price *1.15
if @price = null
return 0
return @price
end---===================================================================
-- 生成test数据
DECLARE @A INT
DECLARE @B INT
DECLARE @C INT
DECLARE X_Cursor CURSOR FOR
SELECT A FROM AData WHERE A IN(
1,2,3,4,5,6,7,8,9,10,
11,12,13,14,15,16,17,18,19,20,
21,22,23,24,25,26,26,28,29,30,
31,32,33,34,35,36,37,38,39,40,
41,42,43,44,45,46,47,48,49,50
)OPEN X_Cursor
FETCH NEXT FROM X_Cursor
INTO @A
WHILE @@FETCH_STATUS = 0
BEGIN -- BEGINT Y DECLARE Y_Cursor CURSOR FOR
SELECT A FROM AData
OPEN Y_Cursor
FETCH NEXT FROM Y_Cursor
INTO @B
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CAST(@A AS VARCHAR(10)) +'>' +CAST(@B AS VARCHAR(10))
--BEGIN Z
DECLARE Z_Cursor CURSOR FOR
SELECT A FROM AData
OPEN Z_Cursor
FETCH NEXT FROM Z_Cursor
INTO @C
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CAST(@A AS VARCHAR(10)) +'>' +CAST(@B AS VARCHAR(10)) +'>' +CAST(@C AS VARCHAR(10))
DECLARE @Random int;
DECLARE @Upper int;
DECLARE @Lower int --1 and 999
SET @Lower = 100 -- The lowest random number
SET @Upper = 10000-- The
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0) print @Random INSERT INTO [test].[dbo].[tb]([A], [B], [C],[Price]) VALUES(@A, @B, @C,@Random)
FETCH NEXT FROM Z_Cursor
INTO @C
END CLOSE Z_Cursor
DEALLOCATE Z_Cursor --END Z
FETCH NEXT FROM Y_Cursor
INTO @B
END CLOSE Y_Cursor
DEALLOCATE Y_Cursor
-- END Y
FETCH NEXT FROM X_Cursor
INTO @A
ENDCLOSE X_Cursor
DEALLOCATE X_Cursordelete from tb
where a=b or b=c or a=c
所有情况如下:记录
ID A B C Price
1 1 2 3 100
2 1 3 2 200
3 2 1 3 1000
4 2 3 1 120
5 3 1 2 130
6 3 2 1 140
7 8 9 10 1000
8 8 10 9 1100
9 9 8 10 1200
10 9 10 8 1500
11 10 8 9 900
12 10 9 8 2000....
...类似上面的记录要求
1) 1,2,3在A,B,C中的序列组合
8,9,10也是在A,B,C中的序列组合
最后合并成 2条记录
1*2*3
8*9*10(见函数sortabc)2)取得合适的price值,做为 组合的price (例如1*2*3、8*9*10组合)
(见函数 GetGroupPrice)
先取得这个组的最小Price和平均Price
a) 如果最小Price小于平均Price的 0.75 ,则取得第二小的Price做为这个组的Price
b) 如果最小Price大于平均Price的 0.75 ,则取平均Price作为 这个组的Price3)组中每个记录的值和 本组的Price 进行比较如果 本组的Price -Price <100 ,则置Price 为0
如果 本组的Price -Price >100 ,则置Price 为本组的Price -Price我的sql语句如下:
1)先update所有记录的 sortabc (这个字段存的值是a,b,c)
UPDATE tb SET sortabc =dbo.sortabc(a,b,c)
2) 取得每个组的Price ,然后update字段
UPDATE tb Set GroupPrice =dbo.GetGroupPrice(sortabc) 3) 置Group的标志位
UPDATE tb Set IsGroup = 1
WHERE sortabc IN
(
SELECT sortabc FROM
(
SELECT sortabc,Price,dbo.GetGroupPrice(sortabc) AS GroupPrice FROM tb
) UN
GROUP BY sortabc,GroupPrice HAVING COUNT(*) =6
)4)设置Price 的值
--计算差值
UPDATE TB SET ValuePrice =Price- GroupPrice WHERE IsGroup =1 --如果<100 ,则置成零
UPDATE TB SET ValuePrice =0 WHERE ValuePrice<0 OR ValuePrice<100 --如果>0 ,则把标志位置成 0
UPDATE TB SET IsGroup =0 WHERE ValuePrice >0 --取得计算的结果
SELECT DISTINCT sortabc,GroupPrice FROM TB
WHERE IsGroup =1 相关的帖子http://community.csdn.net/Expert/topic/4970/4970010.xml?temp=.4705316
http://community.csdn.net/Expert/topic/5017/5017662.xml?temp=.7935755
相关语句如下:
drop table tb
go
drop function GetGroupPrice
godrop function sortabc
go
USE [test]
GOCREATE TABLE [dbo].[tb](
[ID] [int] IDENTITY(1,1) NOT NULL,
[A] [int] NULL,
[B] [int] NULL,
[C] [int] NULL,
[Price] [decimal](18, 4) NULL CONSTRAINT [DF_tb_Price] DEFAULT ((1000)),
[SortABC] [varchar](50) COLLATE Chinese_PRC_CI_AS NULL,
[GroupPrice] [decimal](18, 4) NULL,
[ValuePrice] [decimal](18, 4) NULL,
[IsGroup] [int] NULL CONSTRAINT [DF_tb_IsGroup] DEFAULT ((0))
) ON [PRIMARY]GO
--============================================================================================
create function [dbo].[sortabc]
(
@a int,
@b int,
@c int
)
returns varchar(100)
as
begin if (@a>@b) and( @b>@c)
begin
return cast(@c as varchar(3))+'*'+cast(@b as varchar(3))+'*'+cast(@a as varchar(3))
end if (@a>@c) and (@c>@b)
begin
return cast(@b as varchar(3))+'*'+cast(@c as varchar(3))+'*'+cast(@a as varchar(3))
end
if (@b>@a) and (@a>@c)
begin
return cast(@c as varchar(3))+'*'+cast(@a as varchar(3))+'*'+cast(@b as varchar(3))
end if (@b>@c) and (@c>@a)
begin
return cast(@a as varchar(3))+'*'+cast(@c as varchar(3))+'*'+cast(@b as varchar(3))
end
if (@c>@b) and (@b>@a)
begin
return cast(@a as varchar(3))+'*'+cast(@b as varchar(3))+'*'+cast(@c as varchar(3))
end if (@c>@a) and (@a>@b)
begin
return cast(@b as varchar(3))+'*'+cast(@a as varchar(3))+'*'+cast(@c as varchar(3))
end return''
endGO--============================================================================================CREATE function [dbo].[GetGroupPrice]
(
@Sortabc varchar(100)
)
returns decimal(18,4)
as
beginDECLARE @Price decimal(18,4)
DECLARE @MIN decimal(18,4)
DECLARE @AVG decimal(18,4)
--
--DECLARE @Sortabc varchar(100)
--SET @Sortabc ='1*2*15'--SELECT * FROM tb where sortabc = '1*2*6'
--update tb set price =1000.00 where id =406
--
SELECT TOP 1 @MIN =min(price) ,@AVG = avg(price) FROM tb WHERE sortabc = @Sortabc
GROUP BY sortabc
IF (@MIN <@AVG *0.75)
BEGIN
SET @price = (SELECT Top 1 Price FROM TB WHERE PRICE >@MIN AND Sortabc= @Sortabc)
END
ELSE
BEGIN
SET @Price =@AVG
END
SET @price = @price *1.15
if @price = null
return 0
return @price
end---===================================================================
-- 生成test数据
DECLARE @A INT
DECLARE @B INT
DECLARE @C INT
DECLARE X_Cursor CURSOR FOR
SELECT A FROM AData WHERE A IN(
1,2,3,4,5,6,7,8,9,10,
11,12,13,14,15,16,17,18,19,20,
21,22,23,24,25,26,26,28,29,30,
31,32,33,34,35,36,37,38,39,40,
41,42,43,44,45,46,47,48,49,50
)OPEN X_Cursor
FETCH NEXT FROM X_Cursor
INTO @A
WHILE @@FETCH_STATUS = 0
BEGIN -- BEGINT Y DECLARE Y_Cursor CURSOR FOR
SELECT A FROM AData
OPEN Y_Cursor
FETCH NEXT FROM Y_Cursor
INTO @B
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CAST(@A AS VARCHAR(10)) +'>' +CAST(@B AS VARCHAR(10))
--BEGIN Z
DECLARE Z_Cursor CURSOR FOR
SELECT A FROM AData
OPEN Z_Cursor
FETCH NEXT FROM Z_Cursor
INTO @C
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT CAST(@A AS VARCHAR(10)) +'>' +CAST(@B AS VARCHAR(10)) +'>' +CAST(@C AS VARCHAR(10))
DECLARE @Random int;
DECLARE @Upper int;
DECLARE @Lower int --1 and 999
SET @Lower = 100 -- The lowest random number
SET @Upper = 10000-- The
SELECT @Random = Round(((@Upper - @Lower -1) * Rand() + @Lower), 0) print @Random INSERT INTO [test].[dbo].[tb]([A], [B], [C],[Price]) VALUES(@A, @B, @C,@Random)
FETCH NEXT FROM Z_Cursor
INTO @C
END CLOSE Z_Cursor
DEALLOCATE Z_Cursor --END Z
FETCH NEXT FROM Y_Cursor
INTO @B
END CLOSE Y_Cursor
DEALLOCATE Y_Cursor
-- END Y
FETCH NEXT FROM X_Cursor
INTO @A
ENDCLOSE X_Cursor
DEALLOCATE X_Cursordelete from tb
where a=b or b=c or a=c
IF (@MIN <@AVG *0.75)
BEGIN
SET @price = (SELECT Top 1 Price FROM TB WHERE PRICE >@MIN AND Sortabc= @Sortabc)
END
ELSE
BEGIN
SET @Price =@AVG
END
--〉
IF (@MIN <@AVG *0.75)
BEGIN
SET @price = (SELECT Top 1 Price FROM TB WHERE PRICE >@MIN AND Sortabc= @Sortabc oeder by Price)
END
ELSE
BEGIN
SET @Price =@AVG
END2、函数[dbo].[GetGroupPrice]
SET @price = @price *1.15
这句在上面没说明
2.中的那个 SET @price = @price *1.15 ,就是返回的price需要先乘以1.15倍。有什么地方不明白的?
我下面有测试数据,你先试试。
3) 置Group的标志位
UPDATE tb Set IsGroup = 1
WHERE sortabc IN
(
SELECT sortabc FROM
tb
GROUP BY sortabc,GroupPrice HAVING COUNT(*) =6
)
4、设置Price 的值的语句好像和说明并不同
/* 说明
3)组中每个记录的值和 本组的Price 进行比较如果 本组的Price -Price <100 ,则置Price 为0
如果 本组的Price -Price >100 ,则置Price 为本组的Price -Price
*/4)设置Price 的值
--计算差值
-- UPDATE TB SET ValuePrice =Price- GroupPrice WHERE IsGroup =1
UPDATE TB SET ValuePrice =GroupPrice-Price WHERE IsGroup =1 --如果<100 ,则置成零
-- UPDATE TB SET ValuePrice =0 WHERE ValuePrice<0 OR ValuePrice<100
UPDATE TB SET ValuePrice =0 WHERE ValuePrice<100
AND IsGroup =1 --如果>0 ,则把标志位置成 0
-- UPDATE TB SET IsGroup =0 WHERE ValuePrice >0
--这个没说明
--取得计算的结果
SELECT DISTINCT sortabc,GroupPrice FROM TB
WHERE IsGroup =1 SELECT * FROM TB
WHERE IsGroup =1
@MIN <@AVG *0.75的时候,少写了个ORDER BY,应该会计算错的
UPDATE TB SET IsGroup =0 WHERE ValuePrice >0这句的意思是,如果差值ValuePrice 大于0。则 IsGroup这个标志位置成0
因为我下面的计算还需要用到这个数的。
ID A B C Price
1 1 2 3 100
2 1 3 2 200
3 2 1 3 1000
4 2 3 1 120
5 3 1 2 130
6 3 2 1 140取得结果应该是
1)合并的结果是 1*2*3 230
230 这个值是这样算的 avgPrice = (100+200+1000+120+130+140)/6
minPrice = 100
if minPrice(100) <avgPrice)0.75 (213)
price =200(第二小的值)
end price =200*1.15 =230 所以本组的合并后的值是2302)然后 本组里面所有的记录-230 ,如果大于100,则保留,如果小于100,则为0
结果如下:
ID a b c Price
1 1 2 3 0
2 1 3 2 0
3 2 1 3 780
4 2 3 1 0
5 3 1 2 0
6 3 2 1 0我就是要得到这样2个结果
可以不按照我的方法做,只要计算的速度快就行了