需要帮助sql查询，分组和union / join？

I have a mysql table with 3 columns:

  • id [int]
  • name [varchar]
  • time [timestamp CURRENT_TIMESTAMP]
  • flag [boolean]

I am using a query like this to group the results by day:

SELECT COUNT(DISTINCT name), DATE(time) as day FROM `table` GROUP BY day

That works fine, but what I really want to get COUNT(DISTINCT name) WHERE FLAG = 0 and COUNT(DISTINCT name) WHERE FLAG = 1 in the same query and group them by day.

How can I do that?

Then you have to apply grouping first on flag then on day

SELECT COUNT(DISTINCT name), DATE(time) as day FROM `table` GROUP BY flag,day

I am wondering that you want to create the result with a stored procedure or only a select query?

"date" 
"count number of flag = 1" 
"count number of flag =0"

You can

  1. create 2 template tables and insert results from query "flag =1" and query of "flag = 0" into them
  2. join 2 template tables on "date".

Your question is not very clear but I think this might help.. SELECT COUNT(DISTINCT name), DATE(time) as day FROM table GROUP BY day HAVING FLAG=0 and FLAG=1

Or you can manipulate this query as you want to group..