SQL语句循环问题，在线急等！100分！

cyancrystal 2013-12-08 10:41:30
A表：
Aid type cname sname
B表:
Bid sname rank
C表（统计表）：
Cid sname type1,type2,type3,type4


假设：
A表当天新增了1000笔客户数据，其中A.type = 1的100笔，A.type =2的300笔，A.type =3的500笔，A.type =4的100笔
B表里面当天b.rank=2的有20人，b.rank=1的有15人，b.rank=0的有5人，共40人
B表sname是来自于A表sname的

希望创建一个sql的作业，每天0点自动运行sql语句，实现：

1、把当天A表的1000笔客户数据分配出去，B表里面b.rank=2+b.rank=1的有35人参与客户分配，平均每个人可以分到1000/35个，b.rank=0的人不参与分配
2、A.type = 1的数据只能平均分配给b.rank=2的20人，意味着b.rank=2的人每人大约能分配到100/20个客户，剩下的从其它type值里面分配，分配方式均为随机分配
3、分配的方式为把b.sname的值写入a.sname
4、每个b.sname分配到的客户写入C表来作为统计日志

举例如下：
b.sname="john"的当天共分配到了100/20+900/35笔≈31笔数据（也就是A表1000笔数据里面有28笔的A.sname="john"）
其中：数据分配到了A.type = 1的5笔（100/20=5）,A.type = 2的9笔（300/35≈9），A.type = 3的14笔（500/35≈14）,A.type = 4的3笔（100/35≈3）共约31笔
将sname="john"的type值对应的1，2，3，4写入C表对应的type1,type2,type3,type4


给出语句马上给分，谢谢！

...全文
667 21 打赏 收藏 转发到动态 举报
写回复
用AI写文章
21 条回复
切换为时间正序
请发表友善的回复…
发表回复
唐诗三百首 2014-03-12
  • 打赏
  • 举报
 回复
难点在于需确保分配结果的2个均匀,力气活.
cyancrystal 2014-01-03
  • 打赏
  • 举报
 回复
自己顶一下！
orochiheart 2013-12-31
  • 打赏
  • 举报
 回复
引用 10 楼 s1242413556 的回复:
[quote=引用 9 楼 t101lian 的回复:] [quote=引用 8 楼 cyancrystal 的回复:] [quote=引用 7 楼 feiazifeiazi 的回复:] [code=sql] drop table a; drop table b; drop table c;
楼上的大哥我测试了完全不行哦，麻烦大家再帮我一下！ 谢谢谢谢！ [/quote] 不明觉厉。。帮你顶上去， [/quote]不明觉厉。。帮你顶上去，[/quote] 不明觉厉。。帮你顶上去，
cyancrystal 2013-12-30
  • 打赏
  • 举报
 回复
自己顶一下！
fcuandy 2013-12-23
  • 打赏
  • 举报
 回复
引用 15 楼 cyancrystal 的回复:
[quote=引用 12 楼 fcuandy 的回复:] 至于关于是否当天数据的判断， 只是个过滤，我就没考滤写到语句里了。
能给出完整的语句吗？我直接创建一个作业放进去就可以执行了，万分感谢！[/quote] 我都写成这样了，你还问我要完整语句？ 结果都出来了
cyancrystal 2013-12-22
  • 打赏
  • 举报
 回复
引用 14 楼 feiazifeiazi 的回复:
额。怎么不行？！ 你那里报错？结果不对？
大哥谢谢你，辛苦回复这么多！ 补充说明一下： A表还有一个字段是datetime，通过时间来判断当天一公生成了多少笔新的数据，我举例说的1000笔不是说一定是1000笔，也可能是1200笔 2、A.type = 1的数据只能平均分配给b.rank=2的20人，意味着b.rank=2的人每人大约能分配到100/20个客户，剩下的从其它type值里面分配，分配方式均为随机分配 剩下的A.type=2,3,4的数据（假设还有900笔），每个type的值都平均分配给b.rank=2的20人与b.rank=1的15人 如果还有疑问我继续解释
cyancrystal 2013-12-22
  • 打赏
  • 举报
 回复
引用 12 楼 fcuandy 的回复:
至于关于是否当天数据的判断， 只是个过滤，我就没考滤写到语句里了。
能给出完整的语句吗？我直接创建一个作业放进去就可以执行了，万分感谢！
fcuandy 2013-12-18
  • 打赏
  • 举报
 回复
