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.7k12 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;I am executing this query:
SELECT
voterfile_county.Name,
voterfile_precienct.PREC_ID,
voterfile_precienct.Name,
COUNT((SELECT voterfile_voter.ID
FROM voterfile_voter
JOIN voterfile_household
WHERE voterfile_voter.House_ID = voterfile_household.ID
AND voterfile_household.Precnum = voterfile_precienct.PREC_ID)) AS Voters
FROM voterfile_precienct JOIN voterfile_county
WHERE voterfile_precienct.County_ID = voterfile_County.ID;
I am trying to make it return something like this:
County_Name Prec_ID Prec_Name Voters(Count of # of voters in that precienct)
However, I am getting the error:
#1242 — Subquery returns more than 1 row.
I have tried placing the COUNT statement in the subquery but I get an invalid syntax error.
asked Apr 22, 2009 at 16:59
gsueagle2008gsueagle2008
4,5639 gold badges36 silver badges46 bronze badges
2
If you get error:error no 1242 Subquery returns more than one row, try to put ANY before your subquery. Eg:
This query return error:
SELECT * FROM t1 WHERE column1 = (SELECT column1 FROM t2);
This is good query:
SELECT * FROM t1 WHERE column1 = ANY (SELECT column1 FROM t2);
2
You can try it without the subquery, with a simple group by:
SELECT voterfile_county.Name,
voterfile_precienct.PREC_ID,
voterfile_precienct.Name,
count(voterfile_voter.ID)
FROM voterfile_county
JOIN voterfile_precienct
ON voterfile_precienct.County_ID = voterfile_County.ID
JOIN voterfile_household
ON voterfile_household.Precnum = voterfile_precienct.PREC_ID
JOIN voterfile_voter
ON voterfile_voter.House_ID = voterfile_household.ID
GROUP BY voterfile_county.Name,
voterfile_precienct.PREC_ID,
voterfile_precienct.Name
When you use GROUP BY, any column that you are not grouping on must have an aggregate clause (f.e. SUM or COUNT.) So in this case you have to group on county name, precienct.id and precient.name.
answered Apr 22, 2009 at 18:35
3
Try this
SELECT
voterfile_county.Name, voterfile_precienct.PREC_ID,
voterfile_precienct.Name,
(SELECT COUNT(voterfile_voter.ID)
FROM voterfile_voter JOIN voterfile_household
WHERE voterfile_voter.House_ID = voterfile_household.ID
AND voterfile_household.Precnum = voterfile_precienct.PREC_ID) as Voters
FROM voterfile_precienct JOIN voterfile_county
ON voterfile_precienct.County_ID = voterfile_County.ID
answered Apr 22, 2009 at 17:03
2
See the below example and modify your query accordingly.
select COUNT(ResultTPLAlias.id) from
(select id from Table_name where .... ) ResultTPLAlias;
answered May 18, 2011 at 7:55
ArunjithArunjith
1312 silver badges10 bronze badges
So I’ve just started learning MySQL with a few exercises and I’m currently stuck at these 2 problems. Since this is technically an exercise I’d really appreciate hints instead of full solutions. Here are the 2 tables first:
CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
LINE_TOTAL float(8)
);
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99','14.99');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99','9.98');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95','38.95');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95','39.95');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99','74.95');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99','14.97');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95','19.90');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87','70.44');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99','20.97');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92','109.92');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99','256.99');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99','29.98');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99','4.99');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87','29.35');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95','359.85');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95','9.95');
CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE date,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-
nozzle','2015-11-03','8','5','109.99','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2015-12-
13','32','15','14.99','0.05',NULL);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2015-11-
13','18','12','17.49','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2016-01-
15','15','8','39.95','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2016-01-
15','23','5','43.99','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','2015-12-
30','8','5','109.92','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','2015-12-
24','6','5','99.87','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','2016-
01-20','12','5','38.95','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2016-01-
12','23','10','9.95','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','2016-01-
2','8','5','14.40','0.05',NULL);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2015-
12-15','43','20','4.99','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2016-02-
17','11','5','256.99','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2016-02-
27','188','75','5.87','0','24004');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, ''25','2016-03-
01','172','75','6.99','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2016-02-
14','237','100','8.45','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5"
mesh','2016-01-27','18','5','119.95','0.1','25595');
PKs are line.INV_NUMBER, line.LINE_NUMBER, product.P_CODE and FKs are line.P_CODE and line.V_CODE
So the first question is to list all product sales that are greater than the average units sold for that product and add a correlated in-line sub-query to list the average units sold per product.
If I understood correctly this means that I need to sum the line.LINE_UNITS group by P_CODE then compare it to the average units sold but I keep getting the same error that says sub query returns more than 1 row. Here’s my code:
select P_CODE,
LINE_UNITS,
( SELECT AVG(line.LINE_UNITS) ) as ‘Unit_Average’
from line
where
( SELECT sum(line.LINE_UNITS)
from line
group by P_CODE ) >
( SELECT AVG(line.LINE_UNITS) );
I think I need to do a join instead and I’d really appreciate some hints.
For the second question I am supposed to list the difference between each product’s prices and the average product price. I managed to type up a few lines but I couldn’t get it to execute for all rows of the product table. I’m thinking of using over() but im having quite a lot of syntax errors. Here’s my code:
select P_CODE, P_PRICE,
( SELECT avg(product.P_PRICE) ) as 'Average',
if(product.P_PRICE <=
( SELECT avg(product.P_PRICE) ),
( SELECT avg(product.P_PRICE) ) - product.P_PRICE,
product.P_PRICE - ( SELECT avg(product.P_PRICE) )
) as 'Difference'
from product;
Thanks guys!
So I’ve just started learning MySQL with a few exercises and I’m currently stuck at these 2 problems. Since this is technically an exercise I’d really appreciate hints instead of full solutions. Here are the 2 tables first:
CREATE TABLE LINE (
INV_NUMBER int,
LINE_NUMBER int,
P_CODE varchar(10),
LINE_UNITS float(8),
LINE_PRICE float(8),
LINE_TOTAL float(8)
);
INSERT INTO LINE VALUES('1001','1','13-Q2/P2','1','14.99','14.99');
INSERT INTO LINE VALUES('1001','2','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1002','1','54778-2T','2','4.99','9.98');
INSERT INTO LINE VALUES('1003','1','2238/QPD','1','38.95','38.95');
INSERT INTO LINE VALUES('1003','2','1546-QQ2','1','39.95','39.95');
INSERT INTO LINE VALUES('1003','3','13-Q2/P2','5','14.99','74.95');
INSERT INTO LINE VALUES('1004','1','54778-2T','3','4.99','14.97');
INSERT INTO LINE VALUES('1004','2','23109-HB','2','9.95','19.90');
INSERT INTO LINE VALUES('1005','1','PVC23DRT','12','5.87','70.44');
INSERT INTO LINE VALUES('1006','1','SM-18277','3','6.99','20.97');
INSERT INTO LINE VALUES('1006','2','2232/QTY','1','109.92','109.92');
INSERT INTO LINE VALUES('1006','3','23109-HB','1','9.95','9.95');
INSERT INTO LINE VALUES('1006','4','89-WRE-Q','1','256.99','256.99');
INSERT INTO LINE VALUES('1007','1','13-Q2/P2','2','14.99','29.98');
INSERT INTO LINE VALUES('1007','2','54778-2T','1','4.99','4.99');
INSERT INTO LINE VALUES('1008','1','PVC23DRT','5','5.87','29.35');
INSERT INTO LINE VALUES('1008','2','WR3/TT3','3','119.95','359.85');
INSERT INTO LINE VALUES('1008','3','23109-HB','1','9.95','9.95');
CREATE TABLE PRODUCT (
P_CODE varchar(10),
P_DESCRIPT varchar(35),
P_INDATE date,
P_QOH int,
P_MIN int,
P_PRICE float(8),
P_DISCOUNT float(8),
V_CODE int
);
INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-
nozzle','2015-11-03','8','5','109.99','0','25595');
INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade','2015-12-
13','32','15','14.99','0.05',NULL);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade','2015-11-
13','18','12','17.49','0','21344');
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50','2016-01-
15','15','8','39.95','0','23119');
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50','2016-01-
15','23','5','43.99','0','23119');
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade','2015-12-
30','8','5','109.92','0.05','24288');
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade','2015-12-
24','6','5','99.87','0.05','24288');
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.','2016-
01-20','12','5','38.95','0.05','25595');
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer','2016-01-
12','23','10','9.95','0.1','21225');
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.','2016-01-
2','8','5','14.40','0.05',NULL);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine','2015-
12-15','43','20','4.99','0','21344');
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.','2016-02-
17','11','5','256.99','0.05','24288');
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft','2016-02-
27','188','75','5.87','0','24004');
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, ''25','2016-03-
01','172','75','6.99','0','21225');
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50','2016-02-
14','237','100','8.45','0','21231');
INSERT INTO PRODUCT VALUES('WR3/TT3','Steel matting, 4''x8''x1/6", .5"
mesh','2016-01-27','18','5','119.95','0.1','25595');
PKs are line.INV_NUMBER, line.LINE_NUMBER, product.P_CODE and FKs are line.P_CODE and line.V_CODE
So the first question is to list all product sales that are greater than the average units sold for that product and add a correlated in-line sub-query to list the average units sold per product.
If I understood correctly this means that I need to sum the line.LINE_UNITS group by P_CODE then compare it to the average units sold but I keep getting the same error that says sub query returns more than 1 row. Here’s my code:
select P_CODE,
LINE_UNITS,
( SELECT AVG(line.LINE_UNITS) ) as ‘Unit_Average’
from line
where
( SELECT sum(line.LINE_UNITS)
from line
group by P_CODE ) >
( SELECT AVG(line.LINE_UNITS) );
I think I need to do a join instead and I’d really appreciate some hints.
For the second question I am supposed to list the difference between each product’s prices and the average product price. I managed to type up a few lines but I couldn’t get it to execute for all rows of the product table. I’m thinking of using over() but im having quite a lot of syntax errors. Here’s my code:
select P_CODE, P_PRICE,
( SELECT avg(product.P_PRICE) ) as 'Average',
if(product.P_PRICE <=
( SELECT avg(product.P_PRICE) ),
( SELECT avg(product.P_PRICE) ) - product.P_PRICE,
product.P_PRICE - ( SELECT avg(product.P_PRICE) )
) as 'Difference'
from product;
Thanks guys!
322 Глава 7
SELECT name FROM catalogs
WHERE (SELECT id_catalog FROM products
WHERE id_product = 5) < id_catalog;
|
+——————— |
+ |
|
| name |
| |
|
+——————— |
+ |
|
| Видеоадаптеры |
| |
|
| Жесткие диски |
| |
|
| |
Оперативная память | |
|
+——————— |
+ |
В предыдущем разделе рассмотрены запросы, где вложенный запрос возвращает единственное значение. Если вложенный запрос возвращает несколько строк, СУБД MySQL генерирует ошибку 1242: «Вложенный запрос возвращает более одной строки» (листинг 7.35).
Листинг 7.35. Вложенный запрос возвращает более одной строки
SELECT name FROM catalogs
WHERE id_catalog = (SELECT id_catalog FROM products);
ERROR 1242: Subquery returns more than 1 row
Для того чтобы предотвратить такую ошибку, там, где во вложенном запросе ожидается лишь одно значение, разумно использовать конструкцию LIMIT 1 (лис-
тинг 7.36).
Листинг 7.36. Использование конструкции LIMIT
SELECT name FROM catalogs
WHERE id_catalog = (SELECT id_catalog FROM products LIMIT 1);
7.6.1. Ключевое слово IN
Для того чтобы выбрать строки за таблицы catalogs, у которых первичный ключ id_catalog совпадает с одним из значений, возвращаемых вложенным запросом, следует воспользоваться конструкцией IN (листинг 7.36).
|
Сложные вопросы MySQL |
323 |
Листинг 7.36. Использование конструкции IN
SELECT name FROM catalogs
WHERE id_catalog IN (SELECT id_catalog FROM products
GROUP BY id_catalog)
|
ORDER BY name; |
|
|
+——————— |
+ |
|
| name |
| |
|
+——————— |
+ |
|
| Видеоадаптеры |
| |
|
| Жесткие диски |
| |
|
| Материнские платы |
| |
| Оперативная память |
|
| Процессоры |
| |
|
+——————— |
+ |
Запрос, представленный в листинге 7.36, аналогичен запросу, показанному в листинге 7.37.
Листинг 7.37. Использование конструкции IN совместно со списком скалярных величин
SELECT name FROM catalogs
WHERE id_catalog IN (1,2,3,4,5)
|
ORDER BY name; |
|
|
+——————— |
+ |
|
| name |
| |
|
+——————— |
+ |
|
| Видеоадаптеры |
| |
|
| Жесткие диски |
| |
|
| Материнские платы |
| |
| Оперативная память |
|
| Процессоры |
| |
|
+——————— |
+ |
Для того чтобы возвратить строки, которые отсутствуют в результирующей таблице, возвращаемой вложенным запросом, следует воспользоваться оператором NOT IN, представленным в листинге 7.38.
Листинг 7.38. Использование оператора NOT IN
SELECT name FROM catalogs
WHERE id_catalog NOT IN (SELECT DISTINCT id_catalog
FROM products WHERE id_catalog < 3)
ORDER BY name;
|
324 |
Глава 7 |
|
+——————— |
+ |
|
| name |
| |
|
+——————— |
+ |
|
| Видеоадаптеры |
| |
|
| Жесткие диски |
| |
|
| Оперативная память | |
|
|
+——————— |
+ |
7.6.2. Ключевое слово ANY
Конструкция IN позволяет осуществить поиск величины в списке и выражает логику оператора «равно» (=). Однако на месте оператора = в этом запросе может стоять другой оператор сравнения (листинг 7.39).
Листинг 7.39. Вложенный запрос возвращает более одной строки
SELECT name FROM catalogs
WHERE id_catalog > (SELECT id_catalog FROM products);
ERROR 1242: Subquery returns more than 1 row
Данный запрос также завершается неудачей, но сформулировать его с использованием конструкции IN уже не получится. К счастью, язык запросов SQL обладает средствами решения подобных задач. Для этого применяется ключевое слово ANY (листинг 7.40).
Листинг 7.40. Использование ключевого слова ANY
SELECT id_catalog, name FROM catalogs
WHERE id_catalog > ANY (SELECT id_catalog FROM products);
|
+———— |
+——————— |
+ |
||
|
| id_catalog | |
name |
| |
||
|
+———— |
+——————— |
+ |
||
|
| |
2 |
| |
Материнские платы |
| |
|
| |
3 |
| |
Видеоадаптеры |
| |
|
| |
4 |
| |
Жесткие диски |
| |
|
| |
5 |
| |
Оперативная память | |
|
|
+———— |
+——————— |
+ |
Ключевое слово ANY применяется для сравнения значений с использованием одного из шести операторов сравнения (=, <>, <, <=, >, >=). Проверяемое значение id_catalog поочередно сравнивается с каждым элементом, который возвращает вложенный запрос. Если хотя бы одно из сравнений возвращает 1 (истина), строка выводится запросом. В листинге 7.40 происходит сравнение значений первичного ключа id_catalog (1, 2, 3, 4, 5), которые присутствуют в таблице catalogs, со значениями
поля id_catalog (1, 2, 3, 4, 5) из таблицы products. Значение id_catalog = 1 не удов-
летворяет ни одному условию:
|
Сложные вопросы MySQL |
325 |
|||
|
1 |
> 1 — 0 (ложь) |
|||
|
1 |
> 2 |
— 0 |
(ложь) |
|
|
1 |
> 3 |
— 0 |
(ложь) |
|
|
1 |
> 4 |
— 0 |
(ложь) |
|
|
1 |
> 5 |
— 0 |
(ложь) |
Поэтому в результаты эта строка не попадает, в то же время все остальные цифры удовлетворяют хотя бы одному условию и попадают в результирующую таблицу:
|
3 |
> 1 — |
1 |
(истина) |
||
|
3 |
> 2 |
— |
1 |
(истина) |
|
|
3 |
> 3 |
— |
0 |
(ложь) |
|
|
3 |
> |
4 |
— |
0 |
(ложь) |
|
3 |
> |
5 |
— |
0 |
(ложь) |
То есть запрос вида
«WHERE X > ANY (SELECT Y…)»
можно интерпретировать как «где X больше хотя бы одного выбранного Y«, а запрос вида
«WHERE X < ANY (SELECT Y…)»
следует читать «где X меньше хотя бы одного Y…».
7.6.3. Ключевое слово ALL
Вместо ключевого слова ANY может быть использовано ключевое слово ALL, которое точно так же применяется совместно с одним из шести операторов сравнения (=, <>, <, <=, >, >=). В этом случае проверяемое значение также поочередно сравнивается с каждым элементом, который возвращает вложенный запрос, но строка возвращается только тогда, когда все сравнения дают 1 (истина).
ЗАМЕЧАНИЕ
Если в выражениях с ключевым словом ANY используется логика ИЛИ, т. е. достаточно, чтобы срабатывало хотя бы одно из многих условий, то в случае ALL используется логика
И— должны срабатывать все условия.
Влистинге 7.41 представлен запрос, возвращающий все товарные позиции из таблицы products базы данных shop, цена которых превышает среднюю цену каждого из элементов каталога.
Листинг 7.41. Использование ключевого слова ALL
SELECT * FROM catalogs
WHERE id_catalog >= ALL (SELECT id_catalog FROM products
GROUP BY id_catalog);
|
+———— |
+——————— |
+ |
|
|
| id_catalog | |
name |
| |
|
|
+———— |
+——————— |
+ |
|
|
| |
5 | |
Оперативная память | |
|
|
+———— |
+——————— |
+ |
Соседние файлы в предмете [НЕСОРТИРОВАННОЕ]
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #
- #