查询以返回一行，该行中的单独列中包含多个项目

admin

查询以返回一行，该行中的单独列中包含多个项目

sql

我有返回多行结果的情况。我正在寻找一种返回单行的方法，该行中的多个项目位于单独的列中。我最初的查询：

SELECT a.name, a.city, a.address, a.abbrv, b.urltype, b.url 
FROM jos__universityTBL as a 
LEFT JOIN jos__university_urlTBL as b on b.universityID = a.ID 
WHERE a.stateVAL = 'CA'

我的输出：

| University Of Southern Califor         | Los Angeles         |         | usc   |       2 | http://web-app.usc.edu/ws/soc/api/                                                                                                       | 
| University Of Southern Califor         | Los Angeles         |         | usc   |       4 | http://web-app.usc.edu/ws/soc/api/                                                                                                      | 
| University Of Southern Califor         | Los Angeles         |         | usc   |       1 | www.usc.edu                                                                                                                             | 
| San Jose State University              | San Jose            |         | sjsu  |       2 | http://info.sjsu.edu/home/schedules.html                                                                                                | 
| San Jose State University              | San Jose            |         | sjsu  |       4 | https://cmshr.sjsu.edu/psp/HSJPRDF/EMPLOYEE/HSJPRD/c/COMMUNITY_ACCESS.CLASS_SEARCH.GBL?FolderPath=PORTAL_ROOT_OBJECT.PA_HC_CLASS_SEARCH | 
| San Jose State University              | San Jose            |         | sjsu  |       1 | www.sjsu.edu

我的表格架构…

mysql> describe jos_universityTBL;
+----------------+--------------+------+-----+---------+----------------+
| Field          | Type         | Null | Key | Default | Extra          |
+----------------+--------------+------+-----+---------+----------------+
| name           | varchar(50)  | NO   | UNI |         |                | 
| repos_dir_name | varchar(50)  | NO   |     |         |                | 
| city           | varchar(20)  | YES  |     |         |                | 
| stateVAL       | varchar(5)   | NO   |     |         |                | 
| address        | varchar(50)  | NO   |     |         |                | 
| abbrv          | varchar(20)  | NO   |     |         |                | 
| childtbl       | varchar(200) | NO   |     |         |                | 
| userID         | int(10)      | NO   |     | 0       |                | 
| ID             | int(10)      | NO   | PRI | NULL    | auto_increment | 
+----------------+--------------+------+-----+---------+----------------+
9 rows in set (0.00 sec)

mysql> describe jos_university_urlTBL;
+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| universityID | int(10)      | NO   |     | 0       |                | 
| urltype      | int(5)       | NO   |     | 0       |                | 
| url          | varchar(200) | NO   | MUL |         |                | 
| actionID     | int(5)       | YES  |     | 0       |                | 
| status       | int(5)       | YES  |     | 0       |                | 
| ID           | int(10)      | NO   | PRI | NULL    | auto_increment | 
+--------------+--------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

我真的在努力获得类似的东西：

                                 |<<the concated urltype-url >>|
ucs  |  losangeles   |  usc.edu  |  1-u1 |  2-u2 |  3-u2   |

阅读 152

2021-07-01

共1个答案

admin

您可以使用group_concat：

SELECT a.name, a.city, a.address, a.abbrv, b.urltype, 
    group_concat(b.url SEPARATOR ' ')
FROM jos__universityTBL as a 
LEFT JOIN jos__university_urlTBL as b on b.universityID = a.ID 
WHERE a.stateVAL = 'CA'
GROUP BY a.name, a.city, a.address, a.abbrv, b.urltype

在SQL中很难生成动态列。尽可能查看是否可以将其移至客户端。如果不是，则可以在子查询中添加行号，并为每个行号分配自己的列。这是一个表稍有不同的示例：

drop table if exists Universities;
drop table if exists Urls;
create table Universities (
  id int auto_increment primary key
, Name varchar(50)
);
create table Urls (
  id int auto_increment primary key
, UniversityId int
, Url varchar(50)
);
insert into Universities (name) values ('USC'), ('SJSU');
insert into Urls (UniversityId, Url) values 
    (1,'http://a/'), (1,'http://b/'),
    (2,'http://c/'), (2,'http://d/'), (2,'http://e/');

SELECT
    Name
,   group_concat(case RowNr when 1 then Url end) as FirstCol
,   group_concat(case RowNr when 2 then Url end) as SecondCol
,   group_concat(case RowNr when 3 then Url end) as ThirdCol
FROM (
    SELECT 
        u.Name
    ,   l.Url
    ,   (@i := case when @LastUni = u.Name then @i + 1 else 1 end) as RowNr
    ,   @LastUni := u.name
    FROM Universities u
    JOIN Urls l ON u.id = l.UniversityId
    JOIN (SELECT @i := 0, @LastUni := '') init
) subquery
GROUP BY Name;

打印：

SJSU     http://c/  http://d/   http://e/
USC      http://a/  http://b/   NULL

2021-07-01