Anonymous
Anonymous asked in 電腦與網際網路程式設計 · 2 decades ago

另一個SQL的問題

請教一下

現在有兩個TABLE

CUSTOMER = Customer_ID

+ Customer_Name

POLICY = Policy_Number

+ Customer_ID

如果要查詢customer_name

條件是在policy裡面customer_id出現兩次(包含兩次)以上

並在customer_name後面顯示其customer_id出現的次數

該如何表達?

謝謝囉

Update:

哈囉 謝謝你們的幫助

不過我試過了 都沒有成功 我有試過修正啦

不是只有照抄而已

目前我頂多能做到讓它顯示ID跟次數

SELECT * FROM

(SELECT CUSTOMER_ID, COUNT(CUSTOMER_ID) AS TIMES FROM POLICY

GROUP BY CUSTOMER_ID) WHERE TIMES >= 2 ;

不知道能不能修正到可以讓它顯示NAME呢?

Update 2:

我把table的指令放上來

CREATE TABLE CUSTOMER

(Customer_Id VARCHAR(6) NOT NULL,

First_Name VARCHAR(25) ,

Family_Name VARCHAR(25) )

CREATE TABLE POLICY

(Policy_Number VARCHAR(7) NOT NULL,

Customer_Id VARCHAR(6) )

Update 3:

INSERT INTO CUSTOMER (Customer_Id, First_Name, Family_Name)

VALUES ('O12378', 'Fred', 'Krueger');

VALUES ('O14781', 'Russell', 'Mock');

VALUES ('O10098', 'Liam', 'Hodgson');

VALUES ('O10023', 'Susan', 'Fletcher');

VALUES ('O18904', 'Harold', 'Mitchell');

Update 4:

VALUES ('O19008', 'Henry', 'Black');

VALUES ('O20012', 'Harry', 'White');

VALUES ('O21222', 'Amelia', 'Earhart');

VALUES ('O12356', 'Charles', 'Lindberg');

VALUES ('O23415', 'Charles', 'Wright');

VALUES ('O24531', 'Charles', 'Kingsford/Smith');

Update 5:

VALUES ('O10102', 'David', 'Bigglesworth');

VALUES ('O19876', 'Helen', 'Highwater');

VALUES ('O17865', 'Konrad', 'Chamberlain');

VALUES ('O14623', 'Geoff', 'Boon');

VALUES ('O19646', 'Matt', 'Black');

VALUES ('O21345', 'Craig', 'Brookes');

Update 6:

INSERT INTO POLICY (Policy_Number, Customer_Id)

VALUES ('P198765', 'O12378');

VALUES ('P198766', 'O18904');

VALUES ('P198767', 'O21222');

VALUES ('P198768', 'O14781');

VALUES ('P198769', 'O17865');

VALUES ('P198770', 'O19876');

VALUES ('P198771', 'O14623');

Update 7:

VALUES ('P198772', 'O10098');

VALUES ('P198773', 'O21222');

VALUES ('P198774', 'O19008');

VALUES ('P198775', 'O23415');

VALUES ('P198776', 'O10098');

VALUES ('P198777', 'O10023');

VALUES ('P198778', 'O21345');

VALUES ('P198779', 'O21222');

Update 8:

VALUES ('P198780', 'O14623');

VALUES ('P198781', 'O19646');

VALUES ('P198782', 'O24531');

希望能有幫助

3 Answers

Rating
  • Anonymous
    2 decades ago
    Favorite Answer

    select * from (select a.customer_name,a.count(customer_id) as times from customer a,policy b where customer.customer_id=policy.customer_id group by   policy.customer_id )where times>=2

    Source(s):
  • Anonymous
    2 decades ago

    Oracle 9i 版本不確定 抱歉囉

    2005-09-24 20:53:54 補充:

    還是不行耶 會出現

    ORA-00933: SQL command not properly ended

    2005-09-26 07:53:55 補充:

    嗯嗯 我當然有加分號啊 要不然指令根本不會執行 只會跳下一行

  • Anonymous
    2 decades ago

    SELECT

    Customer_Name, Count(*) AS Counts

    FROM

    POLICY AS P

    JOIN

    CUSTOMER AS C ON P.Customer_ID = C.Customer_ID

    GROUP BY

    P.Customer_ID

    HAVING

    Count(*) >= 2

    不知道對不對@_@

    沒有資料表來測試

    也沒時間測,給你試試看0.0

    修改一下:

    SELECT

    C.Customer_Name, P.Counts

    FROM

    (

    SELECT

    Customer_ID, Count(*) AS Counts

    FROM

    POLICY

    GROUP BY

    Customer_ID

    Having

    Count(*) >= 2

    ) AS P

    LEFT JOIN

    CUSTOMER AS C ON P.Customer_ID = C.Customer_ID

    2005-09-22 19:19:46 補充:

    請問你是用什麼資料庫?

    什麼版本的?

    2005-09-23 20:28:47 補充:

    9i不就是版本嗎 ̄﹁ ̄

    2005-09-23 20:30:15 補充:

    修改後的SQL還是不能Run嗎?

    2005-09-25 22:33:02 補充:

    命令沒有正確終止@@

    Oracle的SQL結尾是不是要加分號呀

    我沒有用過Oracle,我是用MS SQL學SQL的

    這個語法應該是通用的

    我在公司看到的Oracle SQL語法好像都有加分號,不知道是不是這個問題@@

    Source(s): T-SQL
Still have questions? Get your answers by asking now.