连接结果中唯一ID的重复输入

Question

表提供程序

ID  NAME
1   name1
2   name2
3   name3

表Lang

ID  Lan
1   English
2   English
2   Tamil
3   English
3   Tamil
3   Telugu

连接结果是

1   name1   English
2   name2   English
2   name2   Tamil
3   name3   English
3   name3   Tamil
3   name3   Telugu

但我需要这样的结果

id  Name   Lan1    Lan2  Lan3
1   name1  English NULL  NULL
2   name2  English Tamil NULL
3   name3  English Tamil TELUGU

你能帮帮我吗

Answer 1

试试这个枢轴查询，它应该同时适用于MySQL和Server (您从未告诉我们您真正使用的是哪一个)。MAX()函数应该忽略这两个关系数据库的null值。

SELECT t.ID, t.NAME, MAX(t.Lan1) AS Lan1, MAX(t.Lan2) AS Lan2, MAX(t.Lan3) AS Lan3
FROM
(
    SELECT p.ID, p.NAME,
        CASE WHEN l.Lan = 'English' THEN 'English' ELSE null END AS Lan1,
        CASE WHEN l.Lan = 'Tamil'   THEN 'Tamil'   ELSE null END AS Lan2
        CASE WHEN l.Lan = 'Telugu'  THEN 'Telugu'  ELSE null END AS Lan3
    FROM Provider p INNER JOIN Lang l ON p.ID = l.ID
) t
GROUP BY t.ID, t.NAME

Answer 2

关于mysql

select ID,NAME
,(select LAN from LANG where LANG.id=PROVIDER.ID and LAN='English' limit 1)  
as LAN1 
,(select LAN from LANG where LANG.id=PROVIDER.ID and LAN='Tamil' limit 1)  
as LAN2 
,(select LAN from LANG where LANG.id=PROVIDER.ID and LAN='Telugu' limit 1)  
as LAN3 
from PROVIDER;

在sqlserver上感谢@Bungicasse的回答

select ID,NAME
,(select TOP 1 LAN from LANG where LANG.id=PROVIDER.ID and LAN='English')  
as LAN1 
,(select TOP 1 LAN from LANG where LANG.id=PROVIDER.ID and LAN='Tamil' )  
as LAN2 
,(select TOP 1 LAN from LANG where LANG.id=PROVIDER.ID and LAN='Telugu')  
as LAN3 
from PROVIDER;

以及mysql上的构建模式脚本。

drop table if exists  LANG;
drop table if exists  PROVIDER;
CREATE TABLE LANG (
  ID int(11) NOT NULL,
  LAN varchar(255) NOT NULL
);
INSERT INTO lang (ID, Lan) VALUES (1, 'English');
INSERT INTO lang (ID, Lan) VALUES (2, 'English');
INSERT INTO lang (ID, Lan) VALUES (2, 'Tamil');
INSERT INTO lang (ID, Lan) VALUES (3, 'English');
INSERT INTO lang (ID, Lan) VALUES (3, 'Tamil');
INSERT INTO lang (ID, Lan) VALUES (3, 'Telugu');

CREATE TABLE PROVIDER (
  ID int(11) NOT NULL,
  NAME varchar(255) NOT NULL,
  PRIMARY KEY  (ID)
);

INSERT INTO PROVIDER VALUES (1, 'name1');
INSERT INTO PROVIDER VALUES (2, 'name2');
INSERT INTO PROVIDER VALUES (3, 'name3');

论mssql

CREATE TABLE LANG (
  ID INTEGER NOT NULL,
  LAN varchar(255) NOT NULL
);
INSERT INTO lang (ID, Lan) VALUES (1, 'English');
INSERT INTO lang (ID, Lan) VALUES (2, 'English');
INSERT INTO lang (ID, Lan) VALUES (2, 'Tamil');
INSERT INTO lang (ID, Lan) VALUES (3, 'English');
INSERT INTO lang (ID, Lan) VALUES (3, 'Tamil');
INSERT INTO lang (ID, Lan) VALUES (3, 'Telugu');

CREATE TABLE PROVIDER (
  ID INTEGER NOT NULL,
  NAME varchar(255) NOT NULL,
  PRIMARY KEY  (ID)
);

INSERT INTO PROVIDER VALUES (1, 'name1');
INSERT INTO PROVIDER VALUES (2, 'name2');
INSERT INTO PROVIDER VALUES (3, 'name3');

在这里测试一下

sql小提琴

Answer 3

如果您使用Server，您应该更改@Alice代码，因为限制1不能工作，而应该使用TOP 1。

select ID,NAME 
,(select TOP 1 LAN from LANG where LANG.id=PROVIDER.ID and LAN='English')  
as LAN1 
,(select TOP 1 LAN from LANG where LANG.id=PROVIDER.ID and LAN='Tamil')  
as LAN2 
,(select TOP 1 LAN from LANG where LANG.id=PROVIDER.ID and LAN='Telugu')  
as LAN3 
from PROVIDER;