SqlServer 表关联查询

[_小黑_]

CREATE TABLE [dbo].[T_OrderProcess](
	[OrderID] [nvarchar](20) NOT NULL,
	[PMID] [nvarchar](20) NOT NULL,
	[ProductionDate] [datetime] NULL,
	[Remark] [nvarchar](200) NULL,
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[OpOrder] [int] NULL,
	[OpTime] [nvarchar](50) NULL,
	[IsState] [nvarchar](10) NOT NULL,
	[LaboratoryNO] [nvarchar](50) NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[T_OrderProcess] ADD  CONSTRAINT [T_OrderProcess_Default]  DEFAULT ((1)) FOR [IsState]
GO
--数据
insert into T_OrderProcess 
select 
'RO1607010001',  '算了算了', '2015-10-16 00:00:00.000','',   '1'  ,         '21333354.0'    

CREATE TABLE [dbo].[T_OrderMaterial](
	[OrderID] [nvarchar](20) NOT NULL,
	[MaterialNumber] [nvarchar](50) NOT NULL,
	[BatchNumber] [nvarchar](50) NULL,
	[MaterialNum] [decimal](18, 0) NULL,
	[Remark] [nvarchar](200) NULL,
	[Unit] [nvarchar](50) NULL,
	[Id] [int] IDENTITY(1,1) NOT NULL,
	[IsState] [nvarchar](10) NOT NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[T_OrderMaterial] ADD  CONSTRAINT [T_OrderMaterial_Default]  DEFAULT ((1)) FOR [IsState]
GO

-- 数据
insert into T_OrderMaterial 
select 
'RO1607010001'   ,      '2345'  ,   '123'  ,   111   union all
select                                                                                                                                                                                                 
'RO1607010001',         '3456' ,    '122'  ,   55   

[中国风]

LEFT JOIN--FULL JOIN 记录数以两表多的记录数为准

[唐诗三百首]

LZ脚本执行报错: Msg 213, Level 16, State 1, Line 20 Column name or number of supplied values does not match table definition.

[中国风]

指定显示的列

SELECT * FROM (SELECT *,MIN(ID)OVER(PARTITION BY OrderID) AS RN FROM T_OrderMaterial) AS a LEFT JOIN T_OrderProcess AS b ON a.OrderID=b.OrderID AND a.RN=a.ID

[_小黑_]

引用 2 楼 roy_88 的回复:

指定显示的列

SELECT * FROM (SELECT *,MIN(ID)OVER(PARTITION BY OrderID) AS RN FROM T_OrderMaterial) AS a LEFT JOIN T_OrderProcess AS b ON a.OrderID=b.OrderID AND a.RN=1

两个表 的数据 不一定 是那个表的数据 多

引用 1 楼 ap0405140 的回复:

LZ脚本执行报错: Msg 213, Level 16, State 1, Line 20 Column name or number of supplied values does not match table definition.

--数据
--insert into T_OrderProcess 
--select 
--'RO1607010001',  '算了算了', '2015-10-16 00:00:00.000','',   1  ,         '21333354.0'   ,'1','' 
 
 
-- 数据
--insert into T_OrderMaterial 
--select 
--'RO1607010001'   ,      '2345'  ,   '123'  ,   111 ,null ,null,'1' union all
--select                                                                                                                                                                                                 
--'RO1607010001',         '3456' ,    '122'  ,   55   ,null,null,'1'

试一下 这个 谢谢了

[_小黑_]

引用 2 楼 roy_88 的回复:

指定显示的列

SELECT * FROM (SELECT *,MIN(ID)OVER(PARTITION BY OrderID) AS RN FROM T_OrderMaterial) AS a LEFT JOIN T_OrderProcess AS b ON a.OrderID=b.OrderID AND a.RN=a.ID

如果 是 T_OrderMaterial 这个表的数据 大于 T_OrderProcess 这个表的数据 的话 这样写 可以 但是 反过来 就 不行了

[中国风]

SELECT  *
FROM    ( SELECT    * ,
                    ROW_NUMBER() OVER ( PARTITION BY OrderID ORDER BY ID ) AS RN
          FROM      T_OrderMaterial
        ) AS a
        LEFT JOIN ( SELECT  * ,
                            ROW_NUMBER() OVER ( PARTITION BY OrderID ORDER BY ID ) AS RN
                    FROM    T_OrderProcess
                  ) AS b ON a.OrderID = b.OrderID
                            AND a.RN = b.RN;

这样用

[中国风]

#5方法，通过行记录顺序去匹配