[python] 求助 有两个mssql表,找到里面相同的字段值
本帖最后由 maniachhz 于 2013-01-23 17:53:55 编辑 有两个mssql表(table_A表与table_B表),每个表有几万行数据, 分别处在局域网上的两台服务器上.
两个表都包含有两个字段col与col2,字段已经设置单一索引. 但两个字段值也不能相同, 即col与col2值不能相同.
由于其它原因,目前两个字段有相同的值,table_A与table_B两个字段也有相同,现在想找出这些值,然后导出到excel文件里.
我尝试先从table_A 查找两字段,如何有相同值输出到屏幕,使用for嵌套循环
curs.execute('SELECT * FROM %s' % table)
lst = []
i = 0
for data in curs:
lst.append(data)
i += 1
for j1 in range(i): # i 为table_A的总行数
for j2 in range(i):
m=re.search(lst[j1]['W_MAC'], lst[j2]['E_MAC'], re.IGNORECASE ) # lst 保存 table_A所有行数据, 索引可以为数字或者字符如'W_MAC', 'E_MAC'
if bool(m):
print(lst[j1]['W_MAC']+ ' '+ str(j1)),
print(' ' + lst[j2]['E_MAC']+ ' '+ str(j2))
hasha = {}
def do_find(curs, tnum):
global hasha
lines = 1
for data in curs:
wmac = data['W_MAC']
emac = data['E_MAC']
if exists(hasha[wmac]):
for lst in hasha[wmac]:
print '%s\t%d:%d:%d\t%d:%d:%d' % (wmac, tnum, lines, 0, lst[0], lst[2], lst[1])
hasha[wmac].append([tnum, 0, lines])
else:
hasha[wmac] = [[tnum, 0, lines]]
if exists(hasha[emac]):
for lst in hasha[emac]:
print '%s\t%d:%d:%d\t%d:%d:%d' % (emac, tnum, lines, 1, lst[0], lst[2], lst[1])
hasha[emac].append([tnum, 1, lines])
else:
hasha[emac] = [[tnum, 1, lines]]
lines += 1
curs.execute('SELECT * FROM %s' % tablea)
do_find(curs, 0)
curs.execute('SELECT * FROM %s' % tableb)
do_find(curs, 1)
-- 建立测试A表
USE [TESTDB]
GO
/****** Object: Table [dbo].[Table_a] Script Date: 01/24/2013 16:36:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_a](
[phone] [nvarchar](50) NULL,
[mac] [nvarchar](50) NULL,
[lastdate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table_a] ADD CONSTRAINT [DF_Table_a_lastdate] DEFAULT (getdate()) FOR [lastdate]
GO
-- 建立测试B表
USE [TESTDB]
GO
/****** Object: Table [dbo].[Table_b] Script Date: 01/24/2013 16:36:39 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_b](
[phone] [nvarchar](50) NULL,
[mac] [nvarchar](50) NULL,
[lastdate] [datetime] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Table_b] ADD CONSTRAINT [DF_Table_b_lastdate] DEFAULT (getdate()) FOR [lastdate]
GO
-- 执行SQL语句
WITH
CT1 AS
(
SELECT PHONE, MAC FROM TABLE_A(NOLOCK)
),
CT2 AS
(
SELECT PHONE, MAC FROM TABLE_B(NOLOCK)
),
CT3 AS
(
SELECT CT1.PHONE, CT1.MAC FROM CT1
WHERE PHONE = MAC
),
CT4 AS
(
SELECT CT2.PHONE, CT2.MAC FROM CT2
WHERE PHONE = MAC
),
CT5 AS
(
SELECT CT1.PHONE, CT1.MAC FROM CT1, CT2
WHERE CT1.PHONE = CT2.PHONE AND CT1.MAC = CT2.MAC
)
SELECT CT3.PHONE, CT3.MAC FROM CT3
UNION
SELECT CT4.PHONE, CT4.MAC FROM CT4
UNION
SELECT CT5.PHONE, CT5.MAC FROM CT5