MySQL return a blank row every other row from a select?
I'm wondering if there is a way for a select statement to return an empty row (or a row with all empty values for each column) every开发者_StackOverflow中文版 other row, so that I can avoid doing this after exporting the results from MySQL.
I've been unable to do so any way I could think of, and google leads me no where.
Lets say I have a table user
with 3 columns: user_id
, user_name
and password
. Here is a method using a LEFT JOIN
to select a row of null
values for every columns of a table without specifying the columns individually in the SELECT
:
SELECT u.*
FROM (SELECT null) a
LEFT JOIN `user` u
ON false;
This query will return:
+---------+-----------+----------+
| user_id | user_name | password |
+---------+-----------+----------+
| NULL | NULL | NULL |
+---------+-----------+----------+
I'm not 100% sure I got what you want. Are you looking for something like SELECT * FROM table1 LIMIT 0
?
Sometimes this is really necessary, such as when using a database for applications.
Use just:
select 0 limit 0;
(note: I cannot leave comments yet, so I'm posting this here) Maybe this will help you get started?
You would have to find a way to loop through this:
SELECT col1, col2, col3 FROM table LIMIT 1 UNION SELECT "", "", "";
set @i := 0;
set @j := 1;
select c1
from (
select c1, @i := @i + 2 as o
from my_table
union
select '' as c1, @j := @j + 2 as o
from my_table
) ss
order by o
;
SELECT col1, col2, col3
FROM (
SELECT dummy, col1, col2, col3
FROM (
SELECT col1 AS dummy, col1, col2, col3
FROM dumbtask
ORDER BY col1
) t1
UNION ALL
(
SELECT col1 AS dummy, NULL AS col1, NULL AS col2, NULL AS col3
FROM dumbtask
ORDER BY dummy
)
ORDER BY dummy) t3;
I have to say though ... this is one of the most stupid tasks that I've seen lately.
Instead of using Select, it is possible just place
values row(null)
or
values row('Or', 'any', 'other'), row('rows','and','columns')
精彩评论