如何将多个表与常用列名组合在一起？ [英] How to combine more than one Table with out common column names?

问题描述

;  WITH  K_RT_MasterRetailStoresTable（CommonId，Branch） AS  
（
  SELECT  ROW_NUMBER（） OVER （ ORDER   BY 分支） AS  CommonId，Branch 
  FROM  K_RT_MasterRetailStores 
）
，K_BR_AddBranchTable（CommonId，branch） AS  
（
  SELECT  ROW_NUMBER（） OVER （ ORDER   BY 分支） AS  CommonId，分支
  FROM  K_BR_AddBranch 
）
，K_HM_BranchTable（CommonID​​，branch） as  
（
 选择 ROW_NUMBER（） OVER （ ORDER   BY  branch） as  CommonId，branch 
  FROM  K_HM_Branch 
）
， k_Master_BranchesTable（CommonID​​，branch） as  
（
  SELECT  ROW_NUMBER（） OVER （ ORDER   BY  BRANCH） AS  COMMONID，BRANCH 
  FROM  k_Master_Branches 
）
  SELECT  MRS.BRANCH，AB.BRANCH，B.BRANCH，MB.BRANCH 
  FROM  K_RT_MasterRetailStoresTable MRS 
  LEFT   OUTER   JOIN  K_BR_AddBranchTable AB  ON  MRS.COMMONID = AB.COMMONID 
  LEFT   OUTER   JOIN  K_HM_BranchTable B  ON  AB.COMMONID = B.COMMONID 
  LEFT   OUTER   JOIN  k_Master_BranchesTable MB  ON  B.COMMONID = MB.COMMONID 

现在它显示4列，4个表分支显示..现在我想在单列ALL分支中显示.. 。我所做的 ？请帮帮我？

解决方案

试试这样..

  SELECT  BRANCH1  AS  BRANCH  FROM  BRANCH_TABLE 
  UNION   ALL  
  SELECT  BRANCH2  AS  BRANCH  FROM  BRANCH_TABLE 
  UNION   ALL  
  SELECT  BRANCH3  AS  BRANCH  FROM  BRANCH_TABLE 
  UNION   ALL  
  SELECT  BRANCH4  AS  BRANCH  FROM  BRANCH_TABLE; 

< pre lang = c＃> ;; WITH K_RT_MasterRetailStoresTable（CommonId，Branch） AS
（
SELECT ROW_NUMBER（） OVER （ ORDER BY 分支） AS CommonId，Branch
FROM K_RT_MasterRetailStores
）
，K_BR_AddBranchTable（CommonId，branch） AS
（
SELECT ROW_NUMBER（） OVER （ ORDER BY 分支） AS CommonId，分支
FROM K_BR_AddBranch
）
，K_HM_BranchTable （CommonID​​，branch） as
（
SELECT ROW_NUMBER（） OVER （ ORDER BY 分支） as CommonId， branch
FROM K_HM_Branch
）
，k_Master_BranchesTable（CommonID​​，branch） as
（
SELECT ROW_NUMBER（） OVER （ ORDER BY BRANCH） AS COMMONID，BRANCH
< span class =code-keyword> FROM k_Master_Branches
）

SELECT 不同 BRANCH FROM K_RT_MasterRetailStoresTable
UNION ALL
SELECT distinct BRANCH FROM K_BR_AddBranchTable
UNION ALL
SELECT 不同 BRANCH FROM K_HM_BranchTable
UNION ALL
SELECT distinct BRANCH FROM k_Master_BranchesTable;

;WITH K_RT_MasterRetailStoresTable (CommonId,Branch) AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY Branch) AS CommonId,Branch
    FROM K_RT_MasterRetailStores
)
,K_BR_AddBranchTable (CommonId, branch) AS
(
    SELECT ROW_NUMBER() OVER(ORDER BY branch) AS CommonId,branch
    FROM K_BR_AddBranch
)
,K_HM_BranchTable (CommonID,branch) as
(
   SELECT ROW_NUMBER() OVER(ORDER BY branch) as CommonId,branch
   FROM K_HM_Branch
 )
 ,k_Master_BranchesTable (CommonID, branch) as
 (
  SELECT ROW_NUMBER() OVER(ORDER BY BRANCH) AS COMMONID,BRANCH
  FROM k_Master_Branches
 )
 SELECT MRS.BRANCH,AB.BRANCH,B.BRANCH,MB.BRANCH
 FROM K_RT_MasterRetailStoresTable MRS
 LEFT OUTER JOIN K_BR_AddBranchTable AB ON MRS.COMMONID = AB.COMMONID
 LEFT OUTER JOIN K_HM_BranchTable B ON AB.COMMONID = B.COMMONID
 LEFT OUTER JOIN k_Master_BranchesTable MB ON B.COMMONID = MB.COMMONID

Now it shows 4 columns ,4 tables branches are displyed..Now i want to display in single column ALL branch...what i do ? please help me?

解决方案

Try like this..

SELECT BRANCH1 AS BRANCH FROM BRANCH_TABLE
 UNION ALL
 SELECT BRANCH2 AS BRANCH FROM BRANCH_TABLE
 UNION ALL
 SELECT BRANCH3 AS BRANCH FROM BRANCH_TABLE
 UNION ALL
 SELECT BRANCH4 AS BRANCH FROM BRANCH_TABLE;

<pre lang="c#">;WITH K_RT_MasterRetailStoresTable (CommonId,Branch) AS
(
SELECT ROW_NUMBER() OVER(ORDER BY Branch) AS CommonId,Branch
FROM K_RT_MasterRetailStores
)
 ,K_BR_AddBranchTable (CommonId, branch) AS
 (
SELECT ROW_NUMBER() OVER(ORDER BY branch) AS CommonId,branch
FROM K_BR_AddBranch
)
 ,K_HM_BranchTable (CommonID,branch) as
(
 SELECT ROW_NUMBER() OVER(ORDER BY branch) as CommonId,branch
FROM K_HM_Branch
)
,k_Master_BranchesTable (CommonID, branch) as
(
SELECT ROW_NUMBER() OVER(ORDER BY BRANCH) AS COMMONID,BRANCH
FROM k_Master_Branches
) 

SELECT distinct BRANCH FROM K_RT_MasterRetailStoresTable
UNION ALL
SELECT distinct BRANCH FROM K_BR_AddBranchTable
UNION ALL
SELECT distinct BRANCH FROM K_HM_BranchTable
UNION ALL
SELECT distinct BRANCH FROM k_Master_BranchesTable;

这篇关于如何将多个表与常用列名组合在一起？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！