我有两张桌子
Technology
+--------+--------+
| tid(P) | name |
+--------+--------+
| 1 | Java |
|--------|--------|
| 2 | PHP |
+--------+--------+
Employee
+----------+----------+------------+
| eid(P) | tid(F) ^| join_date |
+----------+----------+------------+
| 1 | 1 | 2013-10-01 |
|----------|----------|------------|
| 2 | 1 | 2013-10-10 |
|----------|----------|------------|
| 3 | 1 | 2013-10-12 |
|----------|----------|------------|
| 4 | 1 | 2013-09-10 |
|----------|----------|------------|
| 5 | 1 | 2013-11-10 |
|----------|----------|------------|
| 6 | 1 | 2013-12-10 |
|----------|----------|------------|
| 7 | 2 | 2013-08-01 |
|----------|----------|------------|
| 8 | 2 | 2013-10-28 |
|----------|----------|------------|
| 9 | 2 | 2013-05-12 |
|----------|----------|------------|
| 10 | 2 | 2013-10-10 |
|----------|----------|------------|
| 11 | 2 | 2013-11-10 |
|----------|----------|------------|
| 12 | 2 | 2013-12-05 |
|----------|----------|------------|
对于每种技术，我需要获取最近加入三名员工的数据。我尝试了不同的联接，也对此进行了谷歌搜索，但未获得任何成功。
Expected Result
+-------+--------+-------+------------+
| tid | name | eid | join_date |
+-------+--------+-------+------------+
| 1 | Java | 6 | 2013-12-10 |
+-------+--------+-------+------------+
| 1 | Java | 5 | 2013-11-10 |
+-------+--------+-------+------------+
| 1 | Java | 3 | 2013-10-12 |
+-------+--------+-------+------------+
| 2 | PHP | 12 | 2013-12-05 |
+-------+--------+-------+------------+
| 2 | PHP | 11 | 2013-11-10 |
+-------+--------+-------+------------+
| 2 | PHP | 8 | 2013-10-28 |
+-------+--------+-------+------------+
我应该查询什么？
请指导。
谢谢，
Ankur
尝试这个：
SELECT A.tid, A.name, A.eid, A.join_date
FROM (SELECT IF(@tid = @tid:=t.tid, @cnt:=@cnt+1, @cnt:=0) rowNo, t.tid, t.name, e.eid, e.join_date
FROM Technology t
INNER JOIN Employee e ON t.tid = e.tid, (SELECT @tid:=0, @cnt:=0) A
ORDER BY t.tid, e.join_date DESC
) AS A
WHERE A.rowNo < 3;
编辑：：
SELECT * FROM(
SELECT *, IF(@tid=@tid:=result.tid,@count:=@count+1,@count:=0) as pos
FROM
(SELECT t.tid, t.name, e.eid, e.join_date
FROM Technology t
JOIN Employee e
WHERE e.tid = t.tid
ORDER BY t.tid, e.join_date DESC) result
JOIN (SELECT @tid:=0, @count:=0) c) finalre
where finalre.pos < 3;
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句