데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

-- Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the --subquery is used as an expression.

-- 테스트_DB 생성

CREATE DATABASE [Test_DB] ON PRIMARY

( NAME = N'Test_DB', FILENAME = N'C:\Test_DB.MDF' , SIZE = 3072KB , FILEGROWTH = 1024KB )

LOG ON

( NAME = N'Test_DB_log', FILENAME = N'C:\Test_DB_Log.LDF' , SIZE = 1024KB , FILEGROWTH = 10%)

USE [Test_DB]

CREATE TABLE [dbo].[parts](

[barcode] [varchar](17) NOT NULL,

[descr] [varchar](30) NULL,

[comcode] [varchar](5) NULL

)

INSERT INTO parts ( barcode, descr, comcode ) VALUES ( '8801063310486', '테스트01', '1005' )

INSERT INTO parts ( barcode, descr, comcode ) VALUES ( '8809111694533', '테스트02', '1005' )

INSERT INTO parts ( barcode, descr, comcode ) VALUES ( '8801075014532', '테스트03', '1005' )

CREATE TABLE [dbo].[outd_1710](

[icomcode] [varchar](5) NULL,

[barcode] [varchar](17) NOT NULL,

[mitemcount] [decimal](12, 2) NOT NULL,

[bitemcount] [decimal](12, 2) NOT NULL,

[day1] [varchar](10) NOT NULL

)

CREATE TABLE [dbo].[outd_1711](

[icomcode] [varchar](5) NULL,

[barcode] [varchar](17) NOT NULL,

[mitemcount] [decimal](12, 2) NOT NULL,

[bitemcount] [decimal](12, 2) NOT NULL,

[day1] [varchar](10) NOT NULL

)

CREATE TABLE [dbo].[outd_1712](

[icomcode] [varchar](5) NULL,

[barcode] [varchar](17) NOT NULL,

[mitemcount] [decimal](12, 2) NOT NULL,

[bitemcount] [decimal](12, 2) NOT NULL,

[day1] [varchar](10) NOT NULL

)

CREATE TABLE [dbo].[outd_yymm](

[icomcode] [varchar](5) NULL,

[barcode] [varchar](17) NOT NULL,

[mitemcount] [decimal](12, 2) NOT NULL,

[bitemcount] [decimal](12, 2) NOT NULL,

[day1] [varchar](10) NOT NULL

)

INSERT INTO outd_1710 ( icomcode, barcode, mitemcount, bitemcount, day1 ) VALUES ( '1005', '8801063310486', '10', '1', '2017-10-01' )

INSERT INTO outd_1710 ( icomcode, barcode, mitemcount, bitemcount, day1 ) VALUES ( '1005', '8809111694533', '20', '2', '2017-10-02' )

INSERT INTO outd_1710 ( icomcode, barcode, mitemcount, bitemcount, day1 ) VALUES ( '1005', '8801075014532', '30', '3', '2017-10-03' )

INSERT INTO outd_1711 ( icomcode, barcode, mitemcount, bitemcount, day1 ) VALUES ( '1005', '8801063310486', '10', '1', '2017-11-01' )

INSERT INTO outd_1711 ( icomcode, barcode, mitemcount, bitemcount, day1 ) VALUES ( '1005', '8809111694533', '20', '2', '2017-11-02' )

INSERT INTO outd_1711 ( icomcode, barcode, mitemcount, bitemcount, day1 ) VALUES ( '1005', '8801075014532', '30', '3', '2017-11-03' )

INSERT INTO outd_1712 ( icomcode, barcode, mitemcount, bitemcount, day1 ) VALUES ( '1005', '8801063310486', '10', '1', '2017-12-01' )

INSERT INTO outd_1712 ( icomcode, barcode, mitemcount, bitemcount, day1 ) VALUES ( '1005', '8809111694533', '20', '2', '2017-12-02' )

INSERT INTO outd_1712 ( icomcode, barcode, mitemcount, bitemcount, day1 ) VALUES ( '1005', '8801075014532', '30', '3', '2017-12-03' )

