表A
select * from a
得到下图
表B
select * from b
得到下图
如果select * from a left join b on a.uid = b.uid
则
我想要的结果如下
用join的话 ,有些行并不需要,我只是想拼起来就行,不用那个笛卡尔积,这个问题怎么解决啊,在线等,。,
select * from a
得到下图
表B
select * from b
得到下图
如果select * from a left join b on a.uid = b.uid
则
我想要的结果如下
用join的话 ,有些行并不需要,我只是想拼起来就行,不用那个笛卡尔积,这个问题怎么解决啊,在线等,。,
解决方案 »
- 求一条拆分SQL语句
- 求教一个打印今天和昨天价格的语句
- 求一个能把数据库表结构导成EXCEL的方法
- SQLHelper怎么取得存储过程的return回来的值啊?
- 如何利用查询获得两个表合起来之后的结果?
- 存储过程中变量传入的简单问题.在线等,解决马上给分
- 怎么同时更新不同服务器上两张表的内容?
- 在ms sql数据库中,一个企业名称表,有2万多条记录,要在另26个表中(每个表都有至少几十万条记录)查找指定月份有没有企业记录 把26个表中
- sqlserver服务器有没有超时监测的功能,或相关参数设置。。。,。。。
- sql 2000 两个表的数据对比,显示出多余的值!
- 求一条SQL语句看看能不能实现这样的要求
- 两条语句完全相同,为什么执行的总时间不一样?
from a
left join b on a.uid = b.uid
and right(a.ANAME,1)=right(b.BNAME,1)
select * from(
select row_number()over(partition by UID order by getdate()) as row,
* from a)t
full join
(select row_number()over(partition by UID order by getdate()) as row,
* from b)m
on a.row=b.row
不行 name 那一列我的数据没有这样整齐,用name这一列做没有意义
sql server 2000 row_number 不是可识别函数
from a
full join b on a.uid = b.uid
and ltrim(rtrim(replace(a.ANAME,'a','')))
=ltrim(rtrim(replace(b.BNAME,'b','')))
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'A')
BEGIN
DROP TABLE A
END
GO
CREATE TABLE A
(
AID INT IDENTITY(1,1),
UID INT,
AName VARCHAR(100)
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'B')
BEGIN
DROP TABLE B
END
GO
CREATE TABLE B
(
BID INT IDENTITY(1,1),
UID INT,
BName VARCHAR(100)
)
GOINSERT INTO A
SELECT 1,'a1' UNION
SELECT 1,'a2' UNION
SELECT 1,'a3' UNION
SELECT 1,'a4' UNION
SELECT 2,'a5' UNION
SELECT 2,'a6' UNION
SELECT 3,'a7' INSERT INTO B
SELECT 1,'b1' UNION
SELECT 1,'b2' UNION
SELECT 2,'b5' UNION
SELECT 2,'b6' UNION
SELECT 3,'b7' UNION
SELECT 3,'b8' SELECT AID,tablea.Uid,AName,BID,tableb.Uid,BName
FROM (SELECT AID,Uid,TID = ROW_NUMBER() OVER (PARTITION BY Uid ORDER BY AID), AName
FROM A) AS tablea FULL OUTER JOIN
(SELECT BID,Uid,TID = ROW_NUMBER() OVER (PARTITION BY Uid ORDER BY BID), BName
FROM B) AS tableb
ON tablea.TID = tableb.TID AND tablea.Uid = tableb.uidAID Uid AName BID Uid BName
1 1 a1 1 1 b1
2 1 a2 2 1 b2
3 1 a3 NULL NULL NULL
4 1 a4 NULL NULL NULL
5 2 a5 3 2 b5
6 2 a6 4 2 b6
7 3 a7 5 3 b7
NULL NULL NULL 6 3 b8
首先谢谢你,那个name那一列没有用,全当没有那一列,如果没有name那一列怎么做
union all select 1,1,'a1'
union all select 2,1,'a2'
union all select 3,1,'a3'
union all select 4,1,'a4'
union all select 5,2,'a5'
union all select 6,2,'a6'
union all select 7,3,'a7'select 1 BID,1 UID,'a1' ANAME into #tb2a
union all select 1,1,'a1'
union all select 2,1,'a2'
union all select 3,2,'a5'
union all select 4,2,'a6'
union all select 5,3,'a7'
union all select 6,3,'a8'select ROW_NUMBER() OVER(PARTITION BY UID order by UID,AID) ID,* into #tb1 from #tb1a
select ROW_NUMBER() OVER(PARTITION BY UID order by UID,BID) ID,* into #tb2 from #tb2aselect #tb1.AID,#tb1.UID,#tb1.ANAME,#tb2.BID,#tb2.UID,#tb2.ANAME
from (select UID,ID from (select UID,ID from #tb1
union all select UID,ID from #tb2) a group by UID,ID) tb
left join #tb1 on tb.UID=#tb1.UID and tb.ID=#tb1.ID
left join #tb2 on tb.UID=#tb2.UID and tb.ID=#tb2.ID
drop table #tb2
drop table #tb1a
drop table #tb2aselect 1 AID,1 UID,'a1' ANAME into #tb1a
union all select 1,1,'a1'
union all select 2,1,'a2'
union all select 3,1,'a3'
union all select 4,1,'a4'
union all select 5,2,'a5'
union all select 6,2,'a6'
union all select 7,3,'a7'select 1 BID,1 UID,'a1' BNAME into #tb2a
union all select 1,1,'a1'
union all select 2,1,'a2'
union all select 3,2,'a5'
union all select 4,2,'a6'
union all select 5,3,'a7'
union all select 6,3,'a8'--select ROW_NUMBER() OVER(PARTITION BY UID order by UID,AID) ID,* into #tb1 from #tb1a
--select ROW_NUMBER() OVER(PARTITION BY UID order by UID,BID) ID,* into #tb2 from #tb2aselect 0 ID,* into #tb1 from #tb1a order by UID,AID
select 0 ID,* into #tb2 from #tb2a order by UID,BIDdeclare @id int
declare @UID int
set @id=0
set @UID=0
update #tb1 set @id=case when @UID = UID then @id+1 else 1 end,
@UID =case when @UID =UID then @UID else UID end,ID=@id
from #tb1
set @id=0
set @UID=0
update #tb2 set @id=case when @UID = UID then @id+1 else 1 end,
@UID =case when @UID =UID then @UID else UID end,ID=@id
from #tb2select #tb1.AID,#tb1.UID,#tb1.ANAME,#tb2.BID,#tb2.UID,#tb2.BNAME
from (select UID,ID from (select UID,ID from #tb1
union all select UID,ID from #tb2) a group by UID,ID) tb
left join #tb1 on tb.UID=#tb1.UID and tb.ID=#tb1.ID
left join #tb2 on tb.UID=#tb2.UID and tb.ID=#tb2.ID
SQL 2000SELECT AID,tablea.Uid,AName,BID,tableb.Uid,BName
FROM (SELECT A.AID,Uid,AName,(SELECT SUM(Uid) FROM A AS TABLEA WHERE TABLEA.AID <= A.AID AND TABLEA.UID = A.UID) AS TID
FROM A) AS tablea FULL OUTER JOIN
(SELECT B.BID,Uid,BName,(SELECT SUM(Uid) FROM B AS TABLEB WHERE TABLEB.BID <= B.BID AND TABLEB.UID = B.UID) AS TID
FROM B) AS tableb
ON tablea.TID = tableb.TID AND tablea.Uid = tableb.uid
不用的话就是一对多的关系,结果自然是笛卡尔积.
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'A')
BEGIN
DROP TABLE A
END
GO
CREATE TABLE A
(
AID INT IDENTITY(1,1),
UID INT,
AName VARCHAR(100)
)
GO
IF EXISTS (SELECT 1 FROM SYSOBJECTS WHERE name = 'B')
BEGIN
DROP TABLE B
END
GO
CREATE TABLE B
(
BID INT IDENTITY(1,1),
UID INT,
BName VARCHAR(100)
)
GO--插入数据
INSERT INTO A
SELECT 1,'a1' UNION
SELECT 1,'a2' UNION
SELECT 1,'a3' UNION
SELECT 1,'a4' UNION
SELECT 2,'a5' UNION
SELECT 2,'a6' UNION
SELECT 3,'a7' INSERT INTO B
SELECT 1,'b1' UNION
SELECT 1,'b2' UNION
SELECT 2,'b5' UNION
SELECT 2,'b6' UNION
SELECT 3,'b7' UNION
SELECT 3,'b8' --查询结果
select * from A
select * from B--正解,两表拼凑
SELECT AID,tablea.Uid,AName,BID,tableb.Uid,BName
FROM (SELECT A.AID,Uid,AName,(SELECT SUM(Uid) FROM A AS TABLEA WHERE TABLEA.AID <= A.AID AND TABLEA.UID = A.UID) AS TID
FROM A) AS tablea FULL OUTER JOIN
(SELECT B.BID,Uid,BName,(SELECT SUM(Uid) FROM B AS TABLEB WHERE TABLEB.BID <= B.BID AND TABLEB.UID = B.UID) AS TID
FROM B) AS tableb
ON tablea.TID = tableb.TID AND tablea.Uid = tableb.uid