求一条SQL语句

Question

第一个查询语句是
SELECT  [id]
      ,[CreatedOn]
      ,[Supplier_Code]
      ,[DocNo]
      ,[CreatedBy]
      ,[Supplier_ShortName]
      ,[TotalNetMnyAC]
      ,[TotalTaxMnyAC]
      ,[TotalMnyAC]
      ,[TaxRate]
      ,[Status]
      ,[WindageRatio]
      ,[PriceListName]
      ,[TotalTaxMnyFC]
      ,[TotalTaxMnyTC]
      ,[TotalNetMnyFC]
      ,[TotalNetMnyTC]
      ,[TotalMnyFC]
      ,[TotalMnyTC]
      ,[SupConfirmDate]
      ,[ApprovedBy]
      ,[ApprovedOn]
      FROM table1
      WHERE DATEDIFF(day,table1.CreatedOn,SYSDATETIME())<7
      ORDER BY createdon DESC第二个查询语句是：
SELECT [DocLineNo]
      ,[CreatedOn]
      ,[PurchaseOrder]
      ,[ItemInfo_ItemCode]
      ,[ItemInfo_ItemName]
      ,[ReqQtyTU]
      ,[PurQtyTU]
      ,[SupplierConfirmQtyTU]
      ,[FinallyPriceTC]
      ,[NetMnyAC]
      ,[TotalTaxAC]
      ,[TotalMnyAC]
      ,[Status] FROM table2 WHERE table2.PurchaseOrder=table1.[id]
因为第一个表里的ID对应这多个第二个表里的PurchaseOrder。
现在问题是怎样把第二个后面的查询数据附加到第一个表的查询数据里去
可以用多行显示也可以一行显示
100分送上求解答
最好附上讲解。
呵呵。我是SQL新手。SQLselect行业数据

Answer 1

select * from table1,table2
where 
DATEDIFF(day,table1.CreatedOn,SYSDATETIME())<7
and  table2.PurchaseOrder=table1.[id]
order by ORDER BY createdon DESC直接关联起来就好了吧...还是说我把你的需求理解错了？

Answer 2

可以用多行显示也可以一行显示？table2 怎样显示，字符串类型可用xml合并，数字时可用sum/max

Answer 3

楼主说的主从表关联吧，SELECT a.*,b.*
 FROM  Table1 a inner join table2 b ON  b.PurchaseOrder=a.[id]
WHERE  DATEDIFF(day,a.CreatedOn,SYSDATETIME())<7

Answer 4

有没有办法让结果呈现这个样子呢。
[CreatedOn],[Supplier_Code],...,[ApprovedOn],[DocLineNo]...,[Status]
20130601xxx,xxxxxxxx,       ...,xxx xxxx    ,xxxxx1     ...,xx
                                            ,xxxxx2     ...,xx
                                            ,xxxxx3     ...,xx
                                            ,xxxxx4     ...,xx
                                            ,xxxxx5     ...,xx
                                            ,xxxxx6     ...,xx
20130531xxx,xxxxxxxx,       ...,xxx xxxx    ,xxxxx1     ...,xx
                                            ,xxxxx2     ...,xx
                                            ,xxxxx3     ...,xx
                                            ,xxxxx4     ...,xx

Answer 5

关注一下。
一般这种显示，都是用表格控件去控制的，sql的还不知道怎么弄。

Answer 6

SELECT t1.[id]
      ,t1.[CreatedOn] 'CreatedOn1'
      ,t1.[Supplier_Code]
      ,t1.[DocNo]
      ,t1.[CreatedBy]
      ,t1.[Supplier_ShortName]
      ,t1.[TotalNetMnyAC]
      ,t1.[TotalTaxMnyAC]
      ,t1.[TotalMnyAC]
      ,t1.[TaxRate]
      ,t1.[Status] 'Status1'
      ,t1.[WindageRatio]
      ,t1.[PriceListName]
      ,t1.[TotalTaxMnyFC]
      ,t1.[TotalTaxMnyTC]
      ,t1.[TotalNetMnyFC]
      ,t1.[TotalNetMnyTC]
      ,t1.[TotalMnyFC]
      ,t1.[TotalMnyTC]
      ,t1.[SupConfirmDate]
      ,t1.[ApprovedBy]
      ,t1.[ApprovedOn]
      ,t2.[DocLineNo]
      ,t2.[CreatedOn] 'CreatedOn2'
      ,t2.[PurchaseOrder]
      ,t2.[ItemInfo_ItemCode]
      ,t2.[ItemInfo_ItemName]
      ,t2.[ReqQtyTU]
      ,t2.[PurQtyTU]
      ,t2.[SupplierConfirmQtyTU]
      ,t2.[FinallyPriceTC]
      ,t2.[NetMnyAC]
      ,t2.[TotalTaxAC]
      ,t2.[TotalMnyAC]
      ,t2.[Status] 'Status2'
FROM table1 t1
cross apply(
 SELECT *
 FROM table2 
 WHERE PurchaseOrder=t1.[id]) t2
WHERE DATEDIFF(day,t1.CreatedOn,SYSDATETIME())<7
ORDER BY t1.createdon DESC