C, PHP, VB, .NET

Дневникът на Филип Петров


* Псевдоними на колони и таблици

Публикувано на 19 март 2009 в раздел Бази от Данни.

Псевдонимите на колони ни улесняват да пишем по-ясни записи на заявките. Нeка разгледаме пример от предишната статия:

Задача: Изведете името на банката и средната сума на акаунтите в нея, но само за банките, които имат буквата “M” някъде в името на банковия клон:

mysql> SELECT banks.name, AVG(accounts.amount)
FROM banks JOIN branches ON branches.bank_code = banks.code
           JOIN accounts ON accounts.branch_id = branches.id
WHERE branches.name LIKE '%M%'
GROUP BY banks.name;
+-----------------+-------------+
| name            | AVG(amount) |
+-----------------+-------------+
| Societe General | 2821.020000 |
| Wells Fargo     | 2775.247500 |
+-----------------+-------------+
2 rows in set (0.00 sec)

Виждате, че резултатът е таблица с две колони. Имената им са съответно „name“ и „AVG(amount)“. Въпреки, че от гледна точка на програмиста това е напълно достатъчно, то не е лошо да ги именуваме с по-описателни имена. Например:

mysql> SELECT banks.name AS bank_name, AVG(amount) AS average_amount
FROM banks JOIN branches ON branches.bank_code = banks.code
           JOIN accounts ON accounts.branch_id = branches.id
WHERE branches.name LIKE '%M%'
GROUP BY banks.name;
+-----------------+----------------+
| bank_name       | average_amount |
+-----------------+----------------+
| Societe General | 2821.020000    |
| Wells Fargo     | 2775.247500    |
+-----------------+----------------+
2 rows in set (0.00 sec)

Още повече – ако след where сме използвали в заявката пълното име, то можем да го заменим с неговия псевдоним. В случая това е само на едно място при сортирането:

mysql> SELECT banks.name AS bank_name, AVG(amount) AS average_amount
FROM banks JOIN branches ON branches.bank_code = banks.code
           JOIN accounts ON accounts.branch_id = branches.id
WHERE branches.name LIKE '%M%'
GROUP BY bank_name;
+-----------------+----------------+
| bank_name       | average_amount |
+-----------------+----------------+
| Societe General |    2821.020000 |
| Wells Fargo     |    2775.247500 |
+-----------------+----------------+
2 rows in set (0.00 sec)

Можем да правим и псевдоними вътре в заявката. Например нека разгледаме заявка, която изкарва името на клиента и сумата пари, които притежава, но само за клиентите с id> 3 и id < 10:

mysql> SELECT customers.name, SUM(accounts.amount)
FROM customers JOIN accounts ON customers.id = accounts.customer_id
WHERE customers.id > 3 AND customers.id < 10
GROUP BY customers.id
ORDER BY customers.name;
+-------------------+----------------------+
| name              | SUM(accounts.amount) |
+-------------------+----------------------+
| Atanas Petrov     |              1200.00 |
| Elica Zaharieva   |               211.98 |
| Ivan Ivanov       |               133.48 |
| Mariana Zaharieva |              1236.33 |
| Mihail Ivchev     |              1331.50 |
| Zlatomir Petrov   |               256.41 |
+-------------------+----------------------+
6 rows in set (0.02 sec)

Можем да „прекръстим“ таблицата customers с по-кратко име, като и зададем псевдоним в FROM:

mysql> SELECT cst.name, SUM(accounts.amount)
FROM customers AS cst JOIN accounts ON cst.id = accounts.customer_id
WHERE cst.id > 3 AND cst.id < 10
GROUP BY cst.id
ORDER BY cst.name;
+-------------------+----------------------+
| name              | SUM(accounts.amount) |
+-------------------+----------------------+
| Atanas Petrov     |              1200.00 |
| Elica Zaharieva   |               211.98 |
| Ivan Ivanov       |               133.48 |
| Mariana Zaharieva |              1236.33 |
| Mihail Ivchev     |              1331.50 |
| Zlatomir Petrov   |               256.41 |
+-------------------+----------------------+
6 rows in set (0.00 sec)

Така навсякъде, където сме се обръщали към таблицата customers вече задължително трябва да използваме псевдонима. По принцип преименуването на таблици, които си имат вече статични имена не се счита за добър начин на работа. Наистина често ни спестява писане  (ако използваме по-кратко име), но пък внася допълнително объркване, защото трябва да следим допълнителни данни.

Когато работим с таблици, които са извадки от други, то вече задължително трябва да им избираме псевдоним. Ще демонстрираме това с елементарна заявка, която изкарва броя на банките, които започват с буквата „B“:

mysql> SELECT COUNT(*)
FROM ( SELECT code, name FROM banks ) AS orgs
WHERE orgs.name LIKE 'B%';
+----------+
| COUNT(*) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

Е, в случая можехме да пропуснем вписването на такава таблица, но използвахме примера за демонстрация на техниката. Тази техника обаче ще ни е необходима когато имаме нужда от прилагане на агрегатна функция върху данни получени чрез използването на друга агрегатна функция.

Задача: Намерете броят на клиентите, чийто сметки от тип 2 имат общо повече от 500 долара.

 



16 коментара


  1. решение каза:

    SELECT COUNT(customers.name)
    FROM customers LEFT JOIN accounts ON customers.id = accounts.customer_id
    WHERE accounts.type = 2 AND accounts.amount > 500;

  2. Не, ако един клиент има две сметки от тип 2 с повече от 500 долара, то той ще бъде преброен два пъти. „решение“ даде грешно решение.

  3. Николай каза:

    Задачата с псевдоним ли трябва да се реши?

  4. „Както може“ трябва да се реши.

  5. DISTINCT на едно единствено число е самото то… Не решава проблема.

  6. Николай каза:

    SELECT DISTINCT COUNT(customers.name)
    FROM customers LEFT JOIN accounts ON customers.id = accounts.customer_id
    WHERE accounts.type = 2 AND accounts.amount > 500;

  7. Kiril каза:

    така ли трябва да изглежда решението на задачата?
    select count(*)
    from (
    select customers.name,accounts.type,SUM(accounts.amount)as ammount
    from customers join accounts on customers.id=accounts.customer_id
    where accounts.type=2
    group by customers.id
    ) as some
    where some.ammount>500;

  8. Kiril каза:

    select count(*)
    FROM (
    SELECT customers.name,accounts.type,SUM(accounts.amount)as ammount
    FROM customers JOIN accounts ON customers.id=accounts.customer_id
    WHERE accounts.type=2
    GROUP BY customers.id
    ) as some
    WHERE some.ammount>500;

  9. Student каза:

    Решението, което давам, подходящо ли е?

    SELECT COUNT(customers.name)
    FROM customers LEFT JOIN accounts ON customers.id=accounts.customer_id
    WHERE accounts.type=2 and amount>500;

  10. Kiril е дал решение с „общата сума, която притежава по всички сметки от акаунт 2, да е по-голяма от 500“. Доколкото условието търпи интерпретация, това е вярно.

    Сега ако търсим броя на хората, които имат поне една сметка в акаунт от тип 2, която има повече от 500, тогава заявката става така:

    SELECT COUNT(*)
    FROM (
       SELECT DISTINCT customers.id
       FROM customers
       WHERE customers.id IN(
    	SELECT accounts.customer_id
    	FROM accounts
    	WHERE accounts.type = 2 
    	  AND accounts.amount > 500
    	  AND accounts.customer_id = customers.id
       )
    ) AS t1;

    Ако търсим само хората, на които всеки един от акаунтите от тип 2 са с повече от 500, тогава става по-сложно.

  11. Ivo – има нещо нередно в твоето решение. Имаш „HAVING SUM(accounts.amount)>500“, без са си изчислявал SUM(accounts.amount) в SELECT. Но стана ясно какво имаш предвид като подход.

    По същество методите са едни и същи. Логически твоят подход е по-правилен, въпреки че повечето практици ще предпочетат другия (остаряло разбиране, че вложените SELECT заявки са лоши).

  12. Ivo каза:

    Това друг вариант на решението на Kiril ли е (връща ми същия резултат)?

    SELECT COUNT(*) FROM customers
    WHERE customers.id IN(
    SELECT accounts.customer_id
    FROM accounts
    WHERE accounts.type=2
    GROUP BY accounts.customer_id
    HAVING SUM(accounts.amount)>500
    );

  13. Да, то е възможно – MySQL вижда, че го желаеш и го изчислява – но е нетипично.

  14. Ivo каза:

    В stackoverflow видях следния пример:
    SELECT Name FROM Usernames
    GROUP BY Name
    HAVING COUNT(*) > 1
    , където COUNT(*) не е в SELECT-а, но го има в HAVING. Питах и в един форум и ми казаха, че няма проблем…

  15. Radoslav каза:

    Още едно решение . При проверката на входните данни излиза че е вярно .

    select count(*)
    from customers as cst join accounts as acc on acc.customer_id=cst.id
    where acc.type=2 and acc.amount>500;

  16. Радослав,

    Добавих една думичка за уточнение към задачата. Сметките от тип 2 трябва да имат ОБЩО над 500, т.е. първо трябва да сумираш сметките от тип 2 и след това да проиш.

Добави коментар

Адресът на електронната поща няма да се публикува


*