至于关于是否当天数据的判断， 只是个过滤，我就没考滤写到语句里了。
fcuandy 2013-12-18
  • 打赏
  • 举报
 回复 
IF OBJECT_ID('ta','u') IS NOT NULL
	DROP TABLE ta
GO
IF OBJECT_ID('tb','u') IS NOT NULL
	DROP TABLE tb
GO
IF OBJECT_ID('tc','u') IS NOT NULL
	DROP TABLE tc
GO
/*
A表：
Aid type cname sname
B表:
Bid sname rank
C表（统计表）：
Cid sname type1,type2,type3,type4*/
CREATE TABLE ta(
Aid INT IDENTITY(1,1),
type INT,
cname VARCHAR(10),
sname VARCHAR(10)
)
INSERT ta SELECT TOP 1000 CAST(RAND(CHECKSUM(NEWID())) * 4 + 1 AS INT),LEFT(NEWID(),10),''
	FROM sys.objects,sys.columns
GO
CREATE TABLE tb(
bid INT IDENTITY(1,1),
sname VARCHAR(10),
brank INT)
GO
INSERT tb SELECT TOP 40 LEFT(NEWID(),10),CAST(RAND(CHECKSUM(NEWID())) * 3 + 1 AS INT)
	FROM sys.objects
GO
CREATE TABLE tc
(
cid INT IDENTITY(1,1),
sname VARCHAR(10),
type1 INT,
type2 INT,
type3 INT,
type4 INT
)
GO
DECLARE @a INT,@b INT,@c INT
DECLARE @a1 INT,@b1 INT,@c1 INT

SELECT @a=COUNT(*) FROM ta WHERE type=1
SELECT @b=COUNT(*) FROM tb WHERE brank=2
SELECT @c=(@a+@b-1)/@b

SELECT @a1=COUNT(*) FROM ta WHERE type>1
SELECT @b1=COUNT(*) FROM tb WHERE brank>0
SELECT @c1=(@a1+@b1-1)/@b1

/*
上面这几行变量定义也可以不要，代到下面查询里，一条语句就可以出来，只是套进去写在下面的语句里显的繁琐，所以单独提出来
*/

SELECT Aid,type,cname,bid,b.sname,brank FROM 
(
SELECT *,idx=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM ta WHERE type = 1
)a,
(
SELECT *,idx=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM tb WHERE brank=2
)b
WHERE (a.idx + @c-1)/@c= b.idx

UNION ALL 
SELECT Aid,type,cname,bid,b.sname,brank FROM 
(
SELECT *,idx=ROW_NUMBER() OVER(ORDER BY GETDATE()) FROM ta WHERE type >1
)a,
(
SELECT *,idx=ROW_NUMBER() OVER(ORDER BY NEWID()) FROM tb WHERE brank>0
)b
WHERE (a.idx + @c1-1)/@c1=b.idx

SELECT @@ROWCOUNT
/*
1000
上面的结果集就是你要插入到c表的数据, 然后联表把a表更新即可
*/
帝瓜 2013-12-18
  • 打赏
  • 举报
 回复
引用 9 楼 t101lian 的回复:
[quote=引用 8 楼 cyancrystal 的回复:] [quote=引用 7 楼 feiazifeiazi 的回复:] [code=sql] drop table a; drop table b; drop table c;
楼上的大哥我测试了完全不行哦，麻烦大家再帮我一下！ 谢谢谢谢！ [/quote] 不明觉厉。。帮你顶上去， [/quote]不明觉厉。。帮你顶上去，
飞啊子 2013-12-18
  • 打赏
  • 举报
 回复
引用 8 楼 cyancrystal 的回复:
[quote=引用 7 楼 feiazifeiazi 的回复:] [code=sql] drop table a; drop table b; drop table c;
楼上的大哥我测试了完全不行哦，麻烦大家再帮我一下！ 谢谢谢谢！ [/quote] 额。怎么不行？！ 你那里报错？结果不对？
LongRui888 2013-12-18
  • 打赏
  • 举报
 回复
牛人很多啊
t101lian 2013-12-17
  • 打赏
  • 举报
 回复
