php - MySQL - 按同一表中的多列分组

Question

我有一个类似下面的表格

sno | booking_id | room_type | gender | age | amount | days
1   | 2016JUL001 | AC        | Male   | 25  | 1000   | 15 
2   | 2016JUL001 | AC        | Male   | 42  | 1000   | 15 
3   | 2016JUL001 | AC        | Male   | 28  | 1000   | 15 
4   | 2016JUL010 | N AC      | Female | 45  | 1000   | 15 
5   | 2016JUL010 | N AC      | Female | 46  | 1000   | 15 
6   | 2016JUL005 | N AC      | Male   | 28  | 1000   | 15 
7   | 2016JUL005 | N AC      | Female | 35  | 1000   | 15 
8   | 2016JUL009 | AC        | Female | 26  | 1000   | 15 
9   | 2016JUL009 | AC        | Female | 25  | 1000   | 15

...等等

预期输出[如果我想得到 gender='Female']

sno | booking_id | room_type | gender | age | amount | days
4   | 2016JUL010 | N AC      | Female | 45  | 1000   | 15 
5   | 2016JUL010 | N AC      | Female | 46  | 1000   | 15
8   | 2016JUL009 | AC        | Female | 26  | 1000   | 15 
9   | 2016JUL009 | AC        | Female | 25  | 1000   | 15

预期输出[如果我想得到 gender='Male']

sno | booking_id | room_type | gender | age | amount | days
1   | 2016JUL001 | AC        | Male   | 25  | 1000   | 15 
2   | 2016JUL001 | AC        | Male   | 42  | 1000   | 15 
3   | 2016JUL001 | AC        | Male   | 28  | 1000   | 15

预期输出[如果我想得到 gender='Male' AND gender='Female']

sno | booking_id | room_type | gender | age | amount | days
6   | 2016JUL005 | N AC      | Male   | 28  | 1000   | 15 
7   | 2016JUL005 | N AC      | Female | 35  | 1000   | 15 

注意:我需要 3 个单独的 QUERIES 来获得上述输出

提前致谢

Answer 1

第一个查询:

SELECT sno, booking_id, room_type, gender, age 
FROM customer_data 
WHERE booking_id IN ( SELECT booking_id FROM customer_data 
                      WHERE gender='female' AND age>0 and RIGHT(booking_id,1) <> '1' 
                      GROUP BY booking_id HAVING COUNT(*) > 1 ) 
ORDER BY booking_id ASC, age ASC

第二个:

SELECT sno, booking_id, room_type, gender, age 
FROM customer_data 
WHERE booking_id IN ( SELECT booking_id FROM customer_data 
                      WHERE gender='male' AND age>0 
                      GROUP BY booking_id HAVING COUNT(*) > 1 ) 
ORDER BY booking_id ASC, age ASC

第三个:

SELECT sno, booking_id, room_type, gender, age 
FROM customer_data 
WHERE booking_id IN ( SELECT booking_id FROM customer_data 
                      WHERE gender IN('male','female') AND age>0 
                      GROUP BY booking_id HAVING COUNT(distinct gender) = 2 ) 
ORDER BY booking_id ASC, age ASC

如果在前两个中您只想要只有一种性别的 booking_id，请添加到 having 子句:

AND COUNT(distinct gender) = 1