database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 7228 게시물 읽기
No. 7228
쿼리 결과값을 다시 계산하고 싶어요
작성자
김우성(babokws)
작성일
2020-12-27 20:26
조회수
496

-- WHERE P.barcode = '8801063310486' 이부분을

-- WHERE P.comcode = '1005' 로 변경시 아래와 같이 에러가 뜹니다 Row 가 하나 이상이어서 그런듯한데 어떻게해야하나요?

-- 고수님들 부탁드립니다


-- 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%)

Go

USE [Test_DB]

Go

CREATE TABLE [dbo].[parts](

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

    [descr] [varchar](30) NULL,

    [comcode] [varchar](5) NULL

)

Go

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' )

Go


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

)

Go

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

)

Go

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

)

Go

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' )

Go


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

)

Go

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

)

Go

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

)

Go

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' )

Go

CREATE TABLE [dbo].[bojung_1710](

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

    [barcode] [varchar](17) NULL,

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

    [day1] [varchar](10) NOT NULL

)

Go

CREATE TABLE [dbo].[bojung_1711](

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

    [barcode] [varchar](17) NULL,

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

    [day1] [varchar](10) NOT NULL

)

Go

CREATE TABLE [dbo].[bojung_1712](

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

    [barcode] [varchar](17) NULL,

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

    [day1] [varchar](10) NOT NULL

)

Go

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' )

Go

USE [Test_DB]

Go




-- 쿼리 실행


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


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

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

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

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.
제목
작성자
작성일
조회
7231MSSQL PIVOT 질문 입니다. [3]
우롱
2021-01-15
450
7230행의 데이타를 원하는 만큼 나누어 여러개의 열로 표현할려 합니다. [6]
둥둥이
2021-01-05
445
7229MSSQL 프로시져 템프테이블 문의 [1]
물어보자
2020-12-30
402
7228쿼리 결과값을 다시 계산하고 싶어요 [5]
김우성
2020-12-27
496
7227여러 레코드들을 하나의 컬럼으로 합치고 싶어요 [2]
바보온달
2020-11-26
487
7226row count 조회시 실제 count와 결과값이 다릅니다. [2]
레인버그
2020-09-29
606
7225alwayson구성시 계정 관리
초보
2020-07-27
655
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2021 DSN, All rights reserved.
작업시간: 0.011초, 이곳 서비스는
	PostgreSQL v13.1으로 자료를 관리합니다