引用 8 楼 cyancrystal 的回复:
[quote=引用 7 楼 feiazifeiazi 的回复:] [code=sql] drop table a; drop table b; drop table c;
楼上的大哥我测试了完全不行哦，麻烦大家再帮我一下！ 谢谢谢谢！ [/quote] 不明觉厉。。帮你顶上去，
cyancrystal 2013-12-16
  • 打赏
  • 举报
 回复
引用 7 楼 feiazifeiazi 的回复:
[code=sql] drop table a; drop table b; drop table c;
楼上的大哥我测试了完全不行哦，麻烦大家再帮我一下！ 谢谢谢谢！
飞啊子 2013-12-09
  • 打赏
  • 举报
 回复 


  
 drop table a;
 drop table b;
 drop table c;


CREATE TABLE a(aid INT  PRIMARY KEY,[type] INT,sname NVARCHAR(50))
CREATE TABLE b(bid INT  PRIMARY KEY,sname NVARCHAR(50),[rank] INT)

CREATE TABLE c(cid INT IDENTITY(1,1)  PRIMARY KEY,[Type] int,sname NVARCHAR(50),objectId int);

GO

INSERT INTO [dbo].[a] ([aid],[type],[sname])
SELECT ((SELECT ISNULL(MAX(ocxa.[aid]),10) FROM a ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,1,null FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<100;

INSERT INTO [dbo].[a] ([aid],[type],[sname])
SELECT ((SELECT ISNULL(MAX(ocxa.[aid]),10) FROM a ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,2,null FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<300;

INSERT INTO [dbo].[a] ([aid],[type],[sname])
SELECT ((SELECT ISNULL(MAX(ocxa.[aid]),10) FROM a ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,3,null FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<500;


INSERT INTO [dbo].[a] ([aid],[type],[sname])
SELECT ((SELECT ISNULL(MAX(ocxa.[aid]),0) FROM a ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,4,null FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<100;


INSERT INTO [dbo].[b] ([bid],[sname],[rank])
SELECT ((SELECT ISNULL(MAX(ocxa.[bid]),0) FROM b ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,'2_'+ltrim(NEWID()),2  FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<20;


INSERT INTO [dbo].[b] ([bid],[sname],[rank])
SELECT ((SELECT ISNULL(MAX(ocxa.[bid]),0) FROM b ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,'1_'+ltrim(NEWID()),1  FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<15;


INSERT INTO [dbo].[b] ([bid],[sname],[rank])
SELECT ((SELECT ISNULL(MAX(ocxa.[bid]),0) FROM b ocxa)+ROW_NUMBER() OVER ( ORDER BY GETDATE())) AS rId
,'0_'+ltrim(NEWID()),0  FROM MASTER.dbo.spt_values sv WHERE sv.[type]='P' AND sv.number<5;


PRINT '---------------'
  
 IF OBJECT_ID('tempdb..#b') IS NOT NULL
 BEGIN
 	DROP TABLE #b
 END
  
 SELECT * INTO #b FROM b WHERE b.[rank]=2;
  
 DECLARE @acount INT=0;
 DECLARE @bcount INT=0;
 SET @acount=(SELECT COUNT(1) FROM a WHERE a.type=1 AND a.sname IS NULL )
 SET @bcount=(SELECT COUNT(1) FROM b WHERE b.[rank]=2)
  
 --SELECT ((@acount+@bcount-1)/@bcount);--总页数
 /*平均分配*/
;with tx as(
	SELECT a.*,b.bid,b.sname AS snameNew FROM (
		SELECT a.*,ROW_NUMBER() OVER ( ORDER BY a.aid) Rn FROM a 
		where a.type=1 AND a.sname IS NULL 
	) a
	INNER JOIN (
		SELECT b.*,ROW_NUMBER() OVER ( ORDER BY r.number ,b.bid) Rn 
		FROM #b b
		INNER JOIN (SELECT * FROM MASTER.dbo.spt_values sv 
					WHERE sv.[type]='P' 
					AND sv.number<((@acount+@bcount-1)/@bcount))
		   r ON 1=1
	) b ON a.Rn=b.Rn
)
UPDATE tx SET sname=snameNew 
OUTPUT INSERTED.type,INSERTED.sname,INSERTED.aid INTO c;--select * from tx;
 

  TRUNCATE TABLE #b;
 
 INSERT INTO #b SELECT * FROM b WHERE b.[rank] IN(1,2);
 SET @acount=(SELECT COUNT(1) FROM a WHERE a.type<>1  AND a.sname IS NULL )
 SET @bcount=(SELECT COUNT(1) FROM b WHERE b.[rank] in(1,2))
 
/*随机分配*/
;with tx as(
	SELECT a.*,b.bid,b.sname AS snameNew FROM (
		SELECT a.*,ROW_NUMBER() OVER ( ORDER BY a.aid) Rn FROM a 
		WHERE a.type<>1 AND a.sname IS NULL 
	) a
	INNER JOIN (
		SELECT b.*,ROW_NUMBER() OVER ( ORDER BY NEWID()) Rn FROM #b b
		INNER JOIN (SELECT * FROM MASTER.dbo.spt_values sv 
					WHERE sv.[type]='P' 
					AND sv.number<((@acount+@bcount-1)/@bcount))
		   r ON 1=1
	) b ON a.Rn=b.Rn
)--SELECT * FROM tx ORDER BY  bid;
UPDATE tx SET sname=snameNew 
OUTPUT INSERTED.type,INSERTED.sname,INSERTED.aid INTO c;--select * from tx;
 
 
SELECT c.sname
,count(CASE WHEN c.type=1 THEN 1 END) type1
,count(CASE WHEN c.type=2 THEN 1 END) type2
,count(CASE WHEN c.type=3 THEN 1 END) type3
,count(CASE WHEN c.type=4 THEN 1 END) type4
,COUNT(1) ct
FROM c GROUP BY c.sname;


--1. MASTER.dbo.spt_values sv 此表，请楼主 自行 替换 成 Nums辅助表。
--2.修改了楼主的 C表（统计表）的表结构。
cyancrystal 2013-12-08
  • 打赏
  • 举报
 回复
坐等大侠解决啊
cyancrystal 2013-12-08
  • 打赏
  • 举报
 回复
引用 2 楼 yupeigu 的回复:
2、A.type = 1的数据只能平均分配给b.rank=2的20人，意味着b.rank=2的人每人大约能分配到100/20个客户，剩下的从其它type值里面分配，分配方式均为随机分配 这个剩下的是什么意思
就是说剩下的900笔数据平均分配给35个人，请参考举例
cyancrystal 2013-12-08
  • 打赏
  • 举报
 回复
引用 3 楼 ap0405140 的回复:
请教LZ一个问题: A表中如何判断是当天新增的记录.
A表里面还有一个是时间字段datetime没有贴出来，抱歉
唐诗三百首 2013-12-08
  • 打赏
  • 举报
 回复
请教LZ一个问题: A表中如何判断是当天新增的记录.
LongRui888 2013-12-08
  • 打赏
  • 举报
 回复
2、A.type = 1的数据只能平均分配给b.rank=2的20人，意味着b.rank=2的人每人大约能分配到100/20个客户，剩下的从其它type值里面分配，分配方式均为随机分配 这个剩下的是什么意思
加载更多回复（1）
7天带你玩转Mysql数据库之SQL语句
您观看课程学习后 免费入群领取【超全Python资料包+17本学习电子书】 七年SQL语句编写经验。满满的干货！ 在网上找了好多SQL数据库的教程，但你还会面临这样的问题： 1. 自己写了SQL却不知道对不对。 2. 在上线后...
SQL语句中的循环
SQL语句中的循环 SQL语句中的循环类似于foreach循环，可以循环遍历某个表并进行新增、修改和删除的操作。 SQL语句中的循环，使用SQL的游标来实现。 看示例： declare @ID int;--声明变量(@名称 类型) begin--开始 ...
oracle单纯的sql能写循环吗,循环执行sql语句
如何用Sql语句循环执行语句这个问题涉及到sql语句的循环执行语句的用法。sql语句中的循环和其他编程语言的原理是类似的，只不过写法上有点区别。1.定义循环时需要用到的变量并赋值：declare @i intset @uid=12.sql...
sql语句循环的问题：foreach的用法
sql语句循环foreach
mysql sql语句for循环语句怎么写_SQL中的循环、for循环、游标
我们使用SQL语句处理数据时，可能会碰到一些需要循环遍历某个表并对其进行相应的操作(添加、修改、删除)，这时我们就需要用到咱们在编程中常常用的for或foreach，但是在SQL中写循环往往显得那么吃力，翻遍网上的资料...
应用实例

27,579

社区成员

68,558

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 应用实例
社区管理员
  • 应用实例社区
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章