1 Subquery returns more than 1 row - MySQL

Узнай цену своей работы

Формулировка задачи:

Господа, Hola! Есть запросик у меня и все вроде бы было хорошо и он отрабатывал, пока я не влепил подзапросы и он не выдал ошибочку: 1 Subquery returns more than 1 row... 
SELECT 
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos, 
  SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog, 
  ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL, 
  ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = 35 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date =
  (
  SELECT deal_date FROM deal WHERE deal_date BETWEEN '01.10.2017' AND '30.10.2017'
  ) 
 
UNION ALL 
 
SELECT 
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos, 
  SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog, 
  ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL, 
  ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = 36 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date = 
  (
  SELECT deal_date FROM deal WHERE deal_date BETWEEN '01.10.2017' AND '30.10.2017'
  )
Пожалуйста о магистры знаний, укажите верный путь... В чем может быть проблема?

Решение задачи: «1 Subquery returns more than 1 row»

textual
Листинг программы 
SET @vardt1 = '01.10.2017';
SET @vardt2 = '01.11.2017';
SET @vardt3 = '2017-10-01';
SET @vardt4 = '2017-10-30';
SET @vardt5 = '2017-11-01';
SET @vardt6 = '2017-11-30';
SET @varid1 = '34';
SET @varid2 = '35';
SET @varid3 = '36';
SET @varid4 = '71';
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid1 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid2 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid3 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt1) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid4 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt3 AND @vardt4
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid1 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid2 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid3 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6
 
UNION
 
SELECT
  Deal_StfCrdRcd AS MerchantID, stfcrd.stfcrd_fio AS MerchNm, postemp.postemp_nm AS Pos
  , SUM(deal_col) AS QSold, SUM(deal_col * prod_prc) AS Itog
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm)) AS Comm, zrp_okl AS OKL
  , ROUND(SUM((deal_col * prod_prc) * zrp_comm) + zrp_okl) AS Salary
  , MONTHNAME(@vardt2) AS Mnh
FROM
  deal, stfcrd, postemp, prod, zrp
WHERE
  Deal_StfCrdRcd = @varid4 AND stfcrd.StfCrd_Rcd = deal.Deal_StfCrdRcd
  AND postemp.PostEmp_Rcd = stfcrd.StfCrd_PostEmpRcd
  AND prod.Prod_Rcd = deal.Deal_ProdRcd
  AND zrp.zrpstf_rcd = deal.Deal_StfCrdRcd
  AND deal_date BETWEEN @vardt5 AND @vardt6

ИИ поможет Вам:

  • решить любую задачу по программированию
  • объяснить код
  • расставить комментарии в коде
  • и т.д
Попробуйте бесплатно

Оцени полезность:

10   голосов , оценка 3.5 из 5