34,590
社区成员
发帖
与我相关
我的任务
分享
select *
from apo_product A
left join apo_album B on A.album=B.ID
left join apo_album_item C on B.ID=C.album
where cover=1
or (not exists (select 1 from apo_album_item where album=C.album and ifnull(COVER,0)=1)
and not exists (select 1 from apo_album_item where album=C.album and ID>C.ID))
-- 商品
CREATE TABLE `apo_product` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`SERIALCODE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`price` decimal(12,2) DEFAULT NULL,
`RANKING` int(11) DEFAULT NULL,
`SALE` tinyint(1) DEFAULT '0',
`STATUS` tinyint(1) DEFAULT '0',
`STORAGES` int(11) DEFAULT NULL,
`album` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `UNQ_apo_product_0` (`SERIALCODE`),
KEY `FK_apo_product_album` (`album`),
CONSTRAINT `FK_apo_product_album` FOREIGN KEY (`album`) REFERENCES `apo_album` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- 像册
CREATE TABLE `apo_album` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`ENTRYDATE` datetime DEFAULT NULL,
`MODIFYDATE` datetime DEFAULT NULL,
`NAME` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`STATUS` tinyint(1) DEFAULT '0',
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- 像册图片
CREATE TABLE `apo_album_item` (
`ID` bigint(20) NOT NULL AUTO_INCREMENT,
`COVER` tinyint(1) DEFAULT '0',
`ENTRYDATE` datetime DEFAULT NULL,
`LINK` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`MODIFYDATE` datetime DEFAULT NULL,
`STATUS` tinyint(1) DEFAULT '0',
`TITLE` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
`album` int(11) DEFAULT NULL,
PRIMARY KEY (`ID`),
UNIQUE KEY `UNQ_apo_album_item_0` (`LINK`),
KEY `FK_apo_album_item_album` (`album`),
CONSTRAINT `FK_apo_album_item_album` FOREIGN KEY (`album`) REFERENCES `apo_album` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
select p.*, ai.link
from apo_product as p
join apo_album as a on p.album=a.id
left outer join apo_album_item as ai on ai.album=a.id
where
ai.cover=1
or (ai.cover=0 and ai.id = (
select max(_ai.id)
from apo_album_item as _ai
where _ai.album=ai.album))
group by p.id
SELECT
t.*,
album_image.link
FROM
(
SELECT
product.id,
product.name,
MAX(album_image.id) AS imgid
FROM
product
JOIN
album_image
ON album_image.album = product.album
GROUP BY
product.id,
product.name
) t
JOIN
album_image
ON t.imgid = album_image.id;
drop table if exists `product_test`;
drop table if exists `album_image_test`;
create table product_test(
pid int primary key,
pname nvarchar(20),
album int
);
create table album_image_test(
imageId int primary key,
album int,
link varchar(20),
cover bit
);
insert into album_image_test values(1, 1, '/image/1.jpg',0);
insert into album_image_test values(2, 1, '/image/2.jpg',0);
insert into album_image_test values(3, 2, '/image/3.jpg',1);
insert into product_test values(1,'饼干',1);
insert into product_test values(2,'月饼',2);
select p.pname
, max(i.cover) as cover
, case when max(i.cover)=1 then i.link
else (select ii.link from album_image_test as ii where ii.imageId=max(i.imageId) )
end as link
from product_test as p
left join album_image_test as i on p.album=i.album
group by p.pid,p.pname
;
SELECT
t.*,
album_image.link
FROM
(
SELECT
product.id,
product.name,
MAX(album_image.id) AS imgid
FROM
product
JOIN
album_image
ON album_image.album = product.album
GROUP BY
product.id,
product.name
) t
JOIN
album_image
ON t.imgid = album_image.id;