I have some legacy code I'm maintaining and there's a SQL query in there that I don't under开发者_运维技巧stand. I'm hoping someone here can explain the purpose of this to me. The query looks like this:
select * from product_performance
where merchantid = 2151277 and clickday >= '2011-09-01'
group by null;
When I run this query without the group by null
at the end, I get 44 rows. When I run it with the group by null
, I get 1 row -- the first row in the previous set. What is happening here, and what is the purpose of using this grouping?
It's grouping the entire query by a constant value, which makes it pick a row effectively at random to return. You could get the same results using LIMIT 1
.
Note that this idiom is not standard SQL, and may cause errors if you try it in other databases. In MySQL, you can disable this behavior by specifying the ONLY FULL GROUP BY
SQL mode. See the documentation for more details.
OTOH, LIMIT 1
is non-standard as well, but more widely supported, and the same effect may be achieved in databases compliant with SQL:2008 with the FETCH ... ONLY
clause. See this for details on that.
hehe, let's explain in this mode: if you have
select emp_id, dept_id
from employees
group by dept_id
you'll get one row for every departament, and one emp_id, not guaranteed who. That means the big data is spitted in departaments.
if you shoot
select emp_id, dept_id
from employees
group by dept_id, emp_id
you'll get data splitted by depts and emps.
But if you group by null
, this means that you do not split anything. You have one group. And mysql will give you an emp_id, and a dept, not guaranteed from what row.
精彩评论