I got an error: #1242 — Subquery returns more than 1 row when i run this sql.
CREATE VIEW test
AS
SELECT cc_name,
COUNT() AS total,
(SELECT COUNT(*)
FROM bed
WHERE respatient_id > 0
GROUP BY cc_name) AS occupied_beds,
(SELECT COUNT(*)
FROM bed
WHERE respatient_id IS NULL
GROUP BY cc_name) AS free_beds
FROM bed
GROUP BY cc_name;
OMG Ponies
324k80 gold badges520 silver badges499 bronze badges
asked Oct 23, 2011 at 16:00
6
The problem is that your subselects are returning more than one value — IE:
SELECT ...
(SELECT COUNT(*)
FROM bed
WHERE respatient_id IS NULL
GROUP BY cc_name) AS free_beds,
...
…will return a row for each cc_name, but SQL doesn’t support compacting the resultset for the subselect — hence the error.
Don’t need the subselects, this can be done using a single pass over the table using:
SELECT b.cc_name,
COUNT(*) AS total,
SUM(CASE
WHEN b.respatient_id > 0 THEN 1
ELSE 0
END) AS occupied_beds,
SUM(CASE
WHEN b.respatient_id IS NULL THEN 1
ELSE 0
END) AS free_beds
FROM bed b
GROUP BY b.cc_name
answered Oct 23, 2011 at 16:05
OMG PoniesOMG Ponies
324k80 gold badges520 silver badges499 bronze badges
8
This is because your subqueries (the SELECT bits that are inside parentheses) are returning multiple rows for each outer row. The problem is with the GROUP BY; if you want to use subqueries for this, then you need to correlate them to the outer query, by specifying that they refer to the same cc_name as the outer query:
CREATE VIEW test
AS
SELECT cc_name,
COUNT() AS total,
(SELECT COUNT()
FROM bed
WHERE cc_name = bed_outer.cc_name
AND respatient_id > 0) AS occupied_beds,
(SELECT COUNT(*)
FROM bed
WHERE cc_name = bed_outer.cc_name
WHERE respatient_id IS NULL) AS free_beds
FROM bed AS bed_outer
GROUP BY cc_name;
(See http://en.wikipedia.org/wiki/Correlated_subquery for information about correlated subqueries.)
But, as OMG Ponies and a1ex07 say, you don’t actually need to use subqueries for this if you don’t want to.
answered Oct 23, 2011 at 16:11
ruakhruakh
174k26 gold badges268 silver badges305 bronze badges
2
Your subqueries return more than 1 row. I think you you need something like :
SELECT COUNT(*) AS total,
COUNT(CASE WHEN respatient_id > 0 THEN 1 END) AS occupied_beds,
COUNT(CASE WHEN respatient_id IS NULL THEN 1 END) AS free_beds
FROM bed
GROUP BY cc_name
You can also try to use WITH ROLLUP + pivoting (mostly for learning purposes, it’s a much longer query ) :
SELECT cc_name,
MAX(CASE
WHEN num_1 = 1 THEN tot_num END) AS free_beds,
MAX(CASE
WHEN num_1 = 2 THEN tot_num END) AS occupied_beds,
MAX(CASE
WHEN num_1 = IS NULL THEN tot_num END) AS total
FROM
(SELECT cc_name, CASE
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END as num_1,
COUNT(*) as tot_num
FROM bed
WHERE
CASE
WHEN respatient_id > 0 THEN 1
WHEN respatient_id IS NULL THEN 2
ELSE 3 END != 3
GROUP BY cc_name,
num_1 WITH ROLLUP)A
GROUP BY cc_name
answered Oct 23, 2011 at 16:06
a1ex07a1ex07
36.8k12 gold badges90 silver badges103 bronze badges
SELECT COUNT()
FROM bed
WHERE respatient_id > 0
GROUP BY cc_name
You need to remove the group-by in the sub query, so possibly something like
SELECT COUNT(*)
FROM bed
WHERE respatient_id > 0
or possibly — depending on what your application logic is….
SELECT COUNT(*) from (
select count(*),cc_name FROM bed
WHERE respatient_id > 0
GROUP BY cc_name) filterview
answered Oct 23, 2011 at 16:02
SorenSoren
14.3k4 gold badges41 silver badges67 bronze badges
Здравствуйте, не понимаю какое добавить еще условие, чтобы решить
DELIMITER //
CREATE TRIGGER Buget_Trigger
AFTER INSERT
ON Buget5
FOR EACH ROW BEGIN
if((select MaxCount from Buget5 where (select max(dateee) where dateee<(now())) )<(select Buget from Buget5 where (select max(dateee) where dateee<(now())))) then
update Insects set Count=Count+20 where(select max(datee) where datee<(now()));
update Mammals set Count=Count+20 where(select max(datee) where datee<(now()));
update ColdBloodedness set Count=Count+20 where(select max(datee) where datee<(now()));
end if;
END //
insert into Mammals(mammals_id, id, typee, count, datee) VALUES (1,null,'',7,'2019-12-13');
insert into ColdBloodedness(ColdBloodedness_Id, id, typee,datee, count) VALUES (1,null,'','2019-12-13',10);
insert into Insects(insects_id, id, typee, count,datee) VALUES (1,null,'',5, '2019-12-13');
insert into Buget5(buget_id, id, buget, maxcount, dateee) VALUES (1, null,600, 200,'2019-12-13');
select * from Insects;
select * from ColdBloodedness;
select * from Mammals;Но если сделаю второй раз, то ничего не сработает и будет ошибка 1242
insert into Buget5(buget_id, id, buget, maxcount, dateee) VALUES (2, null,600, 200,'2019-12-13');
select * from Insects;
select * from ColdBloodedness;
select * from Mammals;Бывает так, что подзапрос одиночной строки возвращает более одной строки. В таком случае возникнет ошибка.
Для каждого магазина найдем одного сотрудника с должностью 'MANAGER'.
SELECT s.store_id,
s.name,
(SELECT e.employee_id
FROM employee e
WHERE e.store_id = s.store_id
AND e.rank_id = 'MANAGER'
) AS employee_id
FROM store s
ORDER BY s.store_id
error: more than one row returned by a subquery used as an expression
Посмотрим, что там с данными не так?
SELECT e.store_id,
e.rank_id,
e.last_name || ' ' || e.first_name AS full_name
FROM employee e
WHERE e.rank_id = 'MANAGER'
ORDER BY e.store_id, e.last_name, e.first_name
В магазине 201 два менеджера, а в магазине 600 — три.
В такой ситуации есть два варианта развития событий. Мы можем решать неправильную задачу. Если в магазине может быть несколько менеджеров, то мы должны работать с массивом менеджеров. В таком случае может быть приемлемым получить одного из них, например, первого по алфавиту.
Возможно, менеджер в магазине должен быть только один. Просто кто-то не создал уникальный ключ, и пользователи создали несколько менеджеров в магазине. В таком случае, мы написали правильный запрос и необходимо избавляться от неправильных данных и дорабатывать структуру данных.
В любом случае, необходимо гарантировать, что в результате подзапроса будет возвращено не более одной строки. Для этого необходимо одно из:
- должен быть уникальный ключ, гарантирующий, что в результате подзапроса будет не более одной строки;
- использовать агрегатную функцию;
- использовать
LIMIT 1для ограничения количества строк.
Воспользуемся LIMIT 1:
SELECT s.store_id,
s.name,
(SELECT e.employee_id
FROM employee e
WHERE e.store_id = s.store_id
AND e.rank_id = 'MANAGER'
ORDER BY e.last_name,
e.first_name,
e.middle_name
LIMIT 1
) AS employee_id
FROM store s
ORDER BY s.store_id
P.S. Если нам нужен список ФИО, то можно воспользоваться string_agg:
SELECT s.store_id,
s.name,
(SELECT string_agg (
e.last_name || ' ' || e.first_name, '; '
ORDER BY e.last_name,
e.first_name
)
FROM employee e
WHERE e.store_id = s.store_id
AND e.rank_id = 'MANAGER'
) AS employees
FROM store s
ORDER BY s.store_id
Таких функций в PostgreSQL довольно много, и они заслуживают отдельной темы.
You need to add the LIMIT clause
SELECT id FROM tbl_play WHERE id = 1 OR gameid = 0 LIMIT 1;
This is an oversimplified answer.
If there is a row with PK=1, you find it.
If not, a full table scan may ensue because of gameid not being indexed.
You are better off splitting the query with a UNION. Perhaps, like this:
SELECT id FROM
(
SELECT id FROM tbl_play WHERE id = 1
UNION
(SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;
Since I do not know the data, I cannot predict any performance results.
Give it a Try !!!
UPDATE 2014-01-23 11:01 EST
The reason it works because of the goal I set for the query : Retrieve one value.
First look at the UNION
SELECT id FROM tbl_play WHERE id = 1
UNION
(SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
The first SELECT can return at most one row.
The second SELECT can return at most one row because of the LIMIT 1.
In the worst case scenario, the UNION will have two rows.
Now look at the whole query:
SELECT id FROM
(
SELECT id FROM tbl_play WHERE id = 1
UNION
(SELECT id FROM tbl_play WHERE gameid = 0 LIMIT 1)
) A ORDER BY id LIMIT 1;
The outer part of the query will receive either 0, 1, or 2 rows.
Then, the part ORDER BY id LIMIT 1 forces the outer query to choose the first value.
#1 05.10.2017 16:32:04
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Вывод больше чем одну строку во вложенном подзапросе
Вот мне нужен вот такой подзапрос:
необходимо вывести платежи, за тот день, когда был сделан наибольший платеж
SELECT * FROM payments WHERE paydate = (SELECT paydate FROM payments WHERE amount =
(SELECT MAX(amount) FROM payments));
Вот таблица из которой получаю подзапросы
mysql> select * from payments;
+——+———————+———-+———-+
| num | paydate | receiver | amount |
+——+———————+———-+———-+
| 1 | 2016-10-30 15:00:00 | 1 | 35000.50 |
| 2 | 2017-11-30 15:25:00 | 2 | 25000.50 |
| 3 | 2015-11-30 10:25:00 | 2 | 15000.50 |
| 4 | 2014-11-30 11:25:00 | 3 | 10000.50 |
| 5 | 2014-11-30 11:25:00 | 3 | 10000.50 |
| 6 | 2017-10-03 12:03:11 | 4 | 1000.50 |
| 7 | 2017-10-03 12:03:11 | 2 | 500.50 |
| 8 | 2017-10-03 12:03:11 | 3 | 17000.50 |
| 9 | 2017-10-03 12:03:11 | 1 | 100.50 |
| 10 | 2017-10-03 12:03:11 | 4 | 15000.50 |
| 11 | 2017-10-03 12:03:11 | 4 | 35000.50 |
| 12 | 2017-10-03 12:03:11 | 2 | 45000.50 |
| 13 | 2017-10-03 12:03:11 | 3 | 55000.50 |
| 14 | 2017-10-03 12:03:11 | 4 | 85000.50 |
| 15 | 2017-10-03 12:03:11 | 4 | 85000.50 |
| 16 | 2017-10-03 12:03:11 | 4 | 85000.50 |
+——+———————+———-+———-+
16 rows in set (0.00 sec)
Вот я разложил запросы по мере их выполнения
mysql> select max(amount) from payments;
+————-+
| max(amount) |
+————-+
| 85000.50 |
+————-+
1 row in set (0.02 sec)
mysql> SELECT paydate FROM payments WHERE amount = (SELECT MAX(amount) FROM payments);
+———————+
| paydate |
+———————+
| 2017-10-03 12:03:11 |
| 2017-10-03 12:03:11 |
| 2017-10-03 12:03:11 |
+———————+
3 rows in set (0.00 sec)
mysql> SELECT * FROM payments WHERE paydate = (SELECT paydate FROM payments WHERE amount =
-> (SELECT MAX(amount) FROM payments));
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> SELECT * FROM payments WHERE paydate = (SELECT paydate FROM payments WHERE amount = (SELECT MAX(amount) FROM payments));
ERROR 1242 (21000): Subquery returns more than 1 row
mysql> select * from payments;
Если вложенный запрос содержит больше одной строки с максимальным значением , то появляется ошибка.
Почему ?
Как это обойти ?
Неактивен
#2 05.10.2017 16:36:57
- klow
- Старожил
- Зарегистрирован: 06.12.2014
- Сообщений: 411
Re: Вывод больше чем одну строку во вложенном подзапросе
SELECT * FROM payments WHERE date(paydate) = (SELECT date(paydate) FROM payments WHERE amount =
(SELECT MAX(amount) FROM payments) LIMIT 1);
Неактивен
#3 05.10.2017 16:38:04
- klow
- Старожил
- Зарегистрирован: 06.12.2014
- Сообщений: 411
Re: Вывод больше чем одну строку во вложенном подзапросе
SELECT * FROM payments WHERE date(paydate) = (SELECT date(paydate) FROM payments ORDER BY amount desc LIMIT 1);
Отредактированно klow (05.10.2017 16:38:30)
Неактивен
#4 05.10.2017 16:39:57
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Re: Вывод больше чем одну строку во вложенном подзапросе
Простите поторопился.
Ответ на ошибку вроде нашел….
…если у вас два или больше платежа с такой величиной, второй подзапрос вернет несколько дат и сравнение с помощью знака равно, которое здесь использовано, не может работать, т.к. оператор равно не умеет сравнивать одно значение с несколькими.
mysql> SELECT * FROM payments WHERE paydate IN (SELECT paydate FROM payments WHERE amount =
-> (SELECT MAX(amount) FROM payments));
+——+———————+———-+———-+
| num | paydate | receiver | amount |
+——+———————+———-+———-+
| 6 | 2017-10-03 12:03:11 | 4 | 1000.50 |
| 7 | 2017-10-03 12:03:11 | 2 | 500.50 |
| 8 | 2017-10-03 12:03:11 | 3 | 17000.50 |
| 9 | 2017-10-03 12:03:11 | 1 | 100.50 |
| 10 | 2017-10-03 12:03:11 | 4 | 15000.50 |
| 11 | 2017-10-03 12:03:11 | 4 | 35000.50 |
| 12 | 2017-10-03 12:03:11 | 2 | 45000.50 |
| 13 | 2017-10-03 12:03:11 | 3 | 55000.50 |
| 14 | 2017-10-03 12:03:11 | 4 | 85000.50 |
| 15 | 2017-10-03 12:03:11 | 4 | 85000.50 |
| 16 | 2017-10-03 12:03:11 | 4 | 85000.50 |
+——+———————+———-+———-+
11 rows in set (0.00 sec)
Но как видите запрос корректно не отработал, подскажите почему
Неактивен
#5 05.10.2017 16:41:17
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Re: Вывод больше чем одну строку во вложенном подзапросе
Важно отработать использование оператора
IN
Неактивен
#6 05.10.2017 16:42:13
- klow
- Старожил
- Зарегистрирован: 06.12.2014
- Сообщений: 411
Re: Вывод больше чем одну строку во вложенном подзапросе
А смысл? Вам нужно чтобы выводило все дни с максимальным платежом?
Отредактированно klow (05.10.2017 16:48:17)
Неактивен
#7 05.10.2017 16:42:49
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Re: Вывод больше чем одну строку во вложенном подзапросе
А если максимальные платежи будут различаться по времени за один и тот же день ???
Неактивен
#8 05.10.2017 16:43:19
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Re: Вывод больше чем одну строку во вложенном подзапросе
Таково задание урока…
Неактивен
#9 05.10.2017 16:47:31
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Re: Вывод больше чем одну строку во вложенном подзапросе
klow написал:
SELECT * FROM payments WHERE date(paydate) = (SELECT date(paydate) FROM payments ORDER BY amount desc LIMIT 1);
mysql> SELECT * FROM payments WHERE date(paydate) = (SELECT date(paydate) FROM payments ORDER BY amount desc LIMIT 1);
+——+———————+———-+———-+
| num | paydate | receiver | amount |
+——+———————+———-+———-+
| 6 | 2017-10-03 12:03:11 | 4 | 1000.50 |
| 7 | 2017-10-03 12:03:11 | 2 | 500.50 |
| 8 | 2017-10-03 12:03:11 | 3 | 17000.50 |
| 9 | 2017-10-03 12:03:11 | 1 | 100.50 |
| 10 | 2017-10-03 12:03:11 | 4 | 15000.50 |
| 11 | 2017-10-03 12:03:11 | 4 | 35000.50 |
| 12 | 2017-10-03 12:03:11 | 2 | 45000.50 |
| 13 | 2017-10-03 12:03:11 | 3 | 55000.50 |
| 14 | 2017-10-03 12:03:11 | 4 | 85000.50 |
| 15 | 2017-10-03 12:03:11 | 4 | 85000.50 |
| 16 | 2017-10-03 12:03:11 | 4 | 85000.50 |
+——+———————+———-+———-+
11 rows in set (0.00 sec)
Задание не выполняется, нужно вывести день в который был совершен наибольший платеж
То есть вывести только строки с наибольшим платежем
Отредактированно jron (05.10.2017 16:49:11)
Неактивен
#10 05.10.2017 16:50:06
- klow
- Старожил
- Зарегистрирован: 06.12.2014
- Сообщений: 411
Re: Вывод больше чем одну строку во вложенном подзапросе
Я не понял задачу. Можете привести четкую и полную формулировку задачи?
Неактивен
#11 05.10.2017 17:00:23
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Re: Вывод больше чем одну строку во вложенном подзапросе
оператор IN используется совместно с вложенными запросами.
пример.
Нам необходимо вывести платежи, за тот день, когда был сделан наибольший платеж в вашей базе., и показать строки только с наибольшим платежом
этот запрос … не сработает если будет несколько одинаковых строк
SELECT * FROM payments WHERE paydate = (SELECT paydate FROM payments WHERE amount =
(SELECT MAX(amount) FROM payments));
вот так выведутся дни когда сделаны наибольшие платежи
SELECT * FROM payments WHERE paydate IN (SELECT paydate FROM payments WHERE amount =
(SELECT MAX(amount) FROM payments));
Неактивен
#12 05.10.2017 17:02:35
- klow
- Старожил
- Зарегистрирован: 06.12.2014
- Сообщений: 411
Re: Вывод больше чем одну строку во вложенном подзапросе
А если есть несколько больших одинаковых платежей за разные дни?
Отредактированно klow (05.10.2017 17:03:54)
Неактивен
#13 05.10.2017 17:07:53
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Re: Вывод больше чем одну строку во вложенном подзапросе
у меня такой таблицы нет и в задаче так не сказано, но то же интересно решение
но очень интересует вопрос как показать только строки с наибольшими платежами
SELECT * FROM payments WHERE paydate IN (SELECT paydate FROM payments WHERE amount =
(SELECT MAX(amount) FROM payments));
Неактивен
#14 05.10.2017 17:17:14
- klow
- Старожил
- Зарегистрирован: 06.12.2014
- Сообщений: 411
Re: Вывод больше чем одну строку во вложенном подзапросе
SELECT
p.*
FROM payment p
JOIN (SELECT date(paydate) paydate, amount FROM payments ORDER BY amount DESC LIMIT 1) d ON date(p.paydate) = d.paydate AND p.amount = d.amount;
Неактивен
#15 05.10.2017 17:31:49
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Re: Вывод больше чем одну строку во вложенном подзапросе
mysql> SELECT p.*
-> FROM payments p
-> JOIN (SELECT date(paydate) paydate, amount FROM payments ORDER BY amount DESC LIMIT 1) d
-> ON date(p.paydate) = d.paydate AND p.amount = d.amount;
+——+———————+———-+———-+
| num | paydate | receiver | amount |
+——+———————+———-+———-+
| 14 | 2017-10-03 12:03:11 | 4 | 85000.50 |
| 15 | 2017-10-03 12:03:11 | 4 | 85000.50 |
| 16 | 2017-10-03 12:03:11 | 4 | 85000.50 |
+——+———————+———-+———-+
3 rows in set (0.00 sec)
Неактивен
#16 05.10.2017 17:45:24
- jron
- Завсегдатай
- Зарегистрирован: 03.10.2017
- Сообщений: 44
Re: Вывод больше чем одну строку во вложенном подзапросе
поясните пожалуйста значение ON — в данном контексте
и date(p.paydate)….
Вы использовали в запросе описания типа данных ?
Отредактированно jron (05.10.2017 17:46:16)
Неактивен
#17 05.10.2017 20:01:11
- klow
- Старожил
- Зарегистрирован: 06.12.2014
- Сообщений: 411
Re: Вывод больше чем одну строку во вложенном подзапросе
Функция date() преобразует значение DateTime в Date.
Неактивен