table Anid A B C
1 a b m
1 a b n
2 a b m
table B
mid D nid
1 x 1
2 y 1
3 z 2
关联两张表 select * from A inner join B on A.nid=B.nid需要的结果
nid A B C D
1 a b m x
1 a b n y
2 a b m z
昨天提出过这个问题 不过昨天的问题是只考虑了nid=1
有网友给出了答案 因为看不大懂 所以回问 没问到正点上 花了一天大致弄懂了 现在继续提问 nid不止一个值 可能有多个
要怎么写
1 a b m
1 a b n
2 a b m
table B
mid D nid
1 x 1
2 y 1
3 z 2
关联两张表 select * from A inner join B on A.nid=B.nid需要的结果
nid A B C D
1 a b m x
1 a b n y
2 a b m z
昨天提出过这个问题 不过昨天的问题是只考虑了nid=1
有网友给出了答案 因为看不大懂 所以回问 没问到正点上 花了一天大致弄懂了 现在继续提问 nid不止一个值 可能有多个
要怎么写
2000 有点麻烦
-- Author : htl258(Tony)
-- Date : 2010-05-18 12:39:53
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
------------------------------------------------------------------------------------> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([nid] [int],[A] [nvarchar](10),[B] [nvarchar](10),[C] [nvarchar](10))
INSERT INTO [A]
SELECT '1','a','b','m' UNION ALL
SELECT '1','a','b','n' UNION ALL
SELECT '2','a','b','m'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([mid] [int],[D] [nvarchar](10),[nid] [int])
INSERT INTO [B]
SELECT '1','x','1' UNION ALL
SELECT '2','y','1' UNION ALL
SELECT '3','z','2'--SELECT * FROM [A]
--SELECT * FROM [B]-->SQL查询如下:
SELECT A.nid,A.A,A.B,A.C,B.D
FROM (
SELECT RN=row_number()OVER(PARTITION BY NID ORDER BY NID),*
FROM A
) A
LEFT JOIN (
SELECT RN=row_number()OVER(PARTITION BY NID ORDER BY nid),*
FROM B
) B
ON A.nid=B.nid AND A.RN=B.RN
/*
nid A B C D
----------- ---------- ---------- ---------- ----------
1 a b m x
1 a b n y
2 a b m z(3 行受影响)
*/
??
if object_id('[A]') is not null drop table [A]
create table [A] (nid int,A varchar(1),B varchar(1),C varchar(1))
insert into [A]
select 1,'a','b','m' union all
select 1,'a','b','n' union all
select 2,'a','b','m'
--> 测试数据: [B]
if object_id('[B]') is not null drop table [B]
create table [B] (mid int,D varchar(1),nid int)
insert into [B]
select 1,'x',1 union all
select 2,'y',1 union all
select 3,'z',2select A.Nid,A,B,C,B.D from
(select row_number() over(partition by nid order by getdate() ) id,* from [A])A
inner join
(select row_number() over(partition by nid order by getdate() ) id,* from [B])B
on A.nid=B.nid and a.id=b.idNid A B C D
----------- ---- ---- ---- ----
1 a b m x
1 a b n y
2 a b m z(3 行受影响)
nid A B C D
1 a b m x
1 a b n y
2 a b m z
if object_id('[TA]') is not null drop table [TA]
create table [TA]([nid] int,[A] varchar(1),[B] varchar(1),[C] varchar(1))
insert [TA]
select 1,'a','b','m' union all
select 1,'a','b','n' union all
select 2,'a','b','m'
--> 测试数据:[TB]
if object_id('[TB]') is not null drop table [TB]
create table [TB]([mid] int,[D] varchar(1),[nid] int)
insert [TB]
select 1,'x',1 union all
select 2,'y',1 union all
select 3,'z',2select A.nid,A,B,C,D
from (select *,flag=Row_Number() over(order by nid) from [TA]) A
left join [TB] B on
flag=b.mid/*
nid A B C D
----------- ---- ---- ---- ----
1 a b m x
1 a b n y
2 a b m z(3 行受影响)*/
drop table TA,TB
利用 nid 和所在编号 连接
'row_number' 不是可以识别的 函数名
悲剧呀 我的突然想起来公司的数据库是2000的
我们公司的数据库全是2000的 怎么搞呀 上次装2003系统的时候认不到网卡 所以就装了个XP 的
不知道怎么搞的 这个XP 的系统装不上SQL2005服务器版 我就装了个2000的
换数据库就得换系统 很麻烦 所以 2000的平台上怎么搞!!!!!!!
insert into A values(1,'a','b','m')
insert into A values(1,'a','b','n')
insert into A values(2,'a','b','m')
create table B (mid int,D varchar(1),nid int)
insert into B values(1,'x',1)
insert into B values(2,'y',1)
insert into B values(3,'z',2)
goselect m.nid , n.a , n.b , n.c , m.d from b m,
(select t.* , px = (select count(1) from a where nid < t.nid or (nid = t.nid and c < t.c)) + 1 from a t) n
where m.mid = n.pxdrop table a , b/*
nid a b c d
----------- ---- ---- ---- ----
1 a b m x
1 a b n y
2 a b m z(所影响的行数为 3 行)
*/
2.SQL2000查询:
--> 生成测试数据表: [A]
IF OBJECT_ID('[A]') IS NOT NULL
DROP TABLE [A]
GO
CREATE TABLE [A] ([nid] [int],[A] [nvarchar](10),[B] [nvarchar](10),[C] [nvarchar](10))
INSERT INTO [A]
SELECT '1','a','b','m' UNION ALL
SELECT '1','a','b','n' UNION ALL
SELECT '2','a','b','m'--> 生成测试数据表: [B]
IF OBJECT_ID('[B]') IS NOT NULL
DROP TABLE [B]
GO
CREATE TABLE [B] ([mid] [int],[D] [nvarchar](10),[nid] [int])
INSERT INTO [B]
SELECT '1','x','1' UNION ALL
SELECT '2','y','1' UNION ALL
SELECT '3','z','2'--SELECT * FROM [A]
--SELECT * FROM [B]-->SQL2000查询如下:
exec('
select rn=identity(int),* into #a from a;
select rn=identity(int),* into #b from b;
select a.nid,a.a,a.b,a.c,b.d
from #a a
left join #b b
on a.nid=b.nid and a.rn=b.rn
')
/*
nid a b c d
----------- ---------- ---------- ---------- ----------
1 a b m x
1 a b n y
2 a b m z(3 行受影响)
*/
INSERT @a select 1 ,'a', 'b', 'm'
union all select 1 ,'a', 'b', 'n'
union all select 2 ,'a', 'b', 'm' DECLARE @b table(mid INT, D varchar(20), nid INT)
INSERT @b SELECT 1, 'x', 1
union all select 2, 'y', 1
union all select 3, 'z', 2SELECT aa.nid,aa.a,aa.b,aa.c,d FROM (SELECT *,(SELECT count(1) FROM @a WHERE nid=a.nid AND c<=a.c) e FROM @a a) aa
FULL JOIN (SELECT *,(SELECT count(1) FROM @b WHERE nid=b.nid AND mid<=b.mid) e FROM @b b) bb
ON aa.nid=bb.nid AND aa.e=bb.e --result
/*nid a b c d
----------- -------------------- -------------------- -------------------- --------------------
1 a b m x
1 a b n y
2 a b m z(所影响的行数为 3 行)
*/
A 表里面的C 字段 是我从一个子表里面查询来的 行数是不确定的
B 表是A 表的一个字表 行数也是不确定的 我刚刚才测试了 10楼的答案
有问题 不好意思 我没表达清楚 其他的还没测试 慢慢测
1 a b m
1 a b n
2 a b m
table B
mid D nid
1 x 1
2 y 1
3 z 2
4 k 2
关联两张表 select * from A inner join B on A.nid=B.nid需要的结果
nid A B C D
1 a b m x
1 a b n y
2 a b m z
2 a b null k
我期望的效果是不管两张子表里面的数据有多少行
到最后连接起来成数据源的时候 C 字段 和 D 字段 都只对应nid输出一行 空白的用null填充
到最后绑定的时候不会重复
INSERT @a select 1 ,'a', 'b', 'm'
union all select 1 ,'a', 'b', 'n'
union all select 2 ,'a', 'b', 'm' DECLARE @b table(mid INT, D varchar(20), nid INT)
INSERT @b SELECT 1, 'x', 1
union all select 2, 'y', 1
union all select 3, 'z', 2
union all select 4, 'k', 2
SELECT isnull(aa.nid,cc.nid) nid,
isnull(aa.a,cc.a) a,
isnull(aa.b,cc.b) b,
aa.c,d
FROM (SELECT *,(SELECT count(1) FROM @a WHERE nid=a.nid AND c<=a.c) e FROM @a a) aa
FULL JOIN (SELECT *,(SELECT count(1) FROM @b WHERE nid=b.nid AND mid<=b.mid) e FROM @b b) bb
ON aa.nid=bb.nid AND aa.e=bb.e INNER JOIN (SELECT DISTINCT nid,a,b FROM @a)cc
ON bb.nid=cc.nid--result
/*nid a b c d
----------- -------------------- -------------------- -------------------- --------------------
1 a b m x
1 a b n y
2 a b m z
2 a b NULL k(所影响的行数为 4 行)*/