加入和GROUP_CONCAT数据顺序问题 [英] Trouble with joining and the order of GROUP_CONCAT data
问题描述
我有几个表,我想从中合并数据.我从 User
表中选择基本信息,并希望从 User_Status
、User_Sex_Info
、User_Personal_Info
和表中加入数据媒体
.
我在处理 Media
表中的数据时遇到问题,因为该表可以容纳同一用户的多条记录,但我只想显示最多 6 条记录(按顺序排列), 在任何情况下,一张 Type
1 图像,然后最多五张 Type
2 图像).其他表每个用户只有一行.我目前有下表来选择数据:
除媒体文件外,此方法适用于所有数据.输出是 18-profile.jpg
而我期待 18-profile.jpg-18-2.jpg-18-2.jpg-18-4.jpg-18-5.jpg-18-6.jpg
Media
表如下所示:
如果不存在`Media`,则创建表(`m_Id` int(11) NOT NULL AUTO_INCREMENT,`m_Type` enum('1','2','3') NOT NULL DEFAULT '3' COMMENT '1 = 个人资料图片\n2 = 用户图片\n3 = 内容图片',`m_File` varchar(255) 非空,`m_Date` 日期时间非空,`User_u_UserId` int(11) 非空,PRIMARY KEY (`m_Id`,`User_u_UserId`),唯一键`m_id_UNIQUE`(`m_Id`),唯一键`m_name_UNIQUE`(`m_File`),密钥`fk_Media_User1_idx`(`User_u_UserId`)) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;插入`媒体`(`m_Id`、`m_Type`、`m_File`、`m_Date`、`User_u_UserId`)值(2, '1', '18-profile.jpg', '2013-07-30 00:00:00', 18),(3, '2', '18-1.jpg', '2013-07-30 00:00:00', 18),(4, '2', '18-2.jpg', '2013-07-30 00:00:00', 18),(5, '2', '18-3.jpg', '2013-07-30 00:00:00', 18),(6, '2', '18-4.jpg', '2013-07-30 00:00:00', 18),(7, '2', '18-5.jpg', '2013-07-30 00:00:00', 18),(8, '2', '18-6.jpg', '2013-07-30 00:00:00', 18);
我是否忽略了什么?我不明白为什么它不起作用.
编辑我创建了一个带有查询的 SQL Fiddle 并在七行中显示了我的数据.我不明白为什么当我在本地运行它时它不再显示任何行
编辑 2显然问题出在 GROUP_CONCAT
中,因为 phpmyadmin 显示的结果与 SQL Fiddle 相同.我去看看
解决了!我将数据分组到错误的参数上,我需要按 User.u_UserId
分组.感谢观看!
更改
GROUP BY UserMedia.m_Id
到
GROUP BY User.u_userId
m_Id
对于每个 Media 行都是唯一的,因此您没有将任何内容组合在一起.
I'm having a few tables from which i want to merge the data. I select basic information from the User
table and want to join data from the tables User_Status
, User_Sex_Info
, User_Personal_Info
and Media
.
I'm encountering troubles with the data from the Media
table, since this table can hold up many records of the same user, but i only want to display a maximum of six records (in order, In any case, one Type
1 image, followed by a maximum of five Type
2 images). The other tables only have one row of each user.
I currently have the following table to select the data:
SELECT
User.u_UserName, User.u_UserMail, User.u_UserRegistration,
Status.us_PaymentStatus,
Sex.us_Gender, Sex.us_Interest,
Personal.up_Name, Personal.up_Dob, Personal.up_City, Personal.up_Province,
UserMedia.m_Id, UserMedia.m_Type, SUBSTRING_INDEX(
GROUP_CONCAT(
CONCAT(
UserMedia.m_Type, ':', UserMedia.m_File
)
ORDER BY UserMedia.m_Type = 1, UserMedia.m_Date DESC SEPARATOR '-'
),'-',6
) AS userFile
FROM User AS User
JOIN User_Status AS Status ON Status.User_u_UserId = User.u_UserId
JOIN User_Sex_Info AS Sex ON Sex.User_u_UserId = User.u_UserId
LEFT JOIN User_Personal_Info AS Personal ON Personal.User_u_UserId = User.u_UserId
LEFT JOIN Media AS UserMedia ON UserMedia.User_u_UserId = User.u_UserId
WHERE User.u_UserId = :uId
GROUP BY UserMedia.m_Id
This one works fine on all the data, except for the media files. The output is 18-profile.jpg
while i was expecting 18-profile.jpg-18-2.jpg-18-2.jpg-18-4.jpg-18-5.jpg-18-6.jpg
The Media
table looks like this:
CREATE TABLE IF NOT EXISTS `Media` (
`m_Id` int(11) NOT NULL AUTO_INCREMENT,
`m_Type` enum('1','2','3') NOT NULL DEFAULT '3' COMMENT '1 = Profile picture\n2 = User picture\n3 = Content image',
`m_File` varchar(255) NOT NULL,
`m_Date` datetime NOT NULL,
`User_u_UserId` int(11) NOT NULL,
PRIMARY KEY (`m_Id`,`User_u_UserId`),
UNIQUE KEY `m_id_UNIQUE` (`m_Id`),
UNIQUE KEY `m_name_UNIQUE` (`m_File`),
KEY `fk_Media_User1_idx` (`User_u_UserId`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
INSERT INTO `Media` (`m_Id`, `m_Type`, `m_File`, `m_Date`, `User_u_UserId`) VALUES
(2, '1', '18-profile.jpg', '2013-07-30 00:00:00', 18),
(3, '2', '18-1.jpg', '2013-07-30 00:00:00', 18),
(4, '2', '18-2.jpg', '2013-07-30 00:00:00', 18),
(5, '2', '18-3.jpg', '2013-07-30 00:00:00', 18),
(6, '2', '18-4.jpg', '2013-07-30 00:00:00', 18),
(7, '2', '18-5.jpg', '2013-07-30 00:00:00', 18),
(8, '2', '18-6.jpg', '2013-07-30 00:00:00', 18);
Do i overlook something? I don't see why it isn't working.
EDIT I created a SQL Fiddle with the query and that shows my data in seven rows. I don't understand why it's not displaying any more rows when i run it locally
EDIT 2
Apparently the problem is in the GROUP_CONCAT
since phpmyadmin shows the same result as the SQL Fiddle. I'm going to look at that
SOLVED IT!
I grouped the data on the wrong parameter, i needed to group by User.u_UserId
. Thanks for looking!
Change
GROUP BY UserMedia.m_Id
to
GROUP BY User.u_userId
m_Id
is unique for each Media row, so you weren't grouping anything together.
这篇关于加入和GROUP_CONCAT数据顺序问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!