mysql - 一张表多选

Question

我有一个名为列表的表，我必须提取以下内容。字段是 id 和 date

1. 如果日期<20120401 然后依靠 id >>>> 给出结果
2. 如果日期>20120401 然后依靠 id >>>> 给出结果

如果有 20 项日期<20120401 和 30 项日期>20120401 的 idnumber xyz

那么结果应该是...

xyz 20 30

我做了像...

select 
 (select count(id) from list where id='xyz' and date<20120401) as date1,
 (select count(id) from list where id='xyz' and date>20120401) as date2;

结果是 20 30

但是如何打印身份证号码呢？

Answer 1

SELECT
id,
SUM(CASE WHEN date < 20120401 THEN 1 ELSE 0 END) AS date1,
SUM(CASE WHEN date > 20120401 THEN 1 ELSE 0 END) AS date2,
FROM list
WHERE id = 'xyz'
GROUP BY id

更新:

SELECT
list.id,
idmaster.idlocation,
SUM(CASE WHEN list.date < 20120401 THEN 1 ELSE 0 END) AS date1,
SUM(CASE WHEN list.date > 20120401 THEN 1 ELSE 0 END) AS date2,
FROM list
INNER JOIN idmaster ON list.id = idmaster.idnumber
WHERE list.id = 'xyz'
GROUP BY id