【求助】兩個MySQL語法的問題....
贊助商連結
Picasso 2005-10-13, 02:16 PM 不好意思,我想請問一下,關於MySQL語法的問題:
1. select first,count(first) as Total from book
group by first;
first Total
Bill 1
Blue 1
Pablo 1
Paloma 1
Ralf 1
Silvia 2
請問我要如何下指令,才能讓Total依大小排列(就是讓Silvia排在第一個)??
2. 查詢價格最高的5本書籍,並列出書名(BookName)、作者(Author)、價格 (Price /*int型態*/)、出版日期(PDate)。
我的指令這樣下的:
select BookName,Author,Price,PDate from book
where max(Price) limit 5
order by Price desc;
結果是錯誤,請問該如何下??
贊助商連結
試試這個:
select
first, count(first) as Total
from
book
group by
first
order by
count(first)
Picasso 2005-10-13, 05:12 PM 不好意思,我想請問一下,關於MySQL語法的問題:
2. 查詢價格最高的5本書籍,並列出書名(BookName)、作者(Author)、價格 (Price /*int型態*/)、出版日期(PDate)。
我的指令這樣下的:
select BookName,Author,Price,PDate from book
where max(Price) limit 5
order by Price desc;
結果是錯誤,請問該如何下??
第2題我解出來了!!
select BookName,Author,Price,PDate from book
order by Price desc limit 5;
我是根本就沒想到 limit 5 要加在Price後面!!
試試這個:
select
first, count(first) as Total
from
book
group by
first
order by
count(first) desc
Picasso 2005-10-13, 07:02 PM 試試這個:
select first, count(first) as Total from book
group by first
order by count(first) desc;
不行耶!!我從其它題目的經驗得知,count(), max(), avg()....etc這一類函數好像無法用在group by, order by上面,它會告訴你語法錯誤。
我在想無法使用group by和order by是否因為count(first) as Total本身是不存在的,當初在create table的時後,根本就沒有宣告total這樣的東西,只不過是透過count()這樣的函數,計算出在first欄位裡,silvia出現了兩次!!
不過我現在是要把計算出來後,由大到小排列!!....
不過還是多謝了,因為我又學到了另一種用法:原來group by, order by可以連著用!!就像我當初跟本沒想過desc後面還可以再接limit.
Shit!!還有Qt要做..... :|||:
leonchou 2005-10-13, 11:28 PM Oracle 可以巢狀的,這樣 --
select first, Total from (
select first, count(first) as Total from book
group by first )
order by Total desc;
不知 MySQL 可否?
另外,MySQL 好像有 Top 語法可找出前幾名?
joe.oo 2005-10-14, 04:03 AM 在最後面, 針對 count(first) 的別名 Total 排序就行了.
Picasso 2005-10-15, 01:08 PM Oracle 可以巢狀的,這樣 --
select first, Total from (
select first, count(first) as Total from book
group by first )
order by Total desc;
不知 MySQL 可否?
另外,MySQL 好像有 Top 語法可找出前幾名?
select first,total from (
select first,count(first) as total from book
group by first )
order by total desc;
ERROR 1248 (42000): Every derived table must have its own alias
Execuse me, how to use "top" clause??
Picasso 2005-10-15, 01:13 PM 在最後面, 針對 count(first) 的別名 Total 排序就行了.
How to do??
order by count(first) as total desc;
Right?? Not to do!! It'll have syntax error. ....>"<
leonchou 2005-10-15, 02:45 PM 好像搞不清MySQL和MSSQL了.. @@
Select Top 10 CustomerID,CompanyName,ContactName,Country from Customers
where (Country like '%UK%' or CompanyName like '%UK%')
Order by Country,CustomerID
select Top 10 b.* from (
select Top 20 主鍵欄位,排序欄位 from 表名 order by 排序欄位 desc) a,表名 b
where b.主鍵欄位 = a.主鍵欄位 order by a.排序欄位
http://kyle.jolin.info/tt/index.php?pl=259&ct1=2
http://blog.blueshop.com.tw/ajun/archive/2005/03/09/2183.aspx
|