请帮我写查询 [英] please help me to write queries

问题描述

我有下面提到的三个表bank bank_id bank_name ------- --------- 1 sbi 2 icici 3 hdfc 4 rbs 5 hsbc location location_id location_city --------- - ------------ 1 NY 2 Colorado 3 gzb 4 london 5 bankok bank_location bank_id location_id ---- ------ 1 1 1 2 1 3 1 4 1 5 3 2 3 4 4 1 5 2 5 5



1）写一个查询列出所有地点的分行的银行名称？







i试过如下查询：

i have three tables mentioned below bank bank_id bank_name ------- --------- 1 sbi 2 icici 3 hdfc 4 rbs 5 hsbc location location_id location_city ----------- ------------ 1 NY 2 Colorado 3 gzb 4 london 5 bankok bank_location bank_id location_id ---- ------ 1 1 1 2 1 3 1 4 1 5 3 2 3 4 4 1 5 2 5 5

1) write a query to list the bank name with branches at all locations ?



i have tried like below query:

SELECT a.bank_name, b.location_city
  FROM bank a,
       location b,
       (SELECT *
          FROM (  SELECT bank_id, COUNT (location_id) location_count
                    FROM bank_location
                GROUP BY bank_id
                ORDER BY location_count DESC)
         WHERE ROWNUM <=1) c
WHERE a.bank_id = c.bank_id;

输出如：



bank_name location_city

-------- --------- ----

汇丰DELHI

汇丰NOIDA

汇丰GZB

汇丰银行

汇丰GOA





但我的先生说输出是正确的但查询有错误。

- 请解决这个问题。

output like:

bank_name location_city
-------- -------------
HSBC DELHI
HSBC NOIDA
HSBC GZB
HSBC MUMBAI
HSBC GOA


but my sir saying output was right but query having mistake.
--please solve this.

SELECT B.BANK_NAME,BL.LOC_CITY FROM BANKNAME B
 INNER JOIN LOCA_BANK L ON L.BANK_ID=B.BANK_ID
 INNER JOIN BANK_LOCATION BL ON BL.LOC_ID=L.LOCATION_ID.

这个查询给出了所有分行的所有银行名称。

但我需要获取哪个银行拥有所有位置





i尝试以下查询

this query giving output all bank names with all branches.
but i need to fetch which bank having all locations


i tried below query

SELECT a.bank_name, b.location_city
  FROM bank a,
       location b,( SELECT bank_name, COUNT (location_city) no_locations
    FROM (SELECT a.bank_name, b.location_city
            FROM bank a
                 INNER JOIN bank_location c
                    ON a.bank_id = c.bank_id
                 INNER JOIN location b
                    ON c.location_id = b.location_id)
GROUP BY bank_name
ORDER BY no_locations DESC) c
 where a.bank_name=c.bank_name and c.no_locations=(select count(*) from location);

此查询也给出了正确的输出。但他再次说查询错误。



请帮忙解决这个问题。

this query also giving correct output .But He said again query is wrong.

please help to solve this.

推荐答案

SELECT B.BANK_NAME,BL.LOC_CITY FROM BANKNAME B
 INNER JOIN LOCA_BANK L ON L.BANK_ID=B.BANK_ID
 INNER JOIN BANK_LOCATION BL ON BL.LOC_ID=L.LOCATION_ID

我们不做你的作业：它是有原因的。它就是为了让你思考你被告知的事情，并试着理解它。它也在那里，以便您的导师可以识别您身体虚弱的区域，并将更多的注意力集中在补救措施上。



亲自尝试，你可能会发现它不是和你想的一样困难！

We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.

Try it yourself, you may find it is not as difficult as you think!

这篇关于请帮我写查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！