56,679
社区成员
发帖
与我相关
我的任务
分享
SELECT
ms.id,
ms.title,
msi.file_path,
GROUP_CONCAT(DISTINCT mst.tab_id) AS tabs
FROM
`dbb_menber_shows` `ms`
INNER JOIN `dbb_menber_show_images` `msi` ON `msi`.`im_id` = `ms`.`id`
INNER JOIN `dbb_menber_show_tabs` `mst` ON `ms`.`id` = `mst`.`im_id`
WHERE
`ms`.`deleted` = 0
AND msi.`status` = 1
AND (
mst.tab_id = 27
OR mst.tab_id = 29
OR mst.tab_id = 38
)
GROUP BY
ms.id
ORDER BY
ms.gold_amount DESC
DROP TABLE IF EXISTS `dbb_menber_show_images`;
CREATE TABLE `dbb_menber_show_images` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`im_id` int(11) unsigned NOT NULL COMMENT '主表id',
`status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否封面:0-正常、1-封面',
`file_path` varchar(255) NOT NULL DEFAULT '' COMMENT '图片路径',
PRIMARY KEY (`id`),
KEY `im_id` (`im_id`,`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=42 DEFAULT CHARSET=utf8 COMMENT='图库';
-- ----------------------------
-- Records of dbb_menber_show_images
-- ----------------------------
INSERT INTO `dbb_menber_show_images` VALUES ('21', '1', '1', '/upload/shows/201702/22/1487759438135.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('22', '1', '0', '/upload/shows/201702/22/1487759437631.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('23', '1', '0', '/upload/shows/201702/22/1487759436574.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('36', '2', '0', '/upload/shows/201702/22/1487748820128.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('35', '2', '0', '/upload/shows/201702/22/1487748821536.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('34', '2', '1', '/upload/shows/201702/22/1487748822853.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('32', '3', '0', '/upload/shows/201703/01/1488356441800.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('31', '3', '0', '/upload/shows/201703/01/1488356442215.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('30', '3', '1', '/upload/shows/201703/01/1488356442631.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('33', '3', '0', '/upload/shows/201703/01/1488356440832.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('37', '4', '1', '/upload/shows/201703/01/1488356442215.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('38', '4', '0', '/upload/shows/201703/01/1488356441800.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('39', '4', '0', '/upload/shows/201703/01/1488356440832.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('40', '5', '1', '/upload/shows/201703/02/1488446765439.jpg');
INSERT INTO `dbb_menber_show_images` VALUES ('41', '5', '0', '/upload/shows/201703/02/1488446764140.jpg');
-- ----------------------------
-- Table structure for dbb_menber_show_tabs
-- ----------------------------
DROP TABLE IF EXISTS `dbb_menber_show_tabs`;
CREATE TABLE `dbb_menber_show_tabs` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`im_id` int(11) unsigned NOT NULL COMMENT '主表id',
`tab_id` int(3) unsigned NOT NULL COMMENT '标签id',
PRIMARY KEY (`id`),
KEY `im_id` (`im_id`,`id`,`tab_id`) USING BTREE,
KEY `tab_id` (`im_id`,`id`,`tab_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=45 DEFAULT CHARSET=utf8 COMMENT='标签中间表';
-- ----------------------------
-- Records of dbb_menber_show_tabs
-- ----------------------------
INSERT INTO `dbb_menber_show_tabs` VALUES ('5', '1', '25');
INSERT INTO `dbb_menber_show_tabs` VALUES ('6', '1', '31');
INSERT INTO `dbb_menber_show_tabs` VALUES ('7', '1', '20');
INSERT INTO `dbb_menber_show_tabs` VALUES ('8', '1', '29');
INSERT INTO `dbb_menber_show_tabs` VALUES ('9', '1', '39');
INSERT INTO `dbb_menber_show_tabs` VALUES ('30', '2', '24');
INSERT INTO `dbb_menber_show_tabs` VALUES ('31', '2', '32');
INSERT INTO `dbb_menber_show_tabs` VALUES ('32', '2', '20');
INSERT INTO `dbb_menber_show_tabs` VALUES ('33', '2', '29');
INSERT INTO `dbb_menber_show_tabs` VALUES ('34', '2', '39');
INSERT INTO `dbb_menber_show_tabs` VALUES ('25', '3', '23');
INSERT INTO `dbb_menber_show_tabs` VALUES ('26', '3', '30');
INSERT INTO `dbb_menber_show_tabs` VALUES ('27', '3', '20');
INSERT INTO `dbb_menber_show_tabs` VALUES ('28', '3', '28');
INSERT INTO `dbb_menber_show_tabs` VALUES ('29', '3', '39');
INSERT INTO `dbb_menber_show_tabs` VALUES ('35', '4', '24');
INSERT INTO `dbb_menber_show_tabs` VALUES ('36', '4', '31');
INSERT INTO `dbb_menber_show_tabs` VALUES ('37', '4', '20');
INSERT INTO `dbb_menber_show_tabs` VALUES ('38', '4', '29');
INSERT INTO `dbb_menber_show_tabs` VALUES ('39', '4', '39');
INSERT INTO `dbb_menber_show_tabs` VALUES ('40', '5', '27');
INSERT INTO `dbb_menber_show_tabs` VALUES ('41', '5', '33');
INSERT INTO `dbb_menber_show_tabs` VALUES ('42', '5', '22');
INSERT INTO `dbb_menber_show_tabs` VALUES ('43', '5', '29');
INSERT INTO `dbb_menber_show_tabs` VALUES ('44', '5', '38');
-- ----------------------------
-- Table structure for dbb_menber_shows
-- ----------------------------
DROP TABLE IF EXISTS `dbb_menber_shows`;
CREATE TABLE `dbb_menber_shows` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT COMMENT 'ID',
`cate_id` mediumint(8) unsigned DEFAULT '0' COMMENT '分类ID',
`menber_id` int(11) unsigned DEFAULT '0' COMMENT '所属会员ID',
`status` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '状态:0-正常、1-精选',
`deleted` tinyint(1) unsigned NOT NULL DEFAULT '0' COMMENT '是否删除:0-未删除、1-已删除',
`title` varchar(128) DEFAULT NULL COMMENT '标题',
`like_amount` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '点赞总数',
`gold_amount` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '打赏总数',
`read_amount` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '阅读总数',
`heart_amount` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '收藏总数',
`create_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '添加时间',
`update_time` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '更新时间',
`deltime` int(11) unsigned NOT NULL DEFAULT '0' COMMENT '删除时间',
PRIMARY KEY (`id`),
KEY `menber_id` (`menber_id`,`id`,`cate_id`) USING BTREE,
KEY `cate_id` (`menber_id`,`id`,`cate_id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=6 DEFAULT CHARSET=utf8 COMMENT='主表';
-- ----------------------------
-- Records of dbb_menber_shows
-- ----------------------------
INSERT INTO `dbb_menber_shows` VALUES ('1', '0', '1', '1', '0', '标题1', '0', '0', '0', '0', '1488274180', '0', '0');
INSERT INTO `dbb_menber_shows` VALUES ('2', '0', '1', '1', '0', '标题3', '0', '0', '0', '0', '1488343322', '1488356743', '0');
INSERT INTO `dbb_menber_shows` VALUES ('3', '0', '1', '1', '0', '标题4', '0', '0', '0', '0', '1488355850', '1488356470', '0');
INSERT INTO `dbb_menber_shows` VALUES ('4', '0', '1', '1', '0', '标题122', '0', '0', '0', '0', '1488419668', '0', '0');
INSERT INTO `dbb_menber_shows` VALUES ('5', '0', '1', '1', '0', '标题123', '0', '0', '0', '0', '1488446812', '0', '0');
SELECT
ms.id,
ms.title,
msi.file_path,
GROUP_CONCAT(DISTINCT mst.tab_id) AS tabs,
GROUP_CONCAT(DISTINCT sc.`name`) AS `name`
FROM
`dbb_menber_shows` `ms`
INNER JOIN `dbb_menber_show_images` `msi` ON `msi`.`im_id` = `ms`.`id`
INNER JOIN `dbb_menber_show_tabs` `mst` ON `ms`.`id` = `mst`.`im_id`
INNER JOIN `dbb_sys_categorys` `sc` ON `sc`.`id` = `mst`.`tab_id`
WHERE
`ms`.`deleted` = 0
AND msi.`status` = 1
AND (sc.pid = 18 AND mst.tab_id = 20) OR (sc.pid = 19 AND mst.tab_id = 29) OR (sc.pid = 37 AND mst.tab_id = 39)
GROUP BY
ms.id
HAVING
count(*) > 6
$order = 'ms.gold_amount DESC';
//接收到的
$str = '20,29,39';
$d = substr_count($str,',');
$strlen = strlen($str) - $d;
$having = "count(*) > $strlen";
//获取pid
$where['id']= ['IN',"$str"];
$cateList = model('syscategory')->field('id,pid')->where($where)->select();
$sql = '';
$or = '';
foreach ($cateList as $key => $val) {
if ($key == 0) {
$or = " ";
} else {
$or = " OR ";
}
$sql .= " $or (sc.pid = ".$val['pid']." AND mst.tab_id = ".$val['id'].") ";
}
$map = " ms.deleted = 0 AND msi.status = 1 AND ".$a;
$field = '
ms.id,
ms.title,
msi.file_path,
ms.like_amount,
ms.gold_amount,
ms.read_amount,
ms.heart_amount
';
$showList = model('menbershow')
-> alias('ms')
-> field($field)
-> join('__MENBER_SHOW_IMAGES__ msi','msi.im_id = ms.id')
-> join('__MENBER_SHOW_TABS__ mst','ms.id = mst.im_id')
-> join('__SYS_CATEGORYS__ sc','sc.id = mst.tab_id')
-> where($map)
-> group('ms.id')
-> having($having)
-> order($order)
// -> page($curr_page, $shownum)
-> fetchSql(true)
-> select();
SELECT
`ms`.`id`,
`ms`.`title`,
`msi`.`file_path`,
`ms`.`like_amount`,
`ms`.`gold_amount`,
`ms`.`read_amount`,
`ms`.`heart_amount`
FROM
`dbb_menber_shows` `ms`
INNER JOIN `dbb_menber_show_images` `msi` ON `msi`.`im_id` = `ms`.`id`
INNER JOIN `dbb_menber_show_tabs` `mst` ON `ms`.`id` = `mst`.`im_id`
INNER JOIN `dbb_sys_categorys` `sc` ON `sc`.`id` = `mst`.`tab_id`
WHERE
(
ms.deleted = 0
AND msi. STATUS = 1
AND (sc.pid = 18 AND mst.tab_id = 20)
OR (sc.pid = 19 AND mst.tab_id = 29)
OR (sc.pid = 37 AND mst.tab_id = 39)
)
GROUP BY
ms.id
HAVING
count(*) > 6
ORDER BY
ms.gold_amount DESC