CREATE TABLE [dbo].[ipgod_1710](

[barcode] [varchar](17) NULL,

[comcode] [varchar](5) NULL,

[iitemcount] [decimal](15, 2) NULL,

[bitemcount] [decimal](15, 2) NULL,

[day1] [varchar](10) NOT NULL

)

CREATE TABLE [dbo].[ipgod_1711](

[barcode] [varchar](17) NULL,

[comcode] [varchar](5) NULL,

[iitemcount] [decimal](15, 2) NULL,

[bitemcount] [decimal](15, 2) NULL,

[day1] [varchar](10) NOT NULL

)

CREATE TABLE [dbo].[ipgod_1712](

[barcode] [varchar](17) NULL,

[comcode] [varchar](5) NULL,

[iitemcount] [decimal](15, 2) NULL,

[bitemcount] [decimal](15, 2) NULL,

[day1] [varchar](10) NOT NULL

)

INSERT INTO ipgod_1710 ( barcode, comcode, iitemcount, bitemcount, day1 ) VALUES ( '8801063310486', '1005', '5', '1', '2017-10-01' )

INSERT INTO ipgod_1710 ( barcode, comcode, iitemcount, bitemcount, day1 ) VALUES ( '8809111694533', '1005', '30', '2', '2017-10-02' )

INSERT INTO ipgod_1710 ( barcode, comcode, iitemcount, bitemcount, day1 ) VALUES ( '8801075014532', '1005', '50', '3', '2017-10-03' )

INSERT INTO ipgod_1711 ( barcode, comcode, iitemcount, bitemcount, day1 ) VALUES ( '8801063310486', '1005', '10', '1', '2017-11-01' )

INSERT INTO ipgod_1711 ( barcode, comcode, iitemcount, bitemcount, day1 ) VALUES ( '8809111694533', '1005', '120', '2', '2017-11-02' )

INSERT INTO ipgod_1711 ( barcode, comcode, iitemcount, bitemcount, day1 ) VALUES ( '8801075014532', '1005', '330', '3', '2017-11-03' )

INSERT INTO ipgod_1712 ( barcode, comcode, iitemcount, bitemcount, day1 ) VALUES ( '8801063310486', '1005', '110', '1', '2017-12-01' )

INSERT INTO ipgod_1712 ( barcode, comcode, iitemcount, bitemcount, day1 ) VALUES ( '8809111694533', '1005', '220', '2', '2017-12-02' )

INSERT INTO ipgod_1712 ( barcode, comcode, iitemcount, bitemcount, day1 ) VALUES ( '8801075014532', '1005', '30', '3', '2017-12-03' )

CREATE TABLE [dbo].[bojung_1710](

[comcode] [varchar](5) NOT NULL,

[barcode] [varchar](17) NULL,

[itemcount] [decimal](10, 2) NULL,

[day1] [varchar](10) NOT NULL

)

CREATE TABLE [dbo].[bojung_1711](

[comcode] [varchar](5) NOT NULL,

[barcode] [varchar](17) NULL,

[itemcount] [decimal](10, 2) NULL,

[day1] [varchar](10) NOT NULL

)

CREATE TABLE [dbo].[bojung_1712](

[comcode] [varchar](5) NOT NULL,

[barcode] [varchar](17) NULL,

[itemcount] [decimal](10, 2) NULL,

[day1] [varchar](10) NOT NULL

)

INSERT INTO bojung_1710 ( comcode, barcode, itemcount, day1 ) VALUES ( '1005', '8801063310486', '1', '2017-10-01' )

INSERT INTO bojung_1710 ( comcode, barcode, itemcount, day1 ) VALUES ( '1005', '8809111694533', '2', '2017-10-02' )

INSERT INTO bojung_1710 ( comcode, barcode, itemcount, day1 ) VALUES ( '1005', '8801075014532', '3', '2017-10-03' )

INSERT INTO bojung_1711 ( comcode, barcode, itemcount, day1 ) VALUES ( '1005', '8801063310486', '1', '2017-11-01' )

INSERT INTO bojung_1711 ( comcode, barcode, itemcount, day1 ) VALUES ( '1005', '8809111694533', '2', '2017-11-02' )

INSERT INTO bojung_1711 ( comcode, barcode, itemcount, day1 ) VALUES ( '1005', '8801075014532', '3', '2017-11-03' )

INSERT INTO bojung_1712 ( comcode, barcode, itemcount, day1 ) VALUES ( '1005', '8801063310486', '1', '2017-12-01' )

INSERT INTO bojung_1712 ( comcode, barcode, itemcount, day1 ) VALUES ( '1005', '8809111694533', '2', '2017-12-02' )

INSERT INTO bojung_1712 ( comcode, barcode, itemcount, day1 ) VALUES ( '1005', '8801075014532', '3', '2017-12-03' )

USE [Test_DB]

-- 쿼리 실행

-- 입고-판매-보정 = 총합계

-----------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------- 입고 -------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------

SELECT

p.barcode, p.descr, '입고-판매-보정' AS "구분", 'FULL' AS "구분2",

ISNULL(i1710.cnt,0) + ISNULL(i1711.cnt, 0) + ISNULL(i1712.cnt, 0) +

ISNULL(yy.cnt,0)

-----------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------- 판매 -------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------

(

SELECT

ISNULL( o1710.cnt, 0) + ISNULL( o1711.cnt, 0) + ISNULL( o1712.cnt, 0) +

ISNULL( yy.cnt, 0)

FROM

(SELECT barcode, SUM(mitemcount - bitemcount) cnt FROM outd_1710 GROUP BY barcode) as o1710

FULL OUTER JOIN ( SELECT * FROM parts ) p ON o1710.barcode = p.barcode

FULL OUTER JOIN ( SELECT barcode, SUM(mitemcount - bitemcount) cnt FROM outd_1711 GROUP BY barcode ) o1711

ON p.barcode = o1711.barcode

FULL OUTER JOIN ( SELECT barcode, SUM(mitemcount - bitemcount) cnt FROM outd_1712

WHERE day1 BETWEEN '2017-10-01' AND '2017-12-31' GROUP BY barcode ) o1712

ON p.barcode = o1712.barcode

FULL OUTER JOIN ( SELECT barcode, SUM(ISNULL(mitemcount,0) - ISNULL(bitemcount,0)) cnt FROM outd_yymm GROUP BY barcode ) yy

ON p.barcode = yy.barcode

-- WHERE P.barcode = '8801063310486'

WHERE P.comcode = '1005'

)

-----------------------------------------------------------------------------------------------------------------------------------------------

---------------------------------------- 보정 ------------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------------------------------------------------------

(

SELECT

ISNULL(b1710.cnt,0) + ISNULL(b1711.cnt, 0) + ISNULL(b1712.cnt, 0) +

ISNULL(yy.cnt,0)

FROM

(SELECT barcode, itemcount AS cnt FROM bojung_1710 GROUP BY barcode, itemcount) as b1710

FULL OUTER JOIN ( SELECT * FROM parts ) p ON b1710.barcode = p.barcode

FULL OUTER JOIN ( SELECT barcode, itemcount AS cnt FROM bojung_1711 GROUP BY barcode, itemcount ) b1711

ON p.barcode = b1711.barcode

FULL OUTER JOIN ( SELECT barcode, itemcount AS cnt FROM bojung_1712

WHERE day1 BETWEEN '2017-10-01' AND '2017-12-31' GROUP BY barcode, itemcount ) b1712

ON p.barcode = b1712.barcode

LEFT OUTER JOIN ( SELECT '0' yymm, 0 cnt ) yy

ON p.barcode = yy.yymm

-- WHERE P.barcode = '8801063310486'

WHERE P.comcode = '1005'

) AS "총합계"

FROM

(SELECT barcode, SUM(iitemcount - bitemcount) cnt FROM ipgod_1710 GROUP BY barcode) i1710

FULL OUTER JOIN ( SELECT * FROM parts ) p ON i1710.barcode = p.barcode

FULL OUTER JOIN ( SELECT barcode, SUM(iitemcount - bitemcount) cnt FROM ipgod_1711 GROUP BY barcode ) i1711

ON p.barcode = i1711.barcode

FULL OUTER JOIN ( SELECT barcode, SUM(iitemcount - bitemcount) cnt FROM ipgod_1712

WHERE day1 BETWEEN '2017-10-01' AND '2017-12-31' GROUP BY barcode ) i1712

ON p.barcode = i1712.barcode

LEFT OUTER JOIN ( SELECT '0' yymm, 0 cnt ) yy

ON p.barcode = yy.yymm

-- WHERE P.barcode = '8801063310486'

WHERE P.comcode = '1005'

이 글에 대한 댓글이 총 5건 있습니다.

서버쿼리를 칼럼에 썼을 때 2rows이상이라면

먼저 결과를 얻은 값에서 1Row를 출력하다 서버쿼리를 만나 2Row를 보여주려고 하니 나는 에러입니다.

어떻게 해야 출력하면 되는지요?

예를 들어 part.comcode = 1005이미 세건인데 그걸 서버쿼리로 보여줘야 할 때는

test01, 02, 03중 뭘 보여줘야 하는지요?

.님이 2020-12-29 12:01에 작성한 댓글입니다. Edit Ｘ

SELECT p.barcode
     , p.descr
     , (SELECT ISNULL(SUM(iitemcount - bitemcount), 0) FROM  ipgod_1710
         WHERE  comcode = p.comcode AND barcode = p.barcode)
     + (SELECT ISNULL(SUM(iitemcount - bitemcount), 0) FROM  ipgod_1711
         WHERE  comcode = p.comcode AND barcode = p.barcode)
     + (SELECT ISNULL(SUM(iitemcount - bitemcount), 0) FROM  ipgod_1712
         WHERE  comcode = p.comcode AND barcode = p.barcode)
     - (SELECT ISNULL(SUM(mitemcount - bitemcount), 0) FROM   outd_1710
         WHERE icomcode = p.comcode AND barcode = p.barcode)
     - (SELECT ISNULL(SUM(mitemcount - bitemcount), 0) FROM   outd_1711
         WHERE icomcode = p.comcode AND barcode = p.barcode)
     - (SELECT ISNULL(SUM(mitemcount - bitemcount), 0) FROM   outd_1712
         WHERE icomcode = p.comcode AND barcode = p.barcode)
     - (SELECT ISNULL(SUM(mitemcount - bitemcount), 0) FROM   outd_yymm
         WHERE icomcode = p.comcode AND barcode = p.barcode)
     + (SELECT ISNULL(SUM( itemcount             ), 0) FROM bojung_1710
         WHERE  comcode = p.comcode AND barcode = p.barcode)
     + (SELECT ISNULL(SUM( itemcount             ), 0) FROM bojung_1711
         WHERE  comcode = p.comcode AND barcode = p.barcode)
     + (SELECT ISNULL(SUM( itemcount             ), 0) FROM bojung_1712
         WHERE  comcode = p.comcode AND barcode = p.barcode)
    AS 총합계
  FROM parts p
 WHERE p.comcode = '1005'
;

마농(manon94)님이 2020-12-29 14:58에 작성한 댓글입니다.

바코드	입고		바코드	판매		바코드	보정		바코드	합계
8801517120081	1								8801517120081	1
8801517130080	2		8801517130080	5					8801517130080	-3
8801517150132	3		8801517150132	8		8801517150132	3		8801517150132	-2
8801517160131	4	-	8801517160131	4	+	8801517160131		=	8801517160131	0
8801517170130	5					8801517170130	3		8801517170130	8
8801517250023	6		8801517250023	9		8801517250023	3		8801517250023	0
8801517280013	7		8801517280013	3					8801517280013	4
8801517300018	8		8801517300018	8		8801517300018	8		8801517300018	8
8801517310130	9		8801517310130	2		8801517310130	1		8801517310130	8

입고 - 판매 + 보정 = 합계를 전부 표시하고 싶어요 기준이있어야 된다면 판매 입니다

김우성(babokws)님이 2020-12-30 15:26에 작성한 댓글입니다.
이 댓글은 2020-12-30 15:28에 마지막으로 수정되었습니다.

SELECT barcode
     , descr
     , i
     , o
     , b
     , i - o + b AS tot
  FROM (SELECT p.barcode
             , p.descr
               -- 입고 --
             , (SELECT ISNULL(SUM(iitemcount - bitemcount), 0) FROM  ipgod_1710
                 WHERE  comcode = p.comcode AND barcode = p.barcode)
             + (SELECT ISNULL(SUM(iitemcount - bitemcount), 0) FROM  ipgod_1711
                 WHERE  comcode = p.comcode AND barcode = p.barcode)
             + (SELECT ISNULL(SUM(iitemcount - bitemcount), 0) FROM  ipgod_1712
                 WHERE  comcode = p.comcode AND barcode = p.barcode)
               AS i
               -- 판매 --
             , (SELECT ISNULL(SUM(mitemcount - bitemcount), 0) FROM   outd_1710
                 WHERE icomcode = p.comcode AND barcode = p.barcode)
             + (SELECT ISNULL(SUM(mitemcount - bitemcount), 0) FROM   outd_1711
                 WHERE icomcode = p.comcode AND barcode = p.barcode)
             + (SELECT ISNULL(SUM(mitemcount - bitemcount), 0) FROM   outd_1712
                 WHERE icomcode = p.comcode AND barcode = p.barcode)
             + (SELECT ISNULL(SUM(mitemcount - bitemcount), 0) FROM   outd_yymm
                 WHERE icomcode = p.comcode AND barcode = p.barcode)
               AS o
               -- 보정 --
             , (SELECT ISNULL(SUM( itemcount             ), 0) FROM bojung_1710
                 WHERE  comcode = p.comcode AND barcode = p.barcode)
             + (SELECT ISNULL(SUM( itemcount             ), 0) FROM bojung_1711
                 WHERE  comcode = p.comcode AND barcode = p.barcode)
             + (SELECT ISNULL(SUM( itemcount             ), 0) FROM bojung_1712
                 WHERE  comcode = p.comcode AND barcode = p.barcode)
               AS b
          FROM parts p
         WHERE p.comcode = '1005'
        ) a
;

마농(manon94)님이 2020-12-30 16:00에 작성한 댓글입니다.
이 댓글은 2020-12-30 16:11에 마지막으로 수정되었습니다.

마농님께 정말 감사드립니다 새해 복 많이 받으세요 ^^

김우성(babokws)님이 2021-01-01 11:31에 작성한 댓글입니다.

[Top]

No.	제목	작성자	작성일	조회
7231	MSSQL PIVOT 질문 입니다. [3]	우롱	2021-01-15	2087
7230	행의 데이타를 원하는 만큼 나누어 여러개의 열로 표현할려 합니다. [6]	둥둥이	2021-01-05	1695
7229	MSSQL 프로시져 템프테이블 문의 [1]	물어보자	2020-12-30	1566
7228	쿼리 결과값을 다시 계산하고 싶어요 [5]	김우성	2020-12-27	1781
7227	여러 레코드들을 하나의 컬럼으로 합치고 싶어요 [2]	바보온달	2020-11-26	1590
7226	row count 조회시 실제 count와 결과값이 다릅니다. [2]	레인버그	2020-09-29	1763
7225	alwayson구성시 계정 관리 [1]	초보	2020-07-27	2018

작업시간: 0.022초, 이곳 서비스는
PostgreSQL v16.4로 자료를 관리합니다