按文件类型划分显示的数据[关闭]

id      time      filetype
381839  604208      ABC
414263  340248      ABC
415358  665808      DEF
428034  215805      XYZ
430640  202388      XYZ
436590  4739780     ABC
438848  635382      DEF

Above table is the result of my mysql query. Currently it just displays a giant table with above columns. It displays all filetypes in one column currently.

select * 
from  `table name` 
where id= `value`

Objective: I want to get different tables divided by filetypes rather than one large table mixed with all filetypes e.g.

Table 1:
id      time      filetype
381839  604208      ABC
414263  340248      ABC
436590  4739780     ABC

Table 2:
428034  215805      XYZ
430640  202388      XYZ

Table 3:
415358  665808      DEF
438848  635382      DEF

and so on.

Is there a way in sql query I can do that, or is it specific to HTML tags that the division can take place. ? I tried grouping them together by GROUP BY but this was not what I anticipated. Any idea in this regard will be appreciated.

Inserting lines between groups is a client-side function. You can use analytical functions to null out the repeating fields, if that's what you are looking for, however last time I looked, Mysql doesn't have ROW_NUMBER() OVER ( ) functionality.

Also, we normally call that a 'break' in a report.

Just order by the filetype field for the query, then when it changes, change the HTML appropriately.

You said I want to get subset of one large table divided by filetype then just use a WHERE condition like

select * 
from  `table name` 
where filetype= 'ABC'

Based on your comment, you will have to do it separately like

create table newtbl
as
select * 
from  `table name` 
where filetype= 'ABC'

(OR)

Use a stored procedure like (Per latest comment, instead of creating new table go for a view)

create procedure newinsert (IN tabname varchar(20), IN catname varchar(10))
as
begin
    create view tabname
    as
    select * 
    from  `table name` 
    where filetype= catname
end

Than you can call your procedure like

call newinsert('newtbl','ABC') 
call newinsert('newtbl1','XYZ') 
call newinsert('newtbl2','DEF') 

In that way, there will not be any overhead; since view doesn't store data by itself. other than this, if you are just looking for display the segregated data then I think your best bet is to get it done in your application.

I presume you want to segregate the result when you display. That cannot be done in the SQL. When you use group by you are just grouping the results and that is not how it will be displayed. Displaying it in distinctly marked groups needs to be done on the UI using HTML/CSS if that is what you are using.

Do you mean this?

SELECT * From table ORDER BY filetype ASC

To group the answers with tags, you can loop the result:

$row=mysqli_fetch_assoc($result);
$type=$row['filetype'];
while($row=mysqli_fetch_assoc($result)){
 if($row['filetype']!=$type){
  echo 'NEW TYPE';
  $type=$row['filetype'];
 }
}

Reading your comment on @rageit answer, I would

SELECT filetype FROM table GROUP BY filetype

So you would get all filetype available, then foreach filetype as type:

SELECT * FROM table WHERE filetype = type

to get each result to handle individually.