MySQL语句问题 [英] MySQL problem with statement
本文介绍了MySQL语句问题的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
SELECT a.id,i.* FROM ads a
INNER JOIN images i ON i.ad_id=a.id
GROUP BY a.id
LIMIT 10
不知道如何在图像表中选择带有"main"标志的图像.
Can't figure out, how to pick images with flag "main" inside images table.
对于一个aid,最多可以在图像表中包含3张照片,这3张照片之一可以具有main = 1字段.我需要在将main标记设置为1的情况下按优先顺序选择照片.
For one a.id can be up to 3 photos inside images table, one of those 3 photos can have a field main=1. I need to pick photos by priority where flag main is set to 1.
CREATE DATABASE IF NOT EXISTS `test123`;
USE `test123`;
CREATE TABLE IF NOT EXISTS `ads` (
`id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=229 DEFAULT CHARSET=utf8;
INSERT INTO `ads` (`id`) VALUES (217), (225), (226), (228);
CREATE TABLE IF NOT EXISTS `images` (
`image_id` int(10) NOT NULL AUTO_INCREMENT,
`ad_id` int(10) DEFAULT '0',
`main` int(10) DEFAULT '0',
`t_0` varchar(255) DEFAULT '0',
`t_1` varchar(255) DEFAULT '0',
`t_8` varchar(255) DEFAULT '0',
PRIMARY KEY (`image_id`),
KEY `ad_id` (`ad_id`),
KEY `t_0` (`t_0`),
KEY `t_1` (`t_1`),
KEY `t_8` (`t_8`),
KEY `main` (`main`)
) ENGINE=MyISAM AUTO_INCREMENT=425 DEFAULT CHARSET=utf8;
INSERT INTO `images` (`image_id`, `ad_id`, `main`, `t_0`, `t_1`, `t_8`) VALUES
(1, 226, 0, 'img_link1', 'img_link2', 'img_link3'),
(2, 228, 0, 'img_link1', 'img_link2', 'img_link3'),
(3, 225, 0, 'img_link1', 'img_link2', 'img_link3'),
(4, 217, 0, 'img_link1', 'img_link2', 'img_link3'),
(5, 217, 1, 'img_link1', 'img_link2', 'img_link3'),
(6, 217, 0, 'img_link1', 'img_link2', 'img_link3');
类似的东西,但是每个ads.id只有1行
Something like that but only 1 row per ads.id
SELECT a.id, i.main AS main
FROM images i
LEFT JOIN ads a ON a.id=i.ad_id
WHERE i.main=1 OR i.main=0
需要的输出:
id | main | links
217 | 1 | ...
225 | 0 | ...
226 | 0 | ...
228 | 0 | ...
推荐答案
这应该可以解决问题
select ad_id, main, concat(t_0, ', ', t_1, ', ', t_8) as links
from images
where main = 0
group by ad_id having ad_id not in (select ad_id from images where main = 1)
union all
select ad_id, main, concat(t_0, ', ', t_1, ', ', t_8) as links from images where main = 1
这篇关于MySQL语句问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文