在SQL Server 2008 r2选择查询中自动复制行 [英] Automatically duplicate rows in SQL server 2008 r2 select query

问题描述

自动复制sql server 2008中的行r2选择查询

i有两个表名1）tbl_Stock_Journal 2）tbl_Stock_Journal_Details



i运行一个选择查询与where条件

i在table1中有5个数据，在表2中有3个数据，当我运行查询结果时显示15行查询显示日期明智

i想要最大数据显示，如果同一日期的其他表没有数据，则显示空白或空



请帮助我.....



我的尝试：

 ALter proc P_GetStockJournalRPT  - ' all'，'all'，'2018-07-10'，'2018-07-10'
 
 
 @SJItmID varchar（100），
 @SJDItmID varchar（100 ），
 @Sdate日期，
 @EDate日期
 
如果
如果@ SJItmID ='ALL'且@ SJDItmID ='ALL'
开始
选择不同的SJ.SJID，i1.itemnm作为itemnmsou，SJ.SJqty ，SJ.SJPCS，SJ.SJRate，SJ.SJamt，SJ.SJleft，SJ.SJDate，SJ.nrr，
 i2.itemnm as itemnmDES，SJD.SJDqty，SJD.SJDPCS，SJD.SJDRate，SJD.SJDamt ，SJD.SJDstock，SJD.SJDdate 
 
来自tbl_Stock_Journal SJ 
 
 INNER JOIN tbl_item AS i1 ON i1.itemid = SJ.SJItmID 
内部JOIN tbl_Stock_Journal_Details AS SJD ON SJ.SJID = SJD.SJID 
内部JOIN tbl_item AS i2 ON i2.itemid = SJd.SJdItmID 
 
其中CAST（sj.SJDate AS DATE）在@Sdate和@EDate $ b $之间b  - '2018-07-10'和'2018-07-10'之间的CAST（sj.SJDate AS DATE）
 end 

解决方案

如果您使用 LEFT OUTER JOIN 而不是 INNER JOIN

我认为您所描述的结果将是结果code>你现在有的。



看看这些CodeProject文章进一步解释

SQL连接的可视化表示 [ ^ ]

SQL Server中的连接类型 [ ^ ]



没有太多改变......

 选择  distinct  SJ.SJID，i1.itemnm  as  itemnmsou，SJ.SJqty，SJ.SJPCS，SJ.SJRate，SJ.SJamt，SJ.SJleft，SJ.SJDate，SJ.nrr，
 i2.itemnm  as  itemnmDES，SJD.SJDqty，SJD.SJDPCS，SJD.SJDRate，SJD.SJDamt，SJD.SJDstock，SJD.SJDdate 
 
 来自 tbl_Stock_Journal SJ 
 
   LEFT   OUTER    JOIN  tbl_item  AS  i1  ON  i1.itemid = SJ.SJItmID 
   LEFT   OUTER    JOIN  tbl_Stock_Journal_Details  AS  SJD  ON  SJ.SJID = SJD.SJID 
   LEFT   OUTER    JOIN  tbl_item  AS  i2  ON  i2.itemid = SJd.SJdItmID 
 
 其中 CAST（sj.SJDate  AS   DATE ） BETWEEN   @ Sdate  < span class =code-keyword> AND   @ EDate  




automatically duplicate rows in sql server 2008 r2 select query
i have two table name 1) tbl_Stock_Journal 2) tbl_Stock_Journal_Details

i run a select query with where condition
i have 5 data in table1 and in table 2 have 3 data when i run the query result is showing 15 row the query show on date wise
i want to maximum data show and if other table in same date have no number of data then show blank or null

please help me.....

What I have tried:



ALter proc P_GetStockJournalRPT --  'all', 'all','2018-07-10' , '2018-07-10'     

   
@SJItmID varchar (100)      ,          
@SJDItmID varchar (100)      , 
@Sdate date,                                  
@EDate Date  
   
As
if @SJItmID='ALL' and @SJDItmID='ALL'
begin
select distinct SJ.SJID, i1.itemnm as itemnmsou, SJ.SJqty, SJ.SJPCS, SJ.SJRate, SJ.SJamt, SJ.SJleft, SJ.SJDate, SJ.nrr,
i2.itemnm as itemnmDES, SJD.SJDqty, SJD.SJDPCS, SJD.SJDRate, SJD.SJDamt, SJD.SJDstock, SJD.SJDdate

from tbl_Stock_Journal SJ

INNER JOIN tbl_item AS i1   ON i1.itemid  =SJ.SJItmID
 inner JOIN tbl_Stock_Journal_Details AS SJD   ON   SJ.SJID = SJD.SJID                             
inner JOIN tbl_item AS i2   ON i2.itemid  =SJd.SJdItmID

where CAST(sj.SJDate AS DATE) BETWEEN @Sdate AND @EDate         
--where CAST(sj.SJDate AS DATE) BETWEEN '2018-07-10' AND '2018-07-10'        
end

解决方案

I think what you are describing would be the results if you used a LEFT OUTER JOIN instead of the INNER JOINs that you currently have.

Have a look at these CodeProject articles to explain further
Visual Representation of SQL Joins[^]
Types of Join in SQL Server[^]

Not a lot of change to make...

select distinct SJ.SJID, i1.itemnm as itemnmsou, SJ.SJqty, SJ.SJPCS, SJ.SJRate, SJ.SJamt, SJ.SJleft, SJ.SJDate, SJ.nrr,
i2.itemnm as itemnmDES, SJD.SJDqty, SJD.SJDPCS, SJD.SJDRate, SJD.SJDamt, SJD.SJDstock, SJD.SJDdate

from tbl_Stock_Journal SJ

LEFT OUTER JOIN tbl_item AS i1   ON i1.itemid  =SJ.SJItmID
LEFT OUTER JOIN tbl_Stock_Journal_Details AS SJD   ON   SJ.SJID = SJD.SJID                             
LEFT OUTER JOIN tbl_item AS i2   ON i2.itemid  =SJd.SJdItmID

where CAST(sj.SJDate AS DATE) BETWEEN @Sdate AND @EDate         




这篇关于在SQL Server 2008 r2选择查询中自动复制行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！