简单的sql题

写道

p表：

name like
钱 看书
钱 做爱
钱 游戏
李 编程
李 看书
李 游戏
赵 动漫

这样一张表，要求《同时具备》看书与游戏，二个爱好的人名？

从简单慢慢优化 写道

SELECT `name`,GROUP_CONCAT(`like`) AS bb FROM p

GROUP BY `name`
HAVING bb LIKE '%3,4%' OR bb LIKE '%4,3%'

SELECT `name` FROM
( SELECT *
FROM p
WHERE `like`='3' OR `like`='4') t
GROUP BY `name`
HAVING COUNT(1)=2


SELECT `name`
FROM p
WHERE `like`='3' OR `like`='4'
GROUP BY `name`
HAVING COUNT(1)=2

  

 

 

取所有分类下最新的一条记录 写道

CREATE TABLE `p2` (
`tid` int(11) NOT NULL,
`p1_id` int(11) NOT NULL,
`dt` date NOT NULL,
PRIMARY KEY (`tid`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

CREATE TABLE `p1` (
`id` int(11) NOT NULL,
`name` char(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8

SELECT a.*,b.cp_name FROM (
SELECT * FROM company_allot ORDER BY `created` DESC
) AS a
LEFT JOIN company AS b ON a.cp_id=b.cp_id
GROUP BY cp_id
order by `created` ASC