t-sql，sql表内连接电子表格

发布于 2024-09-11 18:19:39 字数 543 浏览 6 评论 0原文

我有一个数据表（AmenityData），该表的一列包含邮政部门，例如 E14 7

我还有一个包含邮政区列表的 Excel 电子表格，例如 E14

我需要从邮政区所在的 AmenityData 表中获取所有数据类似于邮政部门，例如 WHERE [PostalDistricts] + '%' LIKE [PostalSector]。

我目前使用的代码没有出现错误，只是不返回任何内容，我知道应该返回大量结果：

SELECT * FROM AmenityData As a
INNER JOIN  OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Bdzserver\db_creation\postaldistricts.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') As b
ON b.[PostalDistricts] + '%' LIKE a.[PostalSector]

我什至不确定是否可以使用 LIKE 连接表，我已经以前从未这样做过。

原文

I have a table (AmenityData) of data and a column of this table contains postalsectors e.g. E14 7

I also have an Excel spreadsheet with a list of postal districts e.g. E14

I need to get all the data out of the AmenityData table where the postal districts are LIKE the postal sectors e.g. WHERE [PostalDistricts] + '%' LIKE [PostalSector].

The code i'm using at the moment is not coming up with an error but just returning nothing and i know that there should be plenty of results returned:

SELECT * FROM AmenityData As a
INNER JOIN  OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Bdzserver\db_creation\postaldistricts.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') As b
ON b.[PostalDistricts] + '%' LIKE a.[PostalSector]

I'm not even sure if you can join tables using a LIKE, i've never done this before.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

行至春深 2024-09-18 18:19:39

我已经设法自己解决了这个问题。

我刚刚在数据中创建了一个新列，并将邮政部门添加到该列，然后将邮政部门转换为邮政区。

UPDATE [AmenityData]
SET PostalDistrict = REPLACE(PostalDistrict , ' ', '')

UPDATE [AmenityData]
SET PostalDistrict = LEFT(PostalDistrict ,LEN(PostalDistrict )-1)

完成此操作后，我运行了以下命令

SELECT * FROM AmenityData As a
INNER JOIN  TypesToGroups As b
ON a.ClassCode =  b.FacilityTypeID
INNER JOIN Groups As c
ON b.GroupID = c.GroupID
INNER JOIN  OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Bdzserver\db_creation\postaldistricts.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') As d
ON d.[PostalDistricts] = a.[PostalDistrict]

，一切正常。

我仍然想知道我原来的问题是否可能，但是否有人可以提供帮助。

J。

I've managed to sort this out myself the long way round.

I just created a new column in the data and added the postalsectors to this column, then converted the postal sectors to postal districts

UPDATE [AmenityData]
SET PostalDistrict = REPLACE(PostalDistrict , ' ', '')

UPDATE [AmenityData]
SET PostalDistrict = LEFT(PostalDistrict ,LEN(PostalDistrict )-1)

Once this was done I ran the following

SELECT * FROM AmenityData As a
INNER JOIN  TypesToGroups As b
ON a.ClassCode =  b.FacilityTypeID
INNER JOIN Groups As c
ON b.GroupID = c.GroupID
INNER JOIN  OPENROWSET ('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=\\Bdzserver\db_creation\postaldistricts.xls;HDR=YES', 'SELECT * FROM [Sheet1$]') As d
ON d.[PostalDistricts] = a.[PostalDistrict]

and all worked fine.

I would still like to know if my original question is possible though if anyone can help.

回复收藏 0 原文