【求助】兩個MySQL語法的問題....



贊助商連結


頁 : [1] 2

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;
結果是錯誤,請問該如何下??

贊助商連結


lomm
2005-10-13, 05:12 PM
試試這個:
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後面!!

lomm
2005-10-13, 05:18 PM
試試這個:
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