22,210
社区成员
发帖
与我相关
我的任务
分享求一条数据插入代码
A表 ：
number name
1001 小明
1002 小红
1003 小军
B表：
custom age sex
李先生 30 男
刘先生 35 男
张女士 40 女
王先生 31 男
杜先生 42 男
陈女士 33 女
C表：字段 number ， custom ， age ， sex 。
用最有效率的方法把B表中的客户数据平均分配给A表中的用户，插入到C表中并删除B表中的对应数据。
例如执行代码后：C表要变为
custom age sex number
李先生 30 男 1001
刘先生 35 男 1001
张女士 40 女 1002
王先生 31 男 1002
杜先生 42 男 1003
陈女士 33 女 1003
，最好按照顺序分配，不要出现类似下面这种情况
custom age sex number
李先生 30 男 1001
刘先生 35 男 1003
张女士 40 女 1002
王先生 31 男 1002
杜先生 42 男 1001
陈女士 33 女 1003
。
以上例子牵涉的数据量较小，但是当要插入的数据达到1万以上时对代码执行效率的要求就很高。用循环来写会很简单，但是效率很低。请各位高手赐教！
number name
1001 小明
1002 小红
1003 小军
B表：
custom age sex
李先生 30 男
刘先生 35 男
张女士 40 女
王先生 31 男
杜先生 42 男
陈女士 33 女
C表：字段 number ， custom ， age ， sex 。
用最有效率的方法把B表中的客户数据平均分配给A表中的用户，插入到C表中并删除B表中的对应数据。
例如执行代码后：C表要变为
custom age sex number
李先生 30 男 1001
刘先生 35 男 1001
张女士 40 女 1002
王先生 31 男 1002
杜先生 42 男 1003
陈女士 33 女 1003
，最好按照顺序分配，不要出现类似下面这种情况
custom age sex number
李先生 30 男 1001
刘先生 35 男 1003
张女士 40 女 1002
王先生 31 男 1002
杜先生 42 男 1001
陈女士 33 女 1003
。
以上例子牵涉的数据量较小，但是当要插入的数据达到1万以上时对代码执行效率的要求就很高。用循环来写会很简单，但是效率很低。请各位高手赐教！
...全文
请发表友善的回复…
发表回复
东那个升 2010-04-21
- 打赏
- 举报
[Quote=引用 5 楼 ldslove 的回复:]
SQL code
insert c
select b.custom , b.age ,b.sex,a.number
from (select *,row_number() over(order by getdate()) as rn from a ) a
,(select *,ntile(3) over(order by getdate()) as grpid from B……
[/Quote]
SQL code
insert c
select b.custom , b.age ,b.sex,a.number
from (select *,row_number() over(order by getdate()) as rn from a ) a
,(select *,ntile(3) over(order by getdate()) as grpid from B……
[/Quote]
declare @number int
select @number=count(number) from a
insert c
select b.custom , b.age ,b.sex,a.number
from (select *,row_number() over(order by getdate()) as rn from a ) a
,(select *,ntile(@number) over(order by getdate()) as grpid from B)b
where a.rn=b.grpid东那个升 2010-04-21
- 打赏
- 举报
insert c
select b.custom , b.age ,b.sex,a.number
from (select *,row_number() over(order by getdate()) as rn from a ) a
,(select *,ntile(3) over(order by getdate()) as grpid from B)b
where a.rn=b.grpid
select * from c
custom age sex number
---------- ----------- ---------- ----------
李先生 30 男 1001
刘先生 35 男 1001
张女士 40 女 1002
王先生 31 男 1002
杜先生 42 男 1003
陈女士 33 女 1003
(6 行受影响)
dawugui 2010-04-21
- 打赏
- 举报
--sql 2000不考虑custom, age ,sex出现重复数据。
--sql 2000考虑custom, age ,sex出现重复数据。需要临时表。
--sql 2005用row_number(),有无重复无所谓.
create table a (number varchar(10) , name varchar(10))
insert into a values('1001', '小明')
insert into a values('1002', '小红')
insert into a values('1003', '小军')
create table B(custom varchar(10) , age int,sex varchar(10))
insert into b values('李先生', 30 ,'男')
insert into b values('刘先生', 35 ,'男')
insert into b values('张女士', 40 ,'女')
insert into b values('王先生', 31 ,'男')
insert into b values('杜先生', 42 ,'男')
insert into b values('陈女士', 33 ,'女')
create table c (custom varchar(10) ,age int,sex varchar(10),number varchar(10))
insert into c
select n.custom , n.age ,n.sex , m.number from
(select t.* , px = (select count(1) from a where number < t.number) + 1 from a t) m,
(select t.* , px = (select count(1) from b where custom < t.custom or (custom = t.custom and age < t.age) or (custom = t.custom and age = t.age and sex < t.sex) ) + 1 from b t) n
where m.px = (n.px - 1) % (select count(1) from a) + 1
select * from c
drop table a , b , c
/*
custom age sex number
---------- ----------- ---------- ----------
刘先生 35 男 1001
陈女士 33 女 1001
王先生 31 男 1002
杜先生 42 男 1002
李先生 30 男 1003
张女士 40 女 1003
（所影响的行数为 6 行）
*/--sql 2000考虑custom, age ,sex出现重复数据。需要临时表。
create table a (number varchar(10) , name varchar(10))
insert into a values('1001', '小明')
insert into a values('1002', '小红')
insert into a values('1003', '小军')
create table B(custom varchar(10) , age int,sex varchar(10))
insert into b values('李先生', 30 ,'男')
insert into b values('刘先生', 35 ,'男')
insert into b values('张女士', 40 ,'女')
insert into b values('王先生', 31 ,'男')
insert into b values('杜先生', 42 ,'男')
insert into b values('陈女士', 33 ,'女')
create table c (custom varchar(10) ,age int,sex varchar(10),number varchar(10))
go
select b.* , id = identity(int,1,1) into d from b
insert into c
select n.custom , n.age ,n.sex , m.number from
(select t.* , px = (select count(1) from a where number < t.number) + 1 from a t) m,
(select t.* , px = (select count(1) from d where id < t.id) + 1 from d t) n
where m.px = (n.px - 1) % (select count(1) from a) + 1
select * from c
drop table a , b , c , d
/*
custom age sex number
---------- ----------- ---------- ----------
李先生 30 男 1001
王先生 31 男 1001
刘先生 35 男 1002
杜先生 42 男 1002
张女士 40 女 1003
陈女士 33 女 1003
（所影响的行数为 6 行）
*/--sql 2005用row_number(),有无重复无所谓.
create table a (number varchar(10) , name nvarchar(10))
insert into a values('1001', N'小明')
insert into a values('1002', N'小红')
insert into a values('1003', N'小军')
create table B(custom nvarchar(10) , age int,sex nvarchar(10))
insert into b values(N'李先生', 30 ,N'男')
insert into b values(N'刘先生', 35 ,N'男')
insert into b values(N'张女士', 40 ,N'女')
insert into b values(N'王先生', 31 ,N'男')
insert into b values(N'杜先生', 42 ,N'男')
insert into b values(N'陈女士', 33 ,N'女')
create table c (custom nvarchar(10) ,age int,sex nvarchar(10),number nvarchar(10))
insert into c
select n.custom , n.age ,n.sex , m.number from
(select t.* , px = row_number() over(order by number) from a t) m,
(select t.* , px = row_number() over(order by custom , age ,sex ) from b t) n
where m.px = (n.px - 1) % (select count(1) from a) + 1
select * from c
drop table a , b , c
/*
custom age sex number
---------- ----------- ---------- ----------
刘先生 35 男 1001
杜先生 42 男 1001
张女士 40 女 1002
王先生 31 男 1002
李先生 30 男 1003
陈女士 33 女 1003
(6 行受影响)
*/dawugui 2010-04-21
- 打赏
- 举报
--sql 2005用row_number()
create table a (number varchar(10) , name nvarchar(10))
insert into a values('1001', N'小明')
insert into a values('1002', N'小红')
insert into a values('1003', N'小军')
create table B(custom nvarchar(10) , age int,sex nvarchar(10))
insert into b values(N'李先生', 30 ,N'男')
insert into b values(N'刘先生', 35 ,N'男')
insert into b values(N'张女士', 40 ,N'女')
insert into b values(N'王先生', 31 ,N'男')
insert into b values(N'杜先生', 42 ,N'男')
insert into b values(N'陈女士', 33 ,N'女')
create table c (custom nvarchar(10) ,age int,sex nvarchar(10),number nvarchar(10))
insert into c
select n.custom , n.age ,n.sex , m.number from
(select t.* , px = row_number() over(order by number) from a t) m,
(select t.* , px = row_number() over(order by custom , age ,sex ) from b t) n
where m.px = (n.px - 1) % (select count(1) from a) + 1
select * from c
drop table a , b , c
/*
custom age sex number
---------- ----------- ---------- ----------
刘先生 35 男 1001
杜先生 42 男 1001
张女士 40 女 1002
王先生 31 男 1002
李先生 30 男 1003
陈女士 33 女 1003
(6 行受影响)
*/dawugui 2010-04-21
- 打赏
- 举报
--sql 2000
create table a (number varchar(10) , name varchar(10))
insert into a values('1001', '小明')
insert into a values('1002', '小红')
insert into a values('1003', '小军')
create table B(custom varchar(10) , age int,sex varchar(10))
insert into b values('李先生', 30 ,'男')
insert into b values('刘先生', 35 ,'男')
insert into b values('张女士', 40 ,'女')
insert into b values('王先生', 31 ,'男')
insert into b values('杜先生', 42 ,'男')
insert into b values('陈女士', 33 ,'女')
create table c (custom varchar(10) ,age int,sex varchar(10),number varchar(10))
insert into c
select n.custom , n.age ,n.sex , m.number from
(select t.* , px = (select count(1) from a where number < t.number) + 1 from a t) m,
(select t.* , px = (select count(1) from b where custom < t.custom or (custom = t.custom and age < t.age) or (custom = t.custom and age = t.age and sex < t.sex) ) + 1 from b t) n
where m.px = (n.px - 1) % (select count(1) from a) + 1
select * from c
drop table a , b , c
/*
custom age sex number
---------- ----------- ---------- ----------
刘先生 35 男 1001
陈女士 33 女 1001
王先生 31 男 1002
杜先生 42 男 1002
李先生 30 男 1003
张女士 40 女 1003
（所影响的行数为 6 行）
*/
chuifengde 2010-04-21
- 打赏
- 举报
DECLARE @s1 INT,@s2 INT
DECLARE @a table(id INT IDENTITY(1,1),number varchar(20),NAME varchar(20))
INSERT @a SELECT '1001', '小明'
union all select '1002', '小红'
union all select '1003', '小军'
DECLARE @b table(id INT IDENTITY(1,1),custom varchar(20),age int, sex varchar(10))
INSERT @b select '李先生', 30 ,'男'
union all select '刘先生', 35 ,'男'
union all select '张女士', 40 ,'女'
union all select '王先生', 31 ,'男'
union all select '杜先生', 42 ,'男'
union all select '陈女士', 33 ,'女'
SELECT @s1=count(1) FROM @a
SELECT @s2=count(1) FROM @b
SELECT * FROM @a a INNER JOIN @b b ON a.id=(b.id+1)/(@s2/@s1)
--result
/*id number NAME id custom age sex
----------- -------------------- -------------------- ----------- -------------------- ----------- ----------
1 1001 小明 1 李先生 30 男
1 1001 小明 2 刘先生 35 男
2 1002 小红 3 张女士 40 女
2 1002 小红 4 王先生 31 男
3 1003 小军 5 杜先生 42 男
3 1003 小军 6 陈女士 33 女
（所影响的行数为 6 行）*/
喜-喜 2010-04-21
- 打赏
- 举报
NTILE (Transact-SQL)
SQL Server 2005 联机丛书（2008 年 11 月）
NTILE (Transact-SQL)
将有序分区中的行分发到指定数目的组中。各个组有编号，编号从一开始。对于每一个行，NTILE 将返回此行所属的组的编号。
Transact-SQL 语法约定
语法
NTILE (integer_expression) OVER ( [ <partition_by_clause> ] < order_by_clause > )
参数
integer_expression
一个正整数常量表达式，用于指定每个分区必须被划分成的组数。integer_expression 的类型可以为 int 或 bigint。
注意：
integer_expression 只能引用 PARTITION BY 子句中的列。integer_expression 不能引用在当前 FROM 子句中列出的列。
<partition_by_clause>
将 FROM 子句生成的结果集划分成 RANK 函数适用的分区。有关 PARTITION BY 的语法，请参阅 OVER 子句 (Transact-SQL)。
< order_by_clause >
确定 NTILE 值分配到分区中各行的顺序。有关详细信息，请参阅 ORDER BY 子句 (Transact-SQL)。当在排名函数中使用 <order_by_clause> 时，不能用整数表示列。
返回类型
bigint
备注
如果分区的行数不能被 integer_expression 整除，则将导致一个成员有两种大小不同的组。按照 OVER 子句指定的顺序，较大的组排在较小的组前面。例如，如果总行数是 53，组数是 5，则前三个组每组包含 11 行，其余两个组每组包含 10 行。另一方面，如果总行数可被组数整除，则行数将在组之间平均分布。例如，如果总行数为 50，有五个组，则每组将包含 10 行。
示例
A. 将行分为组
以下示例将行分成四组。由于总行数不能被组数整除，因此第一个组将包含四行，其余每组包含三行。
复制代码
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0;
GO
B. 使用 PARTITION BY 划分结果集
以下示例将 PARTITION BY 参数添加到示例 A 中的代码。首先按 PostalCode 将行分区，然后在每个 PostalCode 内将行分成四个组。注意，OVER 子句中的 ORDER BY 对 NTILE 进行排序，SELECT 语句的 ORDER BY 对结果集进行排序。
复制代码
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName
,NTILE(4) OVER(PARTITION BY PostalCode ORDER BY SalesYTD DESC) AS 'Quartile'
,s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s
INNER JOIN Person.Contact c
ON s.SalesPersonID = c.ContactID
INNER JOIN Person.Address a
ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL
AND SalesYTD <> 0
ORDER BY LastName;
GO喜-喜 2010-04-21
- 打赏
- 举报
--------------------SQL Server数据格式化工具-------------------
---------------------------------------------------------------
-- DESIGNER :happycell188(喜喜)
-- QQ :584738179
-- Development Tool :Microsoft Visual C++ 6.0 C Language
-- FUNCTION :CONVERT DATA TO T-SQL
---------------------------------------------------------------
-- Microsoft SQL Server 2005
-- Developer Edition on Microsoft Windows XP [版本 5.1.2600]
---------------------------------------------------------------
---------------------------------------------------------------
use test
go
if object_id('test.dbo.A') is not null drop table A
-- 创建数据表
create table A
(
number int,
name char(5)
)
go
--插入测试数据
insert into A select 1001,'小明'
union all select 1002,'小红'
union all select 1003,'小军'
go
if object_id('test.dbo.B') is not null drop table B
-- 创建数据表
create table B
(
custom char(7),
age int,
sex char(3)
)
go
--插入测试数据
insert into B select '李先生',30,'男'
union all select '刘先生',35,'男'
union all select '张女士',40,'女'
union all select '王先生',31,'男'
union all select '杜先生',42,'男'
union all select '陈女士',33,'女'
go
if object_id('test.dbo.C') is not null drop table C
-- 创建数据表
create table C
(
custom char(7),
age int,
sex char(3),
number int
)
go
--代码实现
insert into C
select b.custom , b.age ,b.sex,a.number
from (select *,row_number() over(order by getdate()) as rn from a ) a
,(select *,ntile((select count(*) from A)) over(order by getdate()) as grpid from B)b
where a.rn=b.grpid
select * from C
/*测试结果
custom age sex number
---------------------
李先生 30 男 1001
刘先生 35 男 1001
张女士 40 女 1002
王先生 31 男 1002
杜先生 42 男 1003
陈女士 33 女 1003
(6 行受影响)
*/
fuchunjun 2010-04-21
- 打赏
- 举报
看过回帖，顶。
