用sql连接表
问题描述:
我有两张表我想加入。一个包含名为“客户”的客户。另一张表“Customeraccounts”。这些表格与客户ID有关系。喜欢这个。用sql连接表
Customers
1, john, Smith
CustomerAccounts
1, 1, 12345678
2, 1, 98765432
我如何加入表,输出将是:
1, john, smith, 12345678, 98765432
答
Select
Acc.ID,
Cust.Forname,
Cust.Name,
Acc.Number
From CustomerAccount Acc
Inner Join Customers Cust
On Acc.ID_CUstomer = Cust.ID
我假设你的列中
CustomerAccount ID | ID_Customer |数
客户 ID |的forName |名称
我认为它会工作
答
也许这会为你工作:
SELECT Customers.Id,
Customers.LastName,
Customers.FirstName,
STUFF((SELECT ',' + CustomerAccounts.Number
FROM CustomerAccounts
WHERE CustomerAccounts.CustomerId = Customers.Id
FOR XML PATH('')
),1,1,'')
AS AllCustomerAccounts
FROM Customers
答
在的MySQL你可以做
SELECT
Cust.ID
, Cust.Forname
, Cust.Name
, GROUP_CONCAT(Acc.Number) as AccNumber
FROM CustomerAccount Acc
INNER JOIN Customers Cust ON Acc.ID_Customer = Cust.ID
GROUP BY Cust.id
在SQL服务器你将不得不调整它有点做:
SELECT
Cust.ID,
, Cust.Forname
, Cust.Name
, AccNumber = replace
((SELECT A2.number AS [data()]
FROM CustomerAccount A2
INNER JOIN Customers C2 ON A2.ID_Customer = C2.ID
WHERE Cust.id = C2.id
ORDER BY A2.number FOR xml path('')), ' ', REQUIRED SEPERATOR)
FROM CustomerAccount Acc
INNER JOIN Customers Cust ON Acc.ID_Customer = Cust.ID
GROUP BY Cust.id, cust.Forname, cust.Name
也看到这个问题:Simulating group_concat MySQL function in Microsoft SQL Server 2005?
你尝试过什么?什么没有用?你知道你需要加入,你看看JOIN子句的文档吗? – Oded 2011-06-16 09:27:30
@Oded:JOIN不足以做到这一点。 @espen:你为什么想在SQL中创建这种输出?这是应用程序层的事情,而不是数据库。 – Tomalak 2011-06-16 09:30:42
我已经尝试了所有可以找到的连接子句,我想我通过内部连接正确地完成连接。但我得到 约翰,史密斯,12345678 约翰,史密斯,98765432 我的目标是让一个客户的所有帐户在一行。 – espen 2011-06-16 09:31:29