1 Subquery returns more than 1 row - MySQL

  1. Господа, Hola! Есть запросик у меня и все вроде бы было хорошо и он отрабатывал, пока я не влепил подзапросы и он не выдал ошибочку: 1 Subquery returns more than 1 row...My


textual

Код к задаче: «1 Subquery returns more than 1 row - MySQL»

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.500 из 5



Похожие ответы
  1. Здравствуйте. Помогите корректно записать запрос типаMy