表
--
-- 表的结构 `a`
--CREATE TABLE `a` (
`id` int(10) NOT NULL auto_increment,
`k1` varchar(20) NOT NULL,
`k2` varchar(20) NOT NULL,
`k3` varchar(20) NOT NULL,
`k4` varchar(20) NOT NULL,
`k5` varchar(20) NOT NULL,
`k6` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;--
-- 导出表中的数据 `a`
--INSERT INTO `a` (`id`, `k1`, `k2`, `k3`, `k4`, `k5`, `k6`) VALUES
(1, '我1', '我2', '我3', '我4', '我5', '我6'),
(2, '我1', '我3', '我3', '我4', '我5', '我6'),
(3, '我1', '我4', '我3', '我4', '我5', '我6'),
(4, '我1', '我4', '我4', '我4', '我5', '我6'),
(5, '我1', '我3', '我3', '我4', '我5', '我6'),
(6, '我1', '我6', '我6', '我4', '我5', '我6'),
(7, '我1', '我6', '我5', '我4', '我5', '我6'),
(8, '我1', '我4', '我5', '我4', '我5', '我6'),
(9, '我1', '我4', '我5', '我4', '我3', '我6'),
(10, '我1', '我4', '我5', '我4', '我2', '我6'),
(11, '我1', '我4', '我5', '我4', '我1', '我6'),
(12, '我1', '我4', '我5', '我1', '我1', '我6'),
(13, '我1', '我4', '我1', '我1', '我1', '我6'),
(14, '我1', '我1', '我1', '我1', '我1', '我6'),
(15, '我1', '我1', '我1', '我1', '我1', '我1'),
(16, '我1', '我2', '我1', '我1', '我1', '我1'),
(17, '我1', '我2', '我2', '我1', '我1', '我1'),
(18, '我1', '我2', '我2', '我2', '我1', '我1'),
(19, '我1', '我2', '我2', '我2', '我2', '我1'),
(20, '我1', '我2', '我2', '我2', '我2', '我2');
假设数据有100万!这个表怎么样建索引!如果建好了!希望能发代码上来!谢谢我需要查询效果语句是SELECT *
FROM a
WHERE k1 <> ''
AND `k1` = '我1'
OR `k2` = '我1'
OR `k3` = '我1'
OR `k4` = '我1'
OR `k5` = '我1'
OR `k6` = '我1'
ORDER BY (
(CASE WHEN `k1` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k2` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k3` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k4` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k5` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k6` = '我1'
THEN 8
ELSE 0
END
)
) DESC
LIMIT 0 , 10
--
-- 表的结构 `a`
--CREATE TABLE `a` (
`id` int(10) NOT NULL auto_increment,
`k1` varchar(20) NOT NULL,
`k2` varchar(20) NOT NULL,
`k3` varchar(20) NOT NULL,
`k4` varchar(20) NOT NULL,
`k5` varchar(20) NOT NULL,
`k6` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=21 ;--
-- 导出表中的数据 `a`
--INSERT INTO `a` (`id`, `k1`, `k2`, `k3`, `k4`, `k5`, `k6`) VALUES
(1, '我1', '我2', '我3', '我4', '我5', '我6'),
(2, '我1', '我3', '我3', '我4', '我5', '我6'),
(3, '我1', '我4', '我3', '我4', '我5', '我6'),
(4, '我1', '我4', '我4', '我4', '我5', '我6'),
(5, '我1', '我3', '我3', '我4', '我5', '我6'),
(6, '我1', '我6', '我6', '我4', '我5', '我6'),
(7, '我1', '我6', '我5', '我4', '我5', '我6'),
(8, '我1', '我4', '我5', '我4', '我5', '我6'),
(9, '我1', '我4', '我5', '我4', '我3', '我6'),
(10, '我1', '我4', '我5', '我4', '我2', '我6'),
(11, '我1', '我4', '我5', '我4', '我1', '我6'),
(12, '我1', '我4', '我5', '我1', '我1', '我6'),
(13, '我1', '我4', '我1', '我1', '我1', '我6'),
(14, '我1', '我1', '我1', '我1', '我1', '我6'),
(15, '我1', '我1', '我1', '我1', '我1', '我1'),
(16, '我1', '我2', '我1', '我1', '我1', '我1'),
(17, '我1', '我2', '我2', '我1', '我1', '我1'),
(18, '我1', '我2', '我2', '我2', '我1', '我1'),
(19, '我1', '我2', '我2', '我2', '我2', '我1'),
(20, '我1', '我2', '我2', '我2', '我2', '我2');
假设数据有100万!这个表怎么样建索引!如果建好了!希望能发代码上来!谢谢我需要查询效果语句是SELECT *
FROM a
WHERE k1 <> ''
AND `k1` = '我1'
OR `k2` = '我1'
OR `k3` = '我1'
OR `k4` = '我1'
OR `k5` = '我1'
OR `k6` = '我1'
ORDER BY (
(CASE WHEN `k1` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k2` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k3` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k4` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k5` = '我1'
THEN 1
ELSE 0
END
) + (CASE WHEN `k6` = '我1'
THEN 8
ELSE 0
END
)
) DESC
LIMIT 0 , 10
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货