안녕하세요. 게시판이 여러개가 있는데 메인 페이지에서 각 게시판에 있는글들을 최근글이나 조회수가 높은 글을 2개씩 가져와서 보여주려고 합니다. select * from db group by code order by hits desc; 이정도로 하면 될것 같은데 2개씩 뽑는건 어떻게 해야 할지 모르겠습니다. 고수님들의 많은 조언 바랍니다. 감사합니다.
음 버즌이 뭔가요?
4.1 이상이라면 union 을 쓰면 되겠습니다.
석이님 부디 가르쳐 주세요...어떻게 하나요?
UNION
SELECT ... UNION [ALL | DISTINCT] SELECT ... [UNION [ALL | DISTINCT] SELECT ...]
UNION is used to combine the result from many SELECT statements into one result set. UNION is available from MySQL 4.0.0 on.
SELECT
Selected columns listed in corresponding positions of each SELECT statement should have the same type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.) The column names used in the first SELECT statement are used as the column names for the results returned.
The SELECT statements are normal select statements, but with the following restrictions:
Only the last SELECT statement can have INTO OUTFILE.
INTO OUTFILE
HIGH_PRIORITY cannot be used with SELECT statements that are part of a UNION. If you specify it for the first SELECT, it has no effect. If you specify it for any subsequent SELECT statements, a syntax error results.
HIGH_PRIORITY
If you don't use the keyword ALL for the UNION, all returned rows are unique, as if you had done a DISTINCT for the total result set. If you specify ALL, you get all matching rows from all the used SELECT statements.
ALL
DISTINCT
The DISTINCT keyword is an optional word (introduced in MySQL 4.0.17). It does nothing, but is allowed in the syntax as required by the SQL standard.
Before MySQL 4.1.2, you cannot mix UNION ALL and UNION DISTINCT in the same query. If you use ALL for one UNION, it is used for all of them. As of MySQL 4.1.2, mixed UNION types are treated such that a DISTINCT union overrides any ALL union to its left. A DISTINCT union can be produced explicitly by using UNION DISTINCT or implicitly by using UNION with no following DISTINCT or ALL keyword.
UNION ALL
UNION DISTINCT
If you want to use an ORDER BY or LIMIT clause to sort or limit the entire UNION result, parenthesize the individual SELECT statements and place the ORDER BY or LIMIT after the last one. The following example uses both clauses:
ORDER BY
LIMIT
(SELECT a FROM tbl_name WHERE a=10 AND B=1) UNION (SELECT a FROM tbl_name WHERE a=11 AND B=2) ORDER BY a LIMIT 10;
tbl_name
This kind of ORDER BY cannot use column references that include a table name (that is, names in tbl_name.col_name format). Instead, provide a column alias in the first SELECT statement and refer to the alias in the ORDER BY, or else refer to the column in the ORDER BY using its column position. (An alias is preferable because use of column positions is deprecated.)
tbl_name.col_name
To apply ORDER BY or LIMIT to an individual SELECT, place the clause inside the parentheses that enclose the SELECT:
(SELECT a FROM tbl_name WHERE a=10 AND B=1 ORDER BY a LIMIT 10) UNION (SELECT a FROM tbl_name WHERE a=11 AND B=2 ORDER BY a LIMIT 10);
ORDER BY for individual SELECT statements within parentheses only has an effect when combined with LIMIT. Otherwise, the ORDER BY is optimized away.
The types and lengths of the columns in the result set of a UNION take into account the values retrieved by all the SELECT statements. Before MySQL 4.1.1, a limitation of UNION is that only the values from the first SELECT are used to determine result column types and lengths. This could result in value truncation if, for example, the first SELECT retrieves shorter values than the second SELECT:
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10); +---------------+ | REPEAT('a',1) | +---------------+ | a | | b | +---------------+
That limitation has been removed as of MySQL 4.1.1:
mysql> SELECT REPEAT('a',1) UNION SELECT REPEAT('b',10); +---------------+ | REPEAT('a',1) | +---------------+ | a | | bbbbbbbbbb | +---------------+
감사합니다..
새빠지게 해봤더니...ㅜ.ㅜ;..
버전이 낮네요....ㅜ.ㅜ;....
감사합니다.
좋은 공부했습니다.