如何将独列值作为输入输入到另一个选定语句
原标题：How to use unique column values as input into another select statement
I have a table (MySQL) that has a column called binID. The values in this column can range from 1 to 70.
What I want to do is select the unique values of this column (should be the numbers from 1 to 70), then iterate over them using each (lets call it theBinID) as a parameter into another SELECT statement such as:
SELECT * FROM MyTable WHERE binID = theBinID ORDER BY createdDate DESC LIMIT 10
Basically, I am looking to get the 10 most recent rows for each binID.
I do not believe there is a way to do this with a basic SQL statement, although I would love that to be the answer, so I have written a stored procedure that creates a cursor on the SELECT DISTINCT of binIDs, and then iterates over it and populates a temp table.
My problem is, this is for an optimization, and if I fetch 100K rows I get 1.7 second average time. Executing my stored procedure to get 700 rows (10 records for 70 bins) is taking 1.4 seconds. I realize that 0.3 seconds could be perceived as considerable improvement, but I am hoping to get this sub-second for 100K rows.
Is there a better way??
The full stored proc is this:
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE binID INT;
DECLARE cur1 CURSOR FOR SELECT DISTINCT heatmapBinID from MEStressTest ORDER BY heatmapBinID ASC;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
DROP TEMPORARY TABLE IF EXISTS TempResults;
CREATE TEMPORARY TABLE TempResults (
`recordID` text NOT NULL,
`queryTerm` text NOT NULL,
`recordCreated` double(11,0) NOT NULL,
`recordByID` text NOT NULL,
`recordByName` text NOT NULL,
`recordText` text NOT NULL,
`recordSource` text NOT NULL,
`rerecordCount` int(11) NOT NULL DEFAULT 0 ,
`timecodeOffset` int(11) NOT NULL DEFAULT -1 ,
`recordByImageURL` text NOT NULL,
`canDelete` int(11) NOT NULL DEFAULT 1 ,
`heatmapBinID` int(11) DEFAULT NULL,
`timelineBinID` int(11) DEFAULT NULL,
PRIMARY KEY (`recordID`(20))
);
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO binID;
IF done THEN
LEAVE read_loop;
END IF;
INSERT INTO TempResults (recordID, queryTerm, recordCreated, recordByID, recordByName, recordText, recordSource, rerecordCount, timecodeOffset, recordByImageURL, canDelete, heatmapBinID, timelineBinID)
SELECT * FROM MEStressTest WHERE heatmapBinID = binID ORDER BY recordCreated DESC LIMIT numRecordsPerBin;
END LOOP;
CLOSE cur1;
SELECT * FROM TempResults ORDER BY heatmapBinID ASC, recordCreated DESC;
END
最佳回答
Try to simulate ROW_NUMBER OVER PARTITION in MySQL: http://www.sqlfiddle.com/#!2/fd8b5/4
Given this data:
create table sentai(
band varchar(50),
member_name varchar(50),
member_year int not null
);
insert into sentai(band, member_name, member_year) values
( BEATLES , JOHN ,1960),
( BEATLES , PAUL ,1961),
( BEATLES , GEORGE ,1962),
( BEATLES , RINGO ,1963),
( VOLTES V , STEVE ,1970),
( VOLTES V , MARK ,1971),
( VOLTES V , BIG BERT ,1972),
( VOLTES V , LITTLE JOHN ,1973),
( VOLTES V , JAMIE ,1964),
( ERASERHEADS , ELY ,1990),
( ERASERHEADS , RAYMUND ,1991),
( ERASERHEADS , BUDDY ,1992),
( ERASERHEADS , MARCUS ,1993);
Object, find all three most recent members on each band.
First we have to put a row_number on each member based on most year(via descending order)
select *,
@rn := @rn + 1 as rn
from (sentai s, (select @rn := 0) as vars)
order by s.band, s.member_year desc;
Output:
| BAND | MEMBER_NAME | MEMBER_YEAR | @RN := 0 | RN |
|-------------|-------------|-------------|----------|----|
| BEATLES | RINGO | 1963 | 0 | 1 |
| BEATLES | GEORGE | 1962 | 0 | 2 |
| BEATLES | PAUL | 1961 | 0 | 3 |
| BEATLES | JOHN | 1960 | 0 | 4 |
| ERASERHEADS | MARCUS | 1993 | 0 | 5 |
| ERASERHEADS | BUDDY | 1992 | 0 | 6 |
| ERASERHEADS | RAYMUND | 1991 | 0 | 7 |
| ERASERHEADS | ELY | 1990 | 0 | 8 |
| VOLTES V | LITTLE JOHN | 1973 | 0 | 9 |
| VOLTES V | BIG BERT | 1972 | 0 | 10 |
| VOLTES V | MARK | 1971 | 0 | 11 |
| VOLTES V | STEVE | 1970 | 0 | 12 |
| VOLTES V | JAMIE | 1964 | 0 | 13 |
Then we reset the row number when a member is on different band:
select *,
@rn := IF(@pg = s.band, @rn + 1, 1) as rn,
@pg := s.band
from (sentai s, (select @pg := null, @rn := 0) as vars)
order by s.band, s.member_year desc;
Output:
| BAND | MEMBER_NAME | MEMBER_YEAR | @PG := NULL | @RN := 0 | RN | @PG := S.BAND |
|-------------|-------------|-------------|-------------|----------|----|---------------|
| BEATLES | RINGO | 1963 | (null) | 0 | 1 | BEATLES |
| BEATLES | GEORGE | 1962 | (null) | 0 | 2 | BEATLES |
| BEATLES | PAUL | 1961 | (null) | 0 | 3 | BEATLES |
| BEATLES | JOHN | 1960 | (null) | 0 | 4 | BEATLES |
| ERASERHEADS | MARCUS | 1993 | (null) | 0 | 1 | ERASERHEADS |
| ERASERHEADS | BUDDY | 1992 | (null) | 0 | 2 | ERASERHEADS |
| ERASERHEADS | RAYMUND | 1991 | (null) | 0 | 3 | ERASERHEADS |
| ERASERHEADS | ELY | 1990 | (null) | 0 | 4 | ERASERHEADS |
| VOLTES V | LITTLE JOHN | 1973 | (null) | 0 | 1 | VOLTES V |
| VOLTES V | BIG BERT | 1972 | (null) | 0 | 2 | VOLTES V |
| VOLTES V | MARK | 1971 | (null) | 0 | 3 | VOLTES V |
| VOLTES V | STEVE | 1970 | (null) | 0 | 4 | VOLTES V |
| VOLTES V | JAMIE | 1964 | (null) | 0 | 5 | VOLTES V |
Then we select only the most recent three members on each band:
select x.band, x.member_name, x.member_year
from
(
select *,
@rn := IF(@pg = s.band, @rn + 1, 1) as rn,
@pg := s.band
from (sentai s, (select @pg := null, @rn := 0) as vars)
order by s.band, s.member_year desc
) as x
where x.rn <= 3
order by x.band, x.member_year desc;
Output:
| BAND | MEMBER_NAME | MEMBER_YEAR |
|-------------|-------------|-------------|
| BEATLES | RINGO | 1963 |
| BEATLES | GEORGE | 1962 |
| BEATLES | PAUL | 1961 |
| ERASERHEADS | MARCUS | 1993 |
| ERASERHEADS | BUDDY | 1992 |
| ERASERHEADS | RAYMUND | 1991 |
| VOLTES V | LITTLE JOHN | 1973 |
| VOLTES V | BIG BERT | 1972 |
| VOLTES V | MARK | 1971 |
While windowing function(e.g. ROW_NUMBER OVER PARTITION) is not yet available on MySQL, just simulate it with variables. Please let us know if this is faster than cursor approach
How it looks like on windowing-capable RDBMS: http://www.sqlfiddle.com/#!1/fd8b5/6
with member_recentness as
(
select row_number() over each_band as recent, *
from sentai
window each_band as (partition by band order by member_year desc)
)
select *
from member_recentness
where recent <= 3;
Output:
| RECENT | BAND | MEMBER_NAME | MEMBER_YEAR |
|--------|-------------|-------------|-------------|
| 1 | BEATLES | RINGO | 1963 |
| 2 | BEATLES | GEORGE | 1962 |
| 3 | BEATLES | PAUL | 1961 |
| 1 | ERASERHEADS | MARCUS | 1993 |
| 2 | ERASERHEADS | BUDDY | 1992 |
| 3 | ERASERHEADS | RAYMUND | 1991 |
| 1 | VOLTES V | LITTLE JOHN | 1973 |
| 2 | VOLTES V | BIG BERT | 1972 |
| 3 | VOLTES V | MARK | 1971 |
问题回答
SELECT * FROM MyTable WHERE binID IN (SELECT DISTINCT(bin_id) FROM mysql_table) ORDER BY createdDate DESC LIMIT 10;
This is not tested, nevermind the syntax.
Add indexes to increase performance.
If you try to inner join 2 tables without any joining key, it will be a cartesian product of the 2 tables, i.e.:
SELECT *
FROM MyTable t
INNER JOIN (SELECT DISTINCT binId FROM MyTable) AS u
WHERE
t.binID = theBinID
ORDER BY t.createdDate DESC LIMIT 10
You may refer to this
相关问题
SQL SubQuery getting particular column
I noticed that there were some threads with similar questions, and I did look through them but did not really get a convincing answer. Here s my question:
The subquery below returns a Table with 3 ...
please can anyone check this while loop and if condition
<?php
$con=mysql_connect("localhost","mts","mts");
if(!con)
{
die( unable to connect . mysql_error());
}
mysql_select_db("mts",$con);
/*
date_default_timezone_set ("Asia/Calcutta");
$date = ...
Zend Framework: Authentication, MySQL在从Qalite转变时的错误
我把我的用心从使用QQL转向MySQL。 它与凯科特合作,现在不工作,因为我已经改变,使用MySQL。 这里的错误信息是:
SQL Query for Performing Radius Search based on Latitude Longitude
We have a restaurant table that has lat-long data for each row.
We need to write a query that performs a search to find all restaurants within the provided radius e.g. 1 mile, 5 miles etc.
We have ...
php return a specific row from query
Is it possible in php to return a specific row of data from a mysql query?
None of the fetch statements that I ve found return a 2 dimensional array to access specific rows.
I want to be able to ...
Character Encodings in PHP and MySQL
Our website was developed with a meta tag set to...
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" />
This works fine for M-dashes and special quotes, etc. However, I ...
Pagination Strategies for Complex (slow) Datasets
What are some of the strategies being used for pagination of data sets that involve complex queries? count(*) takes ~1.5 sec so we don t want to hit the DB for every page view. Currently there are ~...
Averaging a total in mySQL
My table looks like
person_id | car_id | miles
------------------------------
1 | 1 | 100
1 | 2 | 200
2 | 3 | 1000
2 | 4 | 500
I need to ...
