根据t_jbqk表条件查询t_qyrk表符合条件的插入t_children表中,t_children并按时间排序 我觉得应该用per_code+family_code来做判断条件. 解决方案 » 免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货 --查询select t.per_code , t.sn , right('00' + cast(t.sn as varchar),2) birth_order , t.name , t.birthday , getdate() modif_date from( select m.* , sn = (select count(1) from ( select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.per_code = t2.per_code and t1.family_code = t2.family_code and rela_to_hh in (2,3) ) n where n.per_code = m.per_code and n.family_code = m.family_code and n.birthday < m.birthday ) + 1 from ( select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.per_code = t2.per_code and t1.family_code = t2.family_code and rela_to_hh in (2,3) ) m) t--如果是插入。insert into t_childrenselect t.per_code , t.sn , right('00' + cast(t.sn as varchar),2) birth_order , t.name , t.birthday , getdate() modif_date from( select m.* , sn = (select count(1) from ( select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.per_code = t2.per_code and t1.family_code = t2.family_code ) n where n.per_code = m.per_code and n.family_code = m.family_code and n.birthday < m.birthday ) + 1 from ( select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.per_code = t2.per_code and t1.family_code = t2.family_code ) m) t --以下为查询,如果是插入到表t_children,使用我在三楼后面的方法即可。create table t_jbqk(per_code varchar(20) , family_code varchar(20) , name varchar(20) , code varchar(20))insert into t_jbqk values('107200000002' , '19001' , '蒙小淮' , '1072000002') insert into t_jbqk values('107200000006' , '19002' , '蒙志坤' , '1072000002') insert into t_jbqk values('107200000009' , '19004' , '蒙凤兰' , '1072000002') insert into t_jbqk values('107200000036' , '17003' , '蒙小兰' , '1072040017') insert into t_jbqk values('107200000042' , '19001' , '黄 贞' , '1072040019') insert into t_jbqk values('107204000206' , '02019' , '韦凤兰' , '1072040014') create table t_qyrk(per_code varchar(20) , family_code varchar(20) , name varchar(20) , code varchar(20) , sex int , rela_to_hh int , birthday datetime)insert into t_qyrk values('107200000001' , '19001' , '黎展中' , '1072000002' , 1 , 0, '1969-01-10') insert into t_qyrk values('107200000002' , '19001' , '蒙小淮' , '1072000002' , 2 , 1, '1970-12-25') insert into t_qyrk values('107200000003' , '19001' , '黎寿容' , '1072000002' , 1 , 2, '1998-10-12') insert into t_qyrk values('107200000004' , '19001' , '蒙小红' , '1072000002' , 2 , 3, '1996-02-12') insert into t_qyrk values('107200000005' , '19002' , '黎中华' , '1072000002' , 1 , 0, '1969-01-10') insert into t_qyrk values('107200000006' , '19002' , '蒙志坤' , '1072000002' , 2 , 1, '1980-01-12') insert into t_qyrk values('107200000007' , '19002' , '黎亚弟' , '1072000002' , 1 , 2, '1998-10-12') insert into t_qyrk values('107200000008' , '19002' , '蒙小兰' , '1072000002' , 2 , 2, '1996-02-12') goselect t.per_code , t.sn , right('00' + cast(t.sn as varchar),2) birth_order , t.name , t.birthday , getdate() modif_date from( select m.* , sn = (select count(1) from ( select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.code = t2.code and t1.family_code = t2.family_code and rela_to_hh between 2 and 3 ) n where n.per_code = m.per_code and n.family_code = m.family_code and n.birthday < m.birthday ) + 1 from ( select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.code = t2.code and t1.family_code = t2.family_code and rela_to_hh between 2 and 3 ) m) torder by per_code , sndrop table t_jbqk , t_qyrk/*per_code sn birth_order name birthday modif_date -------------------- ----------- ----------- -------------------- ------------------------------------------------------ ------------------------------------------------------ 107200000002 1 01 蒙小红 1996-02-12 00:00:00.000 2008-07-19 23:35:26.250107200000002 2 02 黎寿容 1998-10-12 00:00:00.000 2008-07-19 23:35:26.250107200000006 1 01 蒙小兰 1996-02-12 00:00:00.000 2008-07-19 23:35:26.250107200000006 2 02 黎亚弟 1998-10-12 00:00:00.000 2008-07-19 23:35:26.250(所影响的行数为 4 行)*/ 或我可以选择code中的内容生成t_children中的数据 t_children数据的式样 /* code sn sn1 name ---- ----------- ---- 001 1 01 a1 001 2 02 a2 001 3 03 a3 002 1 01 b1 002 2 02 b2 003 1 01 c1 003 2 02 c2 003 3 03 c3 003 4 04 c4 */ --至于你这个要求,没看明白你的code是怎么来的. sql server 导出一个表的(包含数据) 变量赋值问题 sql语句问题 急!!!!!! 邹老大,你那个IP转换数字函数有点毛病! 数据库回复出错 ̄! SQL Server中如何存入jpg图片? 数据更新问题-----在线等待! 不能在事务内部执行过程'sp_adlinkedserver' 是怎么会事啊 询问一个数据库功能实现的问题 简单问题,在线给分! sql2005 远程连接问题(外网) 从一个表查询插入另一个表,SN字段自动生成ID
select t.per_code , t.sn , right('00' + cast(t.sn as varchar),2) birth_order , t.name , t.birthday , getdate() modif_date from
(
select m.* , sn = (select count(1) from
(
select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.per_code = t2.per_code and t1.family_code = t2.family_code and rela_to_hh in (2,3)
) n where n.per_code = m.per_code and n.family_code = m.family_code and n.birthday < m.birthday
) + 1 from
(
select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.per_code = t2.per_code and t1.family_code = t2.family_code and rela_to_hh in (2,3)
) m
) t--如果是插入。
insert into t_children
select t.per_code , t.sn , right('00' + cast(t.sn as varchar),2) birth_order , t.name , t.birthday , getdate() modif_date from
(
select m.* , sn = (select count(1) from
(
select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.per_code = t2.per_code and t1.family_code = t2.family_code
) n where n.per_code = m.per_code and n.family_code = m.family_code and n.birthday < m.birthday
) + 1 from
(
select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.per_code = t2.per_code and t1.family_code = t2.family_code
) m
) t
create table t_jbqk(per_code varchar(20) , family_code varchar(20) , name varchar(20) , code varchar(20))
insert into t_jbqk values('107200000002' , '19001' , '蒙小淮' , '1072000002')
insert into t_jbqk values('107200000006' , '19002' , '蒙志坤' , '1072000002')
insert into t_jbqk values('107200000009' , '19004' , '蒙凤兰' , '1072000002')
insert into t_jbqk values('107200000036' , '17003' , '蒙小兰' , '1072040017')
insert into t_jbqk values('107200000042' , '19001' , '黄 贞' , '1072040019')
insert into t_jbqk values('107204000206' , '02019' , '韦凤兰' , '1072040014')
create table t_qyrk(per_code varchar(20) , family_code varchar(20) , name varchar(20) , code varchar(20) , sex int , rela_to_hh int , birthday datetime)
insert into t_qyrk values('107200000001' , '19001' , '黎展中' , '1072000002' , 1 , 0, '1969-01-10')
insert into t_qyrk values('107200000002' , '19001' , '蒙小淮' , '1072000002' , 2 , 1, '1970-12-25')
insert into t_qyrk values('107200000003' , '19001' , '黎寿容' , '1072000002' , 1 , 2, '1998-10-12')
insert into t_qyrk values('107200000004' , '19001' , '蒙小红' , '1072000002' , 2 , 3, '1996-02-12')
insert into t_qyrk values('107200000005' , '19002' , '黎中华' , '1072000002' , 1 , 0, '1969-01-10')
insert into t_qyrk values('107200000006' , '19002' , '蒙志坤' , '1072000002' , 2 , 1, '1980-01-12')
insert into t_qyrk values('107200000007' , '19002' , '黎亚弟' , '1072000002' , 1 , 2, '1998-10-12')
insert into t_qyrk values('107200000008' , '19002' , '蒙小兰' , '1072000002' , 2 , 2, '1996-02-12')
goselect t.per_code , t.sn , right('00' + cast(t.sn as varchar),2) birth_order , t.name , t.birthday , getdate() modif_date from
(
select m.* , sn = (select count(1) from
(
select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.code = t2.code and t1.family_code = t2.family_code and rela_to_hh between 2 and 3
) n where n.per_code = m.per_code and n.family_code = m.family_code and n.birthday < m.birthday
) + 1 from
(
select t1.per_code , t1.family_code , t2.name , t2.birthday from t_jbqk t1 , t_qyrk t2 where t1.code = t2.code and t1.family_code = t2.family_code and rela_to_hh between 2 and 3
) m
) t
order by per_code , sndrop table t_jbqk , t_qyrk/*
per_code sn birth_order name birthday modif_date
-------------------- ----------- ----------- -------------------- ------------------------------------------------------ ------------------------------------------------------
107200000002 1 01 蒙小红 1996-02-12 00:00:00.000 2008-07-19 23:35:26.250
107200000002 2 02 黎寿容 1998-10-12 00:00:00.000 2008-07-19 23:35:26.250
107200000006 1 01 蒙小兰 1996-02-12 00:00:00.000 2008-07-19 23:35:26.250
107200000006 2 02 黎亚弟 1998-10-12 00:00:00.000 2008-07-19 23:35:26.250(所影响的行数为 4 行)
*/
/*
code sn sn1 name
---- ----------- ----
001 1 01 a1
001 2 02 a2
001 3 03 a3
002 1 01 b1
002 2 02 b2
003 1 01 c1
003 2 02 c2
003 3 03 c3
003 4 04 c4
*/ --至于你这个要求,没看明白你的code是怎么来的.