在sql server 2008中使用三个表报告？ [英] report using three tables in sql server 2008?

问题描述

 创建 表＃table1（名称 varchar （ 50 ），ChicksQuantity  int ，Chicksplaceddate  datetime ，Billablechicks  int ，freechicks  int ，死亡率 int ，Chickrate  float ，TotalAmount  float ，CashRecieved  float ）
  insert  进入＃table1 
 
 选择 A.agentname  as 名称，A.Chicksqty   ChicksQuantity，A.Chicksplaceddate   Chicksplaceddate，A.billablechicks  as  Billablechicks，
 A.freechicks，A.mortality，A.Chickrate，A.Totalamount，A.Cashrec，
 F.farmername  as 名称，F.Chicksqty  as  Chicksquantity，F.chicksplaceddate，F.billablechicks，F.freechicks，
 F.mortality，F.Chickrate，F.Totalamount，F .Cashrec，I.farmername  as 名称，I.noofchicks  as  chicksquantity，I.mortality，I.Chicksplaceddate 来自 k_Master_Agentchicksales A 
 内部  join  k_Master_IntegrationChickSale我  A.hatchid = I.hatchid 
  inner   join  k_Master_Farmerchicksales F   F.hatchid = A.hatchid 其中​​ A. updatedon   '  2013-04-01 00：00：00.000' 和 '  2014-03-31 00：00：00.000' 
 
 
 选择 * 来自＃table1 

i只想使用三张桌子报告9列....

i know create table total columns and insertion columns必须相同，但我如何区分plz帮助我.....

解决方案

你可以使用三个选择语句单个Insert Query不正确。对于单个插入，单个选择可以充当输入。所以写下这样的三个语句，如果你有多个记录，那么这个单词将会完成工作。

  INSERT   INTO ＃table1 
  SELECT  A.agentname  as 名称，
 A.Chicksqty  as  ChicksQuantity，
 A.Chicksplaceddate  as  Chicksplaceddate，
 A.billablechicks  as  Billablechicks，
 A.freechicks，
 A.mortality ，
 A.Chickrate，
 A.Totalamount，
 A.Cashrec 
 
  FROM  k_Master_Agentchicksales A 
  INNER   JOIN  k_Master_IntegrationChickSale我  A.hatchid = I.hatchid 
  INNER   JOIN  k_Master_F armerchicksales F   F.hatchid = A.hatchid 
  WHERE  A.updatedon >  '  2013-04-01 00：00：00.000' 和 '  2014-03-31 00:00： 00.000'  

create Table #table1(Name varchar(50),ChicksQuantity int,Chicksplaceddate datetime,Billablechicks int,freechicks int,Mortality int,Chickrate float,TotalAmount float,CashRecieved float)
insert into #table1

select A.agentname as Name ,A.Chicksqty as ChicksQuantity,A.Chicksplaceddate as Chicksplaceddate, A.billablechicks as Billablechicks,
 A.freechicks,A.mortality,A.Chickrate,A.Totalamount,A.Cashrec,
 F.farmername as Name,F.Chicksqty as Chicksquantity,F.chicksplaceddate,F.billablechicks,F.freechicks,
 F.mortality,F.Chickrate,F.Totalamount,F.Cashrec,I.farmername as Name,I.noofchicks as chicksquantity,I.mortality,I.Chicksplaceddate from  k_Master_Agentchicksales A
inner join k_Master_IntegrationChickSale I on A.hatchid=I.hatchid
inner join k_Master_Farmerchicksales F on F.hatchid=A.hatchid where A.updatedon between '2013-04-01 00:00:00.000' and '2014-03-31 00:00:00.000'


select * from #table1

i want only 9 columns report using three tables....
i know create table total columns and inserting columns must be same but how can i differentiate plz help me.....

解决方案

You ware using Three select statements under the single Insert Query which is not correct. For a single insert single select can act as input. So write three statements like this this single will do the work if you have multiple record.

INSERT INTO #table1 
SELECT A.agentname as Name, 
	A.Chicksqty as ChicksQuantity, 
	A.Chicksplaceddate as Chicksplaceddate, 
	A.billablechicks as Billablechicks,
	A.freechicks,
	A.mortality,
	A.Chickrate,
	A.Totalamount,
	A.Cashrec

FROM k_Master_Agentchicksales A
    INNER JOIN k_Master_IntegrationChickSale I on A.hatchid=I.hatchid
    INNER JOIN k_Master_Farmerchicksales F on F.hatchid=A.hatchid
WHERE A.updatedon between '2013-04-01 00:00:00.000' and '2014-03-31 00:00:00.000'

这篇关于在sql server 2008中使用三个表报告？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！