先把兩個表的數據分別存入兩入臨時表#tmpa 和 #tmpb,select identity(int,1,1) as id , * into #Tmpa from table1
select identity(int,1,1) as id , * into #Tmpb from table2
然後再
select A.Art_ID ,B.Art_UserName ,B.Art_Account
into Newtable from #Tmpa A inner join #Tmpb B on A.id = B.id
select identity(int,1,1) as id , * into #Tmpb from table2
然後再
select A.Art_ID ,B.Art_UserName ,B.Art_Account
into Newtable from #Tmpa A inner join #Tmpb B on A.id = B.id
解决方案 »
- 这个提示是什么意思呢?
- SQL 如何实现这个复杂一点的分组统计???
- 使用bcp从sqlserver到access之间数据迁移的问题??????
- 请问怎么查询出同一字段里面相同的内容?
- dts包把数据从csv导入到数据库中,如何设置导入条件??
- sql server 2005重新安装程序选择不了sql server database services
- 说说你最常用的数据库建模工具?
- 在SQL server7.0中,怎样将一个库中的结构和数据一块拷贝出来,(为了在另一地方重建一个,数据要保留)
- access中的查询语句!!
- 遇到难题,虚心求教!
- 请教日期问题
- 怎样才可以把一个数据库中的数据自制到另一个数据库中???
select identity(int,1,1) ID,* into #1 from table2select a.Art_ID, b.Art_UserName, b.Art_Account
from #1 a join #2 b on a.id=b.id
INSERT t1
SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 11CREATE TABLE t2(Art_UserName varchar(20),Art_Account varchar(20))
INSERT t2
SELECT 'Lisa', 'guesta'
UNION ALL SELECT 'Linda', 'guestb'
UNION ALL SELECT 'Mike', 'guestc'
UNION ALL SELECT 'Kenny', 'guestd'
UNION ALL SELECT 'Ive', 'gueste'SELECT * FROM t1
SELECT * FROM t2SELECT Art_ID,Art_UserName,Art_Account
FROM
(
SELECT
(SELECT COUNT(*) FROM t1 WHERE Art_ID <= a.Art_ID)
numt1,
* FROM t1 a
) a
INNER JOIN
(
SELECT
(SELECT COUNT(*) FROM t2 WHERE
(Art_UserName <= a.Art_UserName)
OR
(Art_UserName = a.Art_UserName AND Art_Account <= a.Art_Account))
numt2,
* FROM t2 a
) b
ON a.numt1 = b.numt2
ORDER BY Art_IDDROP TABLE t1,t2
select identity(int,1,1) ID,* into #1 from table1
select identity(int,1,1) ID,* into #1 from table2select a.Art_ID, b.Art_UserName, b.Art_Account
from #1 a join #2 b on a.id=b.id
gmlxf(烛光)的办法在两个表中的数据不重复时可行
但记录顺序就可能对不上.所以唯一的办法还是用临时表来处理.
(select identity(int,1,1) as id , * from table2 ) b where a.id = b .id
---
没有这样的语法。
CREATE TABLE t1(Art_ID int)
INSERT t1
SELECT 6
UNION ALL SELECT 7
UNION ALL SELECT 8
UNION ALL SELECT 9
UNION ALL SELECT 11CREATE TABLE t2(Art_UserName varchar(20),Art_Account varchar(20))
INSERT t2
SELECT 'Lisa', 'guesta'
UNION ALL SELECT 'Linda', 'guestb'
UNION ALL SELECT 'Mike', 'guestc'
UNION ALL SELECT 'Kenny', 'guestd'
UNION ALL SELECT 'Ive', 'gueste'SELECT * FROM t1
SELECT * FROM t2CREATE TABLE #T(ID int IDENTITY(1,1),Art_ID int,Art_UserName varchar(20),Art_Account varchar(20))
INSERT #T(Art_ID) SELECT * FROM t1
INSERT #T(Art_UserName,Art_Account) SELECT * FROM t2SELECT
Art_ID
,Art_UserName = ISNULL(Art_UserName,(SELECT Art_UserName FROM #T WHERE ID = a.ID + (SELECT COUNT(1)/2 FROM #T)))
,Art_Account = ISNULL(Art_Account,(SELECT Art_Account FROM #T WHERE ID = a.ID + (SELECT COUNT(1)/2 FROM #T)))
FROM #T a WHERE Art_ID IS NOT NULLDROP TABLE t1,t2,#T