一条语句平均分配主表“价钱”-- 谁说我不会做了？靠，看不起他

GO
CREATE TABLE [dbo].[tb_avg_p](
 [id] [int] NOT NULL,
 [name] [varchar](50) NULL,
 [subcount] [int] NULL,
 [val] [int] NULL,
 CONSTRAINT [PK_tb_avg_p] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[tb_avg_s](
 [id] [int] NOT NULL,
 [name] [varchar](50) NULL,
 [pid] [int] NULL,
 CONSTRAINT [PK_tb_avg_s] PRIMARY KEY CLUSTERED
(
 [id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

-- 平均分配主表的“价钱”到子表
DECLARE @PID INT;
SET @PID = 2;
SELECT S.ID, S.[NAME]
,

CASE 
WHEN ROW_NUMBER() OVER (ORDER BY S.ID ) > P.VAL %P.SUBCOUNT
 THEN FLOOR(CAST(P.VAL AS DECIMAL) / P.SUBCOUNT)
ELSE Ceiling(CAST(P.VAL AS DECIMAL) / P.SUBCOUNT)
END
AS 'AVG_VAL'
 FROM TB_AVG_S S , TB_AVG_P P
 WHERE S.PID = P.ID AND  P.ID = @PID
ORDER BY S.ID