代码之家 › 专栏 › 技术社区 › Raj

displaying data from multiple tables

tsql sql-server-2005

Raj · 技术社区 · 14 年前

i have 3 table (SalesLog, Breakages, SalesReturn), I want to display data from these table like

ProductName          SalesQty         BreakQty        ReturnQty
ABCD                   1000              10              20

销售日志表

CREATE TABLE [dbo].[SalesLog](
[SalesID] [int] IDENTITY(1,1) NOT NULL,
[MemoNo] [int] NULL,
[ProductCode] [int] NULL,
[Quantity] [int] NULL,
[Price] [int] NULL,
[pGroup] [int] NULL,
[pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[pSize] [int] NULL,
[BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SalesLog] PRIMARY KEY CLUSTERED 
(
[SalesID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

破损表

CREATE TABLE [dbo].[Breakages](
[breakId] [int] IDENTITY(1,1) NOT NULL,
[MemoNo] [int] NULL,
[SalesmanID] [int] NULL,
[ProductCode] [int] NULL,
[pName] [nvarchar](30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Quantity] [int] NULL,
[pGroup] [nvarchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddedOn] [datetime] NULL,
CONSTRAINT [PK_Breakages_1] PRIMARY KEY CLUSTERED 
(
[breakId] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

SalesReturn Table

CREATE TABLE [dbo].[SalesReturn](
[srID] [int] IDENTITY(1,1) NOT NULL,
[ProductCode] [int] NULL,
[Quantity] [int] NULL,
[pGroup] [int] NULL,
[MemoNo] [int] NULL,
[SalesmanID] [int] NULL,
[Price] [int] NULL,
[BillDate] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[AddedOn] [datetime] NULL,
CONSTRAINT [PK_SalesReturn] PRIMARY KEY CLUSTERED 
(
[srID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Any help will be appreciated..

2 回复 | 直到 14 年前

shereifhawary 14 年前

Select 
      pname as ProductName ,
      ProductCode as pc
      Quantity as SalesQty ,
            (select
                    Quantity 
             from Breakages
             where Breakages.ProductCode = pc
             ) as BreakQty ,
             (select 
                    Quantity 
             from SalesReturn
             where ProductCode = pc) as ReturnQty 
      from SalesLog;

Jamie Ide 14 年前

SELECT
    sl.pName, 
    SUM(sl.Quantity) as TotalQty, 
    SUM(br.Quantity) as TotalBreakageQty, 
    SUM(sr.Quantity) as TotalReturnQty
FROM 
    SalesLog sl
    LEFT JOIN Breakages br ON sl.ProductCode = br.ProductCode
    LEFT JOIN SalesReturn sr ON sl.ProductCode = sr.ProductCode
GROUP BY 
    sl.pName

This will give you total quantities grouped by product name.

As AakashM correctly points out, using inner joins will return only records that have a breakage and a return, so I have changed them to left joins.