查询以返回一行,该行中的单独列中包含多个项目 [英] query to return a single row with the multiple items in separate columns within the row
本文介绍了查询以返回一行,该行中的单独列中包含多个项目的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我遇到的情况是我返回多行结果.我正在寻找一种返回单行的方法,该行中的多个项目位于单独的列中.我的初始查询:
I have a situation where I return results with multiple rows. I'm looking for a way to return a single row, with the multiple items in separate columns within the row. My initial query:
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)
我真的想得到类似的东西:
I'm really trying to get something like:
|<<the concated urltype-url >>|
ucs | losangeles | usc.edu | 1-u1 | 2-u2 | 3-u2 |
推荐答案
您可以使用在SQL中很难生成动态列.尽可能查看是否可以将其移至客户端.如果没有,则可以在子查询中添加行号,并为每个行号分配自己的列.这是一个表稍有不同的示例:
Generating dynamic columns is hard in SQL; if at all possible, see if it can be moved to the client side. If not, you can add a row number in a subquery, and give each row number its own colum. Here's an example with slightly different tables:
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
这篇关于查询以返回一行,该行中的单独列中包含多个项目的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文