Reputation: 309
Say you have a large table with thousands of rows and 3 columns, looking something like this:
Name City Birthyear
Egon Spengler New York 1957
Mac Taylor New York 1955
Sarah Connor Los Angeles 1959
Jean-Luc Picard La Barre 2305
Ellen Ripley Nostromo 2092
James T. Kirk Riverside 2233
Henry Jones Chicago 1899
Jason Bourne Paris 1971
...... and so on. Is it possible to sort people by City into separate files? For example, if you have 100 or more cities in the table, is there a query that would create 100 or more files with people names and birth years in it? For a smaller number of cities one could use
-e "select Name, Birthyear from Table where City = New York" > NewYork.csv
and so on for each city in the table. But it's not fun to do it manually if you have hundreds of cities.
Upvotes: 1
Views: 1579
Reputation: 14684
From the sick and wrong department (may contain syntax errors, this is just to give you an idea of the twisted possibilities):
SELECT concat("SELECT name, birthyear from table where city = '",
city,
"' into outfile '",
city,"';")
FROM table
Put that in query1.sql, then:
mysql < query1.sql > query2.sql
mysql < query2.sql
Write a query that generates queries on the fly, then execute the resulting queries against the database. This definitely falls into the "quick and dirty" category.
Note also that the queries execute on the MySQL server, which could be a different box than where you're executing the MySQL client. If so, you'll need some way at getting at the resulting files. If you don't have shell access to the server, this could be a problem unless there is a network file system that the server can write to that you can read from another host.
Upvotes: 1
Reputation: 83635
You could also put "select Name, Birthyear from Table order by City" into a csv, then use a small script (Perl or something) to split the file where the name in the city column changes. Anyway, probably impossible without some external scripting.
Upvotes: 0
Reputation: 3274
I think your best bet would be to write a little script that does a SELECT DISTINCT on each column and uses the results to write the querys of the form you suggest.
Upvotes